BI Tool and Snapshot Fact Table

View previous topic View next topic Go down

BI Tool and Snapshot Fact Table

Post  apermag on Fri Jul 01, 2011 5:10 pm

Hi folks,

Having an snapshot fact table (say monthly snapshot of product stock): how do you handle in the BI Reporting tool to avoid final users report incorrectly using the "stock" measure in an additive way?

For example:

- Cognos (as far as I know) allows to handle semi-additive facts with determinants.
- Force the users to prompt for one specific month every time they run a report
- Any other method?

Just wondering what are the different possibilities between different BI tools

Thanks

apermag

Posts : 17
Join date : 2011-06-28

View user profile

Back to top Go down

Re: BI Tool and Snapshot Fact Table

Post  LAndrews on Fri Jul 01, 2011 6:41 pm

You are correct - the answer will be tool dependent.

For example in a Cognos cube, you configure a balance measure (e.g. Inventory balance), to be non-additive over the time dimension, but to be additive across other dimensions. For a non additive measure you can configure which value is show against the time dimension (current, last etc).


LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: BI Tool and Snapshot Fact Table

Post  apermag on Mon Jul 04, 2011 4:47 pm

Thanks for your answer.

I am looking for some best practices around different tools re snapshot tables. I've worked most of the time with transactional fact tables, and I am curious about the way tools handle snapshot fact tables.

Some cases it has to be an educational approach with the user (with for example, compatibility matrix). some other cases I guess the tool has to force the user to filter by time dimension or the dimension for which the measure is not additive.

Any more comments are very welcome.


apermag

Posts : 17
Join date : 2011-06-28

View user profile

Back to top Go down

Re: BI Tool and Snapshot Fact Table

Post  VHF on Wed Jul 06, 2011 11:09 am

Microsoft SSAS also has some semi-additive aggregations which are summed across all dimensions except time, including "LastChild" which could be used to give the final inventory balance from within a selected timeframe and "AverageOfChildren" (aka Average Over Time) (only available in Enterprise Edition).

However, when working with Business Objects Universes against a SQL 2008 relational DW (no SSAS cube being used), the best solution I've come up with is to do a self-restricting join on the DimSnapshotDate table (an alias of the standard DimDate table) with a prompt to force the user to select a single snapshot date for reporting. Otherwise, the quantity on hand would incorrectly SUM over time.

When doing hand-coded SQL it is easy enough to get the desired behavior by using a subquery (inner query would SUM across all dimension except time; outer query would AVG the results over time.) But I coudn't come up with a way to tell the BObj Universe how to do this! (version XI 3.1) Anybody have any ideas?

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: BI Tool and Snapshot Fact Table

Post  Vishy on Thu Mar 01, 2012 4:15 am

Name the measure in simple manner, I did this before. I had a fact which used to show month end headcount at organization,practices,department etc levels.

I named this measure not as HEADCOUNT , but as MONTH END HEADCOUNT.
Even after doing this and educating users around this , if he/she makes mistake then we don't have much to do.

In Cognos if you are creating cubes then you can set the property of this measure to PERIOD END so if users select any qtr, then the measure value would last month of that qtr. If user select YEAR then measure value would be last month value of that year.

If your users are using Query studio then try to make it a seperate query subject having this measure and put a FM level filter which will asks a Month value to select before viewing data.




Vishy

Posts : 75
Join date : 2012-02-28
Location : India

View user profile

Back to top Go down

Re: BI Tool and Snapshot Fact Table

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