Cube Design Suggestion!!!

[PRIT AMRIT]

Hi Guru's

We have a huge challenge to derive our measures against relative time categories(i.e. Year, Year-1, Year-2, Month, Month -1 till Month-12, QTD, QTD-2, QTD-2, Week, Week-1 till Week-48, Day, Day-1 till Day-364) this applies for current year, last year. Also we have Budget 

If we are agreed to do it in ETL, then the ETL guys will simply run away with the fact that they have to derive Thousands of Measures for the above time categories.

Now the only option we are left with is to convince the Customer if we can develop Cubes for them, since Cube already have the in-built functionality to derive the measures against the Time Categories. Correct me if I am wrong?

Since I don't have much Cube Development exp, Can you please let me know if you foresee any difficulties in achieving this or anything we should be aware/concerned about? I will make a point of it and then communicate with the Customer. Please reply asap.

Thank you so much in advance.
Prit

[RobsWalker68]

Hi,

A couple of questions you need to consider:

1. Do the users have the requirement to be able to select any given period i.e. look at data historically and the corresponding relative time categories or is it just the relative time categories relating to the "current period".

To create the relative time categories in transformer it needs a date/period to base it on and this may not be applicable across the business and across all your reporting scenarios.

2. If you can't use relative time categories in the cube would it be feasible to use dimensional functions in report studio instead?

3. Instead of creating 1000's of measures in the ETL have you considered modifying your date dimension table to including relative time categories or rolling time categories.  That would be a much simpler piece of engineering.

Cheers

Rob

[PRIT AMRIT]

Hi Rob,

1. look at data historically and the corresponding relative time categories
2. This is an enhancement Project. So the Existing Model is Relational. So it is very unlikely to to change the Model to DMR, since it would have a serious impact. However, even if we have DMR, still we would be deriving the 1000's of measures in the reports. Let me also tell you, the user want to use Query studio to drag & drop purpose also.
3. Even if you create a Time Dimension in ETL, still we will end up with doing UNION in report studio to get all the time categories in 1 report. e.g.

below is my time dimension which I would derive in ETL:
------Date---------Month-------------Month-1----------Month-2
01-Jun-2012----01-Jun-2012----31-May-2012----30-May-2012

My Trancation Table:
------Date-------- Product----- Amount
01-Jun-2012-------AAA---------100
31-May-2012------BBB----------200
30-May-2012------CCC---------300

If we hav the Join betweenTransaction and Date table, I can get only one value i.e. Month or Month-1 or Month-2. So if i want to show these three items in one report, then I have do an UNION.

So if i have to do UNION for a huge list e.g.(Day, Day-1 till Day-50), i have to create 50 Queries in Report studio and then UNION. Again Current Year, Last Year, Budget.... which would have a serious performance issue?

Please help me with my above analysis & understanding. Correct me if i am wrong and guide me :)

Thanks for your help in advance.

Regards,
prit

[RobsWalker68]

Hi Prit,

I'm sure a cube is going to be a good solution just need to be aware if users plan to select historical periods then the relative time categories you create may not be relevant in all situations.  For example if a user wishes to select a given period i.e. Feb 2012 and see the QTD then he would expect to see Jan - March, however using the relative time category QTD he would see Apr - June or something similar depending on what sets your date period in the cube build.

To get around this you can still use a cube but not use the relative time dimension and instead use the primary date dimension and apply the dimension functions in Report Studio instead.  Obviously these arn't available in Query Studio so may need to mix and match.

Not sure where the 1000's of measures are coming from as what ever method you use the date dimension  should be doing the heavy lifting and not the fact table.  I don't understand why you would need to do a union to get the time categories onto a report.  Would you be able to explain that a bit more, its a Friday morning so I'm being a bit slow  ;D

The time dimension example which you could derive from ETL seems to be columnar instead of row based.  Is there a reason for that?  If you were doing a calculation such as day 1 - 50 and still using a relational star schema design then wouldn't that just be a total of what ever measure from the chosen start date + 50 days? Year, a total of the measure for the year column in the date dimension etc.       

Cheers

Rob       



         

[PRIT AMRIT]

The time dimension example which you could derive from ETL seems to be columnar instead of row based.  Is there a reason for that?  If you were doing a calculation such as day 1 - 50 and still using a relational star schema design then wouldn't that just be a total of what ever measure from the chosen start date + 50 days? Year, a total of the measure for the year column in the date dimension etc.

----- I 100% agree with you Mr. Rob ;D Actually it's a Friday morning for me too. he he.... I was thinking would create a Time Dimension and then Join to the Transaction table. Instead I can add as many number of culumns i want and put in columnar manner. But again this would be a HUGE task for ETL folks to ge this table done and the ETL would really take a lot of time to run in the backend.

Why I am saying 1000's measures is, the ETL Table would look like the attached sample excel Let me know if I am missing somehting. If yes, would you mind to provide a Sample Time Dimension Table in excel?

To get around this you can still use a cube but not use the relative time dimension and instead use the primary date dimension and apply the dimension functions in Report Studio instead.

---Agree this could be a work around. I guess performance shouldn't be an issue either?

Thanks Rob for your timely reply. Have a good weekend ;D

Cheers,
Prit

[cognostechie]

Prit - The relative time categories can also be created in Framework Manager

ex:  Last 30 days
       Last N Days - This will prompt you to enter no. of days
       Last 3 Months
       Last year
       YTD
       Prior YTD

You can then use these in the report and the column for the measure will accumulate the data for that time period.

This is a lot better solution than to create so many columns in ETL or create cubes just for that.

I posted lot of these in the Framework Manager forum some time back. If you do a search, you might find it.