Analysis Services Measures Using Single Date Dimension

  • 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