February 17, 2011 at 2:17 pm
Hello, I have a fact table that contains date keys for specific statuses within a customer lifecycle and I was wondering if anyone knows a way to utilize MDX and the fact table in an analysis services cube so you only have to use a single date dimension to get counts of those statuses for a given time.
CREATE TABLE [dbo].[F_APPLICATION_TBL](
[F_Application_Key] [int] IDENTITY(1,1) NOT NULL,
[Application_Number] [varchar](8) NOT NULL,
[Person_Key] [int] NOT NULL,
[Application_Date_Key] [int] NOT NULL,
[Withdrawn_Application_Date_Key] [int] NOT NULL,
[ITM_Date_Key] [int] NOT NULL,
[Matriculation_Date_Key] [int] NOT NULL,
[Denied_Date_Key] [int] NOT NULL,
[Full_Admit_Date_Key] [int] NOT NULL,
[Conditional_Admit_Date_Key] [int] NOT NULL,
[Deferred_Date_Key] [int] NULL
)
INSERT INTO [EDW].[dbo].[F_APPLICATION_TBL]
([Application_Number]
,[Person_Key]
,[Application_Date_Key]
,[Withdrawn_Application_Date_Key]
,[ITM_Date_Key]
,[Matriculation_Date_Key]
,[Denied_Date_Key]
,[Full_Admit_Date_Key]
,[Conditional_Admit_Date_Key]
,[Deferred_Date_Key]
)
VALUES
('1'
,1
,20100101
,-1
,20100115
,20100117
,-1
,20100201
,-1
,-1)
GO
INSERT INTO [EDW].[dbo].[F_APPLICATION_TBL]
([Application_Number]
,[Person_Key]
,[Application_Date_Key]
,[Withdrawn_Application_Date_Key]
,[ITM_Date_Key]
,[Matriculation_Date_Key]
,[Denied_Date_Key]
,[Full_Admit_Date_Key]
,[Conditional_Admit_Date_Key]
,[Deferred_Date_Key]
)
VALUES
('2'
,2
,20100101
,-1
,20100116
,20100117
,-1
,20100201
,-1
,-1)
GO
INSERT INTO [EDW].[dbo].[F_APPLICATION_TBL]
([Application_Number]
,[Person_Key]
,[Application_Date_Key]
,[Withdrawn_Application_Date_Key]
,[ITM_Date_Key]
,[Matriculation_Date_Key]
,[Denied_Date_Key]
,[Full_Admit_Date_Key]
,[Conditional_Admit_Date_Key]
,[Deferred_Date_Key]
)
VALUES
('3'
,3
,20100102
,-1
,20100117
,20100118
,-1
,20100202
,-1
,-1)
GO
When you look at the data you can see 2 applications were created on 2010-01-01, and 1 created on 2010-01-02, 2 applications received a Matriculation status on 2010-01-17 and 1 on 2010-01-18, and 2 applications went into Full Admit on 2010-02-01 and 1 application on 2010-02-02. With that said I would like to set up a single time dimension in analysis services along with a separate measure for each status such that slicing the measures by the single time dimension would look something like this:
Measures
Application Create CountMatriculation CountFull Admit Count
Dates1/1/2010 2 0 0
1/2/2010 1 0 0
1/17/2010 0 2 0
1/18/2010 0 1 0
2/1/2010 0 0 2
2/2/2010 0 0 1
I know I can implement this by creating another table or view that has a single record for each status and application number (with a single date key on each record), but I would like to leave the table as is and utilize MDX to accomplish my goal. If anyone has any ideas or has experienced a similar situation your feedback would be greatly appreciated, thanks!
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply