Multiple dimensions from a single dimension

Question

Multiple dimensions from a single dimension

Graham Stevenson

Newbie

Points: 4
More actions
November 29, 2005 at 7:38 am

#152638

Hi,
I have a cube which has multiple types of 'Period' (accounting, billing etc.). Each type refers back to the same 'Period' dimension table.
Period 7 is say July 2005, 8 is Aug 2005 etc. These have pre-defined start/end dates and NOT a standard Time dimension.
Billing period could be 7 and accounting period is 8.
I want to be able to differentiate these in a single cube. I created separate 'Shared Dimensions' for 'BillingPeriod' and 'AccountingPeriod' - but these both refer to the sam 'Period' table.
So do I need to create a separate dimension table for each period type ???. I CAN link a single Period table to both billing and accounting periods, BUT can't see how this ties into the fact tables key fields (BillingPeriod and AccountingPeriod) - ie how do I identify which is which ??. Member key and name columns BOTH simply state "dbo"."Period"."PeriodID".
Probably a dumb question with a VERY simple answer (I hope).
Thanks
Regards
Graham

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

Firstly, a plug for moving to AS2005, it handles this better with it's Role Playing dimensions.

One of the fastest ways to solve your dilema is to create a view per period type (< 5 minutes if you use copy|paste + search|replace) and then create your dimensions based on the appropriate view. This will then be reflected in your model by having a table joined to the fact for each dimension. (i.e. when you add the Accounting period im to the cube, the vw_Time_accounting_period table/view will also be added to the model, then you just join it on the appropriat ekey value/s).

Steve.

Phil.Nicholas SSCrazy Points: 2147 More actions · Answer 2

In addition in AS2k (and probably 2005) you can add the same table twice, just alias it as another table which will enable you to accomplish the above without views.

Phil Nicholas

Graham Stevenson Newbie Points: 4 More actions · Answer 3

All,

Many thanks for the replies. I also got a similar response from an MS Forum and have implemented Views.

I was just hoping thsat there may be a less 'expensive' (in terms of processing) way to achieve this maybe with virtual dimensions or something, as the documentation does state that the more 'diminsions' the more resource to process (and store ??) the cubes.

Reason is that this is just one dimension which coule be 'shared' in this manner - I do have others as well.

Many thanks though - its working with views right now ;-).

As to the 'plug' for 2005 - I'm working on that but in a corporate environment the deployment issues have to be resolved before I can design it in :-(.

Thanks all

Regards

Graham

Phil.Nicholas SSCrazy Points: 2147 More actions · Answer 4

You could union the views together and have a dimension -

[accounting period type].[accounting period]..etc,

Alternatively have seperate cubes for each accounting period type, does it make sense to have them all in the same cube? Make the dimensions shared (where data is common) so they arent processed several times.

Also you could define a dimension as accounting period (from your accounting period table) with a property of accounting period type. Using the property define a calculated measure which depending on the accounting period selected displays the accounting period type. I think this could then be used to filter the numeric measures returned for a period of a particular type. Probably not a good strategy in this case but may work well for other cases.

Phil Nicholas

Graham Stevenson Newbie Points: 4 More actions · Answer 5

Paul,

Thanks for the continued feedback. The 'periods' are not time dimensions. Essentially there is data which arrives on a certain date, and this may fall into billing period 1, but accounting period 2 etc. The 1 and 2 are stored in the fact table, and reports need to view both together, for say data on selected 'data tates'.

Its working fine with views though so until I have enough 'live data' to evaluate performance issues - we'll probably stay with this. Hopefully we will move on to 2005 fairly soon anyway so I'll get another whole raft of issues, and end up redesigning the cubes anyway :-O.

Regards

Graham

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 6

Graham,

I would have thought that the time dimensions would be a very small part of your processing overhead? Even with 10 or 20 years in the dimension, holding data at the day level you're still only looking at ~3650 and ~7300 members respectively. You should see no performance issues caused by having these dims as shared and resident in memory.

Cheers,

Steve.

Graham Stevenson Newbie Points: 4 More actions · Answer 7

Steve,

Well thats good to know, not much experience yet of large datasets in a cube. I also gave other dimensions with > 4,000 rows which will be 'shared' in a similar manner.

Thanks

Graham