Create dynamic filter based on selected date in MDX / SSAS

0
votes

In SQL Server Analysis Services (2008) I have a fact table in our DSV laid out like this:

DOC_NO    RECORD_NO    REPORT_DATE    CREATE_DATE    CLEAR_DATE    INVOICE_TOTAL
1         2000         1/1/2014       12/1/2013      NULL          $1000.00
2         2001         3/1/2014       1/14/2014      3/1/2014      $1001.00
3         2002         6/1/2014       1/31/2014      6/1/2014      $1002.00
4         2003         2/15/2014      2/14/2014      NULL          $1003.00
5         2004         7/31/2014      2/28/2014      7/31/2014     $1004.00

(Assume that we have dimensions for [DOC_NO] and [RECORD_NO] separately)

In the cube, we have a [Time] -> [Month] -> [Week] -> [Date] dimension. The selected dimension value filters on the REPORT_DATE value in the fact table. Basically if a REPORT_DATE value exists in a month, we display the row of data.

Scenario: In our reporting solution, When the user picks a month from the time filter the desired outcome is that we filter the fact table data for only rows that meet this criteria:

REPORT_DATE >= CREATE_DATE AND (REPORT_DATE <= CLEAR_DATE OR CLEAR_DATE IS NULL)

This amounts to basically telling if a document is "OPEN" or "CLOSED" during a given month. Normally this kind of thing is something we would do conditionally in a named query in the DSV, but due to the dynamic time filtering, we can't do it ahead of time.

Any ideas on how we can accomplish what we're looking for? Basically creating a calculated measure or MDX statement that acts like a dynamic dimension filter.

2
sql-serverfilterssasmdx
You could probably create a calculated measure that identifies the records you want to show, but this sounds rather like a snapshot fact table. Could you perhaps create a view that acts as a fact table for your cube that includes one row per document per month and ties to a dimension attribute for the status? If this is a common use case, this may be worth exploring. – mmarie

2 Answers

1
votes

You Can try this..

Create a named calculation (say "Status") in your fact table using the calculation you have mentioned. This column is going to have values "OPEN" or "CLOSED". Now build a degenerated dimension on top of the fact table which will have "Status" as an attribute. Use this attribute in your MDX query to select "OPEN" or "CLOSED" documents for a selected month.

Let me know if this works for you or not.

0
votes

Here is a solution based on new dimension, that determines whether document is opened or not.

Test data:

create table Data (DOC_NO int,RECORD_NO int,REPORT_DATE datetime,CREATE_DATE datetime,CLEAR_DATE datetime,INVOICE_TOTAL money)

insert Data(DOC_NO,RECORD_NO,REPORT_DATE,CREATE_DATE,CLEAR_DATE,INVOICE_TOTAL)
select 1,2000,'1/1/2014','12/1/2013',NULL,1000.00 union all
select 2,2001,'3/1/2014','1/14/2014','3/1/2014',1001.00 union all
select 3,2002,'6/1/2014','1/31/2014','6/1/2014',1002.00 union all
select 4,2003,'2/15/2014','2/14/2014',NULL,1003.00 union all
select 5,2004,'7/31/2014','2/28/2014','7/31/2014',1004.00

  1. As far as shown in your example all of this rows are open-docs. So let's add new row that has wrong dates:

    insert Data(DOC_NO, RECORD_NO, REPORT_DATE, CREATE_DATE, CLEAR_DATE, INVOICE_TOTAL) select 6,2005,'7/31/2014','8/31/2014',NULL,2005.00

  2. Add dimension keys to ETL-process:

    alter table Data add REPORT_DATE_ID int,CREATE_DATE_ID int,CLEAR_DATE_ID int

  3. Map them with corresponding values:

    update Data set REPORT_DATE_ID=isnull(convert(varchar,REPORT_DATE,112),19000101), CREATE_DATE_ID=isnull(convert(varchar,CREATE_DATE,112),19000101), CLEAR_DATE_ID=isnull(convert(varchar,CLEAR_DATE,112),19000101)

  4. Here is our result:

    select * from Data order by REPORT_DATE table Data

  5. Create named calculation 'IsOpened' in DataSourceView with your conditions:

    case when REPORT_DATE >= CREATE_DATE AND (REPORT_DATE <= CLEAR_DATE OR CLEAR_DATE IS NULL) then 1 else 0 end

  6. Add new dimension, based on this calculation (for example with view like CREATE VIEW vwIsOpened as select 0 as ID union all select 1)

  7. Connect dimension and data cube by key of dimension and named calculation from point 5.

  8. Add calculated member to the cube:

    CREATE MEMBER CURRENTCUBE.[Measures].[IsOpenedDoc] AS IIF([Report Date].[Report Date].CurrentMember.Level is [Report Date].[Report Date].[Month] ,([Is Opened].[Is Opened].&1,[Measures].[Count]) ,null), VISIBLE = 1 ;

I don't know why this behavior is used only with months (as far as I understand from your words), but still added this to IIF condition. Maybe it's better just to use simple tuple from the second row.

Here is the result:

Result

This new calculated measure is only for really opened docs. The last one row of the table corresponds to [Is Opened].[Is Opened].&[0] .

First time I've tried to use dynamic MDX like 'SUM({NULL:StrToMember("[Create Date].[Create Date].[Month].&["+[Report Date].[Report Date].CurrentMember.Member_Key+"]")},[Measures].[Count])'. But this will work only if there are no 'holes' in months (in your example we have no Create Date 2014-03, so this method won't work).

Hope someone can deliver more simple solution based only on MDX funstions (filters,aggregates etc.), but I can't afford it.