ANalysis Services Date/Time Dimension Problem
-
Hi All:
I have a data mart I am trying to build a MOLAP cube on in analysis Services. The data model is a Kimbal star schema design. The fact table only has keys and metrics. There is a seperate time/date dimension which works off of a key. The time/date dimension's granularity is day. THe heigharchy(sp?) is as follows:
.... FisYr (fiscal Year)
... FisQtr (Fiscal Quarter)
.. FisPd (Fiscal Period)
. Date (date)
Here is what I don't understand . . .
How do I make this dimension table act like a true time dimension so that I can get the agregations I need. I need to be able to this year vs last year etc
If I use the wizards time deminsion it just propogates calander format off of the date field. If I treat it like any other dimension it does not propogate like I want.
Any Sugestions?
-
Here's how I do it...
I have a SQL table for my dates. It's updated every day with the current date. Schema as follows:
CREATE TABLE [FH_dates_deminsion] (
[date_id] [int] IDENTITY (37530, 1) NOT NULL , --EQUATES TO EXCEL DATE VALUE
[sql_date] [smalldatetime] NOT NULL ,
[integer_date] [int] NOT NULL ,
[calendar_week] [tinyint] NOT NULL ,
[calendar_month] [tinyint] NOT NULL ,
[calendar_quarter] [tinyint] NOT NULL ,
[calendar_year] [smallint] NOT NULL ,
[calendar_day_int] [smallint] NOT NULL ,
[day_long] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[fiscal_week] [tinyint] NOT NULL ,
[fiscal_quarter] [tinyint] NOT NULL ,
[fiscal_month] [tinyint] NOT NULL ,
[fiscal_year] [smallint] NOT NULL ,
[fiscal_day_int] [smallint] NOT NULL ,
[month_name_short] [char] (3) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[month_name_long] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[fiscal_quarter_long] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[calendar_quarter_long] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
CONSTRAINT [PK__FHIS_dates__76CBA758] PRIMARY KEY CLUSTERED
(
[date_id]
 
ON [PRIMARY]) ON [PRIMARY]
I usually join on the sql_date column to other tables in my cube design.
An example of a time dimension that I will then build is as follows:
Time Dimension for Fiscal Years (FY begins October):
Fiscal Year (key and name columns: fiscal_year)
Quarter (key and name columns: fiscal_quarter)
Month (key: fiscal_month name column: month_name_long sort by fiscal_month)
I have alot of flexibilty with this approach.
Hope this helps.
bb
-
My date time dimension table is very similar to yours. So how do I get the aggregations I want. For example how do I get the cube to compare fiscal year 2005 to fiscal year 2004?
-
Your fact table should have a date column that will join with the date_id, the sql_date, or the integer_date column of the dates_dimension table.
After you create the join, create your time dimension(s) using the date dimension table(s).
Process the cube.
Using MDX you can query multiple years. Example Query:
SELECT { [Admit Date].[Year].&[2003], [Admit Date].[Year].&[2004] } ON COLUMNS ,
{ [Account Status].[All Account Statuses] } ON ROWS
FROM [BAR Summary]
WHERE ( [Measures].[Patients] )
Your OLAP viewer software should help you "slice and dice" the data in the manner you described.
-
Thanks, I was unclear if I could set the cube up that way in analysis services or if it was done on the viewer. You are saying it can only be done on the viewer. This is different than cognos cubes where you can define the structure before you process the cube. Thnaks for you help. This is my first thread on this site, is there any accepted answer or point awards I need to do?
-
Evan,
There's a few ways to do it, some a lot harder than others.
If you want, you can add Calculated Members to your Measures, so you will have a 'Previous Year Sales' measure where you use the base Sales measure but then use MDX functions that look at the currently filtered year to determine the previous year, and it returns that sales for that year. (Pro: pretty easy to set up; Con: need one of these per measure, ie one for sales, one for costs etc etc).
You can add calculated members to your Tiem dimension (thing 'Special Time Categories' in Cognos speak). These are a little harder to do (dependant on how you've set up your time dim) but let you create 'YTD', 'LYTD" etc, and then you can use these across any measure in your cube (watch out for non-additive/semi-additive measures though). (Pro: use across multiple measures; Con: a little tricky with the MDX if you've not used 'nice' member keys).
As BB pointed out, you can use your front end tool to do some of these also. Some tools do this better than others. E.g. to get a YTD in Powerplay, you'd prob have to go to Reporter mode and then add all of the members together (ugly and not self-maintaining). But to compare year-on-year (ie year totals) you could easily do the calc in powerplay. Some other tools have very good time manipulation, letting you created YTD's etc on the client side, where you can control the 'to-date' part of the YTD (ie see the YTD value for March even though it's October).
NB with AS2K5 they (msft) have included some functionality that assists (ok, it does it all) you in creating the calc members within the time dim itself.
Cheers,
Steve.
-
thanks a lot steve, that was more where I was trying to go. I understood most of your advice and I appreciate the reply. I am a little ignorant of some of the acronyms though. Specificly I did not understand the last sentence.
What exactly does
"
NB with AS2K5 they (msft) have included some functionality that assists (ok, it does it all) you in creating the calc members within the time dim itself.
" mean?
It seems that you were telling me of a product or method that would be very helpful but I don't recognize NB or AS2K5 or (mstf), what do those stand for?
Once again, thanks and please excuse my ignorance
-
Hi Evan,
Sorry about the acronyms, I'm a lazy typer
NB means (something like) Note Benne, basically "Note Well" in English.
AS2K5 is Analysis Services 2005 (due out ~7th Nov), so it's the next version of Analysis Services (what you're using today is AS2K or Analysis Services 2000).
msft is my shorthand for Microsoft
Below are two MDX statements from calc members we use. Note that these use/reference a hidden dimension to determine what the 'current day' is. We do this because where we used to use the VBA function 'Now()' (returns the current datetime on the server), if our data hadn't finished loading for the day, this would mean that the MTD would include today but there was no data for today - this is more of a problem at the week level. If you don't have these issues, then using Now() and stripping bits out of it to make the date key should work for you.
The first one is YTD, so reading through the MDX from left to right, give me the sum of the periods-to-date within the Txn Week (time dim) where the 'period-to-date' end point is the opening period of the 'Current Day' (time) dim. Do this for whatever measure is being used. NOTE : this works fine for say 'Store Sales' but using a SUM function across a non-additive measure will either return errors or just erroneous results. We could have used (not sure why we haven't
) the Aggregate function, which should then aggregate the measure according to it's aggregation setting (distinct count, sum , count, min, max etc).SUM
(PeriodsToDate([Txn Week].[Year],StrToMember("[Txn Week].[Week].["+OpeningPeriod([Current Day Txn].[Week]).Name+"]")),[Measures].CurrentMember)
The second one is 'Current Month Last Year'. The keys here are to use Parallelperiod, look at the year level, go back 1, for the specified month.
SUM
({ParallelPeriod([Txn Week].[Year],1,StrToMember("[Txn Week].[Month].["+OpeningPeriod([Current Day Txn].[Month]).Name+"]"))},[Measures].CurrentMember)
There are some alternate ways of doing this as I said, like using date functions across the Now() function to generate strings that you can then use in the StrToMember (string to member) function.
Steve.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply