BI Tool and Snapshot Fact Table

[apermag]

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

[LAndrews]

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).

[apermag]

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.

[VHF]

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?

[Vishy]

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.

[Sponsored content]