Analysis Services Measures Using Single Date Dimension

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Analysis Services Measures Using Single Date...

Analysis Services Measures Using Single Date Dimension

Rate Topic

Display Mode

Topic Options

Author

Message

NtrsBOB

Posted Thursday, February 17, 2011 2:17 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 17, 2013 3:03 PM
Points: 4, Visits: 67

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 Count Matriculation Count Full Admit Count
Dates 1/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!

Post #1066007

« Prev Topic | Next Topic »

Permissions