What is correct way to join a start/end date driven dimension to a fact table in data foundation?

[Former Member]

I have a bad universe or a data design issue. 

Several versions of hierarchies reporting store entities in reporting Fact measures.

Example of date driven Hierarchy Dimension:   ORG_KEY

                                                                            START_DATE

                                                                            END_DATE

                                                                            STORE_NUMBER

                                                                            ORG_HIERARCHY   

                                                                            CURR_FLG   (Y/N)        

Fact table  :                                                         ORG_KEY

                                                                             CALENDAR_KEY

                                                                             TRANS_DATE

                                                                             $amount  

Calendar Dimension:                                            CALENDAR_KEY

                                                                              DAY_DATE

                                                                               FY_WEEK          (201452)

                                                                                FY_PERIOD      (201412)

                                                                                FY_QUARTER   (201401)

                                                                                 FISCAL_YEAR    (2014)

Users WISH:

Wish for store number and org hierarchy to pull as of the last day of each pull without prompt.    The Store(ORG_KEY) as in the fact table; but the ORG_HIERARCHY and other attributes as of the last day they pull.

Daily (Would be Calendar.Day_Date in Filter) ,

Week to date (would be Max Calendar.Day_Date for (201452) FY_WEEK  as entered,

Month to date,

Year to date, 

AdHoc queries.  

My problem is I see how they could manually pull this in Webi.   I have tried everything I know to join to no avail.  I have not gotten @Prompts to work in joins, derived tables, etc.  in the data foundation.  Wonder what is difference between parameter in Data foundation vs. filter in buisness layer.    None of them worked. 

Please help!   ANY ideas would be appreciated.   .

[Former Member]

{Note that abbrevations in brackets are just for short form further down the answer}

Join Store Dim (SD) to Transaction Fact (TF) on ORG_KEY=ORG_KEY with 1 to Many cardinality

Create an alias of Calendar Dim and call it Transaction Date (TD)

Join TD to TF on TD.CALENDAR_KEY=TF.CALENDAR_KEY with 1 to Many cardinality

Create a predefined condition in your universe called "Return Yesterday's Transactions" as:

TD.DAY_DATE = trunc(sysdate-1) <-- That assumes Oracle database; use whatever is correct for yesterday for your RDBMS

The above predefined condition when added to your data query will return only yesterday's transactions.

However, if you want to return all the different types of sales, you would need an object for each one. To do that, you'd use a case statement. Again, using Oracle syntax, an example of MTD would be:

SUM(CASE WHEN trunc(TD.DAY_DATE,'yyyymm') = trunc(sysdate,'yyyymm') THEN TF.amount END)

If you have any more questions about how this approach would work please shout.

As an alternative, you could create a time hierarchy and add scope of analysis to your report for it and enable drilling at report level.

[Former Member]

Would a series of filters work, one for each type of pull?

For example, a 'Year to date' filter with:

WHERE FISCAL_YEAR = '2014'  (where 2014 could be calculated, of course)

And a 'Month to date' filter with a similar filter grabbing the month part of the system date, for example.

The user would choose whichever filter corresponds to their requirements, and there would be no prompts.  The drawback here would be that the results would not be filtered by default, if that is a requirement... although since the year-to-date filtering is implicitly included in the other filters, you could potentially apply it by default, if necessary with an automatically applied filter.