Is is part of the current target?

View previous topic View next topic Go down

Is is part of the current target?

Post  itcouple on Thu Feb 23, 2012 6:31 pm

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

itcouple

Posts : 45
Join date : 2010-10-13

View user profile

Back to top Go down

Re: Is is part of the current target?

Post  ngalemmo on Thu Feb 23, 2012 7:16 pm

The query would be:

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

reply

Post  itcouple on Fri Feb 24, 2012 3:49 am

Hi

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

Regards
Emil

itcouple

Posts : 45
Join date : 2010-10-13

View user profile

Back to top Go down

Re: Is is part of the current target?

Post  ngalemmo on Fri Feb 24, 2012 11:28 am

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

reply

Post  itcouple on Fri Feb 24, 2012 1:44 pm

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

itcouple

Posts : 45
Join date : 2010-10-13

View user profile

Back to top Go down

Re: Is is part of the current target?

Post  ngalemmo on Fri Feb 24, 2012 2:47 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

reply

Post  itcouple on Fri Feb 24, 2012 3:30 pm

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

itcouple

Posts : 45
Join date : 2010-10-13

View user profile

Back to top Go down

Re: Is is part of the current target?

Post  hang on Fri Feb 24, 2012 7:16 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

reply

Post  itcouple on Sat Feb 25, 2012 10:52 am

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

itcouple

Posts : 45
Join date : 2010-10-13

View user profile

Back to top Go down

Re: Is is part of the current target?

Post  hang on Sat Feb 25, 2012 6:39 pm

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.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Is is part of the current target?

Post  Mike Honey on Sun Feb 26, 2012 11:09 pm

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
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: Is is part of the current target?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum