Another Time Dimension question

I want to track sales vs. both date scheduled to be shipped and date actually shipped. Both are fields in my OLTP. I can set the SSIS program to extract data any why I choose.

Would it be best to create a fact table and 2 different dimension tables or combine the time fields in one dimension table or leave them in the fact table and let SQL Server extract the time dimension?

Thanks.

Hello! In SSAS2005(Analysis services 2005) you have something called 'Role-playing' dimensions that solves this problem. You will only need one time dimension in the starschema that you join to all dates in the fact table.

If you have these relations designed in the source system/star schema or in the data source view SSAS2005 will detect this relation and create separate time dimensions automatically when you build the cube.

HTH

Thomas Ivarsson

|||I don't want to apply one time dimension to many fact tables. I want to apply many time dimensions to one fact table. Right now my date fields are in the Fact Table.|||

I do not think that I have said that but my explanation, perhaps, was not good enough.

You use one time dimension and join the fact tables different date dimensions to the same date key in the time dimension table.

Connect the key for order date in the fact table to the date key in the time dimension. Connect the date key for invoice date in the fact table to the date key in the time dimension.

HTH

Thomas Ivarsson

|||

Ahh...that's better....I think I need to take my dates out of my fact table and create a dimTime table and then I can create the multiple relationships.

Thanks.

|||

That seems to be working but I'm a little fuzzy on the theory.

Either or both date fields may be null in my OLTP table, so I can't use either for the key field. My OLTP table has an integer key that is just a counting number.

I created the dimTime table with the same type key field. When I did my SSIS run I just copied the Fact table key field to the dimTime table key field along with the date fields.

The tables are linked but not throught a date field. SSAS is smart enough to build its aggregates on the date fields and pretty much ignore the key field? It only cares that the tables are linked somehow?

Thanks!!!!

|||

You can add a theoretical time member to the time dimension like '2099-12-31" and point fact records without time members to that time member.

There will be many more SQL Server releases until we reach that date.

HTH

/Thomas Ivarsson

Annotations in design mode?

Is there any way to persist Annotations in design mode? I'm considering using them to keep track of measure-group specific state, but I'm worried that every time I re-deploy my cube, I'll lose this info.

Does anyone have experience using Annotations? Are they only exposed via the AMO OM?

In design mode, you can switch to raw XMLA code view by right clicking on the object. You can select "view code" and you'll see XML representation of the object.

You got to be careful modifying the XML directly such that if something goes wrong, you might loose consistency of your object. But on the other hand XML representation gives you power to access any property (such as annotations ) directly without UI.

Again. Be careful with using raw XML code view, but if you feel comfortable with modifying XML, adding new tags you should be able to add annotations to pretty much any object in your database.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||A collegue and I are looking at possibly adding this feature to BIDS through an open source project we are working on at http://www.codeproject.com/BIDSHelper unfortuntately this is not one of the features we have in the pipeline for the first release, but it is one of the things we have on our list of possible features.|||The BIDS helpers look pretty cool, thanks Darren.|||

The ability to edit annotations is finally coded in the BIDS Helper project. See:

http://www.codeplex.com/bidshelper/Wiki/View.aspx?title=Show%20Extra%20Properties&referringTitle=Home

Hope that's helpful.

Annotations in design mode?

Is there any way to persist Annotations in design mode? I'm considering using them to keep track of measure-group specific state, but I'm worried that every time I re-deploy my cube, I'll lose this info.

Does anyone have experience using Annotations? Are they only exposed via the AMO OM?

In design mode, you can switch to raw XMLA code view by right clicking on the object. You can select "view code" and you'll see XML representation of the object.

You got to be careful modifying the XML directly such that if something goes wrong, you might loose consistency of your object. But on the other hand XML representation gives you power to access any property (such as annotations ) directly without UI.

Again. Be careful with using raw XML code view, but if you feel comfortable with modifying XML, adding new tags you should be able to add annotations to pretty much any object in your database.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||A collegue and I are looking at possibly adding this feature to BIDS through an open source project we are working on at http://www.codeproject.com/BIDSHelper unfortuntately this is not one of the features we have in the pipeline for the first release, but it is one of the things we have on our list of possible features.|||The BIDS helpers look pretty cool, thanks Darren.|||

The ability to edit annotations is finally coded in the BIDS Helper project. See:

http://www.codeplex.com/bidshelper/Wiki/View.aspx?title=Show%20Extra%20Properties&referringTitle=Home

Hope that's helpful.