Hi Everyone,
I am a complete newbie to metadata modeling in Cognos and would love to get advice on how to proceed with our first package. I work for a video game company that has multiple games in our portfolio. For each game, we have a variety of standard stats. Our stats are stored in a SQL data warehouse and consolidated into a single table (see bottom of posting for list of fields in this table). Each stat is available in 3 different time dimensions: by day, by week, by month. For some of the stats (such as Revenue), it is possible to calculate the total by week and by month by simply adding up the daily information. For other stats (such as Unique Playing Users), the weekly numbers are not equivalent to the sum of the daily (since we are deduping within the specified time period so that a player is only counted once no matter how many times he/she played.)
Originally, I was thinking of taking the SQL table and importing all of the fields directly into one query subject. However, I'm wondering if I need to split out the measures into two buckets: those that can be aggregated versus those that can't. The measures that can't be aggregated would then be split out into multiple data items, like "unique_players_daily", "unique_players_weekly" and "unique_players_monthly".
Any thoughts or advice? Much thanks in advance!
Fields in Table
Game
Date Type (by Day, by Week, by Month)
Date
# New Account Signups
# Unique Playing Users
Avg Play Time
# Unique Paying Users
Revenue
Best practice is to have everything in a fact table at the same grain. Weekly measures in one fact table and daily in another. Then use determinants in the calendar dimension query subject to resolve queries that include measures at different grains.
That appears to me to be an aggregate table. Assuming your actual data warehouse from which this table is coming has a proper design and indexing, you are probably better off modeling those tables directly. This will give you more flexibility and probably more interesting queries.
If I am off the mark, what tjohnson3050 said is solid advice. Split the weekly and daily stats into seperate query subjects.