Is is part of the current target?

I have a target (milestones) fact table that is linked with Keyword, Country and "Target Actual Finish Date" dimensions.

When I use Keyword and country dimension I would like to easily filter the data to show me keywords (per country) that have "active" target (where target actual finish date is null = -1) and also shows keywords that don't have "active" target.

I am able to do that with MDX but I would prefer something easier and more re-usable.

Is it possible to build it in a way where I have dimension attribute that show CurrentTarget yes/no (per keyword/country) so I can use it excel?

I use SSAS 2008 R2.

Many thanks in advance
Emil

The query would be:

SELECT ... FROM keyworddim, countrydim, facts
WHERE (dimension keys match)
GROUP BY ISNULL(target actual finish date), country, keyword

Hi

Thanks for the reply however I'm afraid I'm not after SQL (or MDX queries).

Regards
Emil

What is the question? Are you asking how it should be modeled?

Hi

Yes my question is how it should be modeled.

I have been thinking about it today and I think I have overlooked the most basic way to do it. Instead of focusing on my Fact Target table I should have think about the fact tables I want to filter. So I will most likely add a flag column IsCurrentTarget on the fact tables I want to use and add a dimension to use it. This will require updates on transactional fact tables ("old" rows) which isn't ideal but in this case should be fine.

Regards
Emil

Assuming you have a date dimension, another approach is to have an 'unknown' date row. Fact tables should never have null FK's. So, you could use a date dimension attribute to identify unknown dates, and populate the actual finish date FK to reference the unknown row.

I do use -1 for null dates but in this case I don't think it would allow me to create a dimension with attribute that would have yes/no taking into consideration that my fact target table is not actually what I want to filter but instead I want to filter other fact tables based on the information from fact target table.

Unless there is a relationship in SSAS other than regular (one-to-many cardinality) that can handle this logic, I might have to just populate it in ETL which doesn't sound to me like a bad idea.

Regards
Emil

Is it a derived attribute by actual-finish-date? If it is, you may not need to add a separate column into fact or a new dimension for it. You should do it in semantic(logic) layer instead, saving the extra ETL work. I can think of following two options:

Option 1.
Create a view on top of the target fact with a derived column IsCurrentTarget using case statement on actual-finish-date. You then feed the view to the cube as your fact table.

With SSAS you could configure an attribute in the fact table/view as a dimension known as Fact Dimension or degenerate dimension by Kimball, without creating a physical dimension table.

Option 2.
Since the actual-finish-date points to the date dimension, the -1 (or future date) corresponds to 'N' and a past or current date to 'Y'. So you may add a Named Calculation to the date dimension in SSAS.

With or without SSAS, you should derived this type of dimension in logical layer instead of adding it to the physical model causing unnecessary work in ETL.

Hi Hang,

Option 1 sounds like something I should do. I already use views in cube instead of tables so that should be just a matter of adding simple logic. I agree that in ETL it would have been overcomplicated.

Option 2? Could you clarify this option? I might not use it but I'm interested to see how that would work? an example of the draft calculation would be appreciated.

Many Thanks
Emil

With option 2, since date dimension is a conformed dimension shared across many subject areas, you may not want the derived attribute to apply to other usage. So you should create a dimension view just for actual-finish-date, like a role playing dimension. Then in SSAS, you create a Named Calculation on the dimension using the similar case statement on the calendar date as you would in the fact view. Now the new derived attribute is attached to the date dimension instead of feeding it through fact view. Hope this will help.

Hi Emil,

SSAS does provide a Many-to-Many relationship that will probably meet your requirement.

Setting these up can be a bit obscure and the product doco is not particularly helpful. The best reference is generally considered to be this:
http://www.sqlbi.com/articles/many2many/

Good luck!
Mike