Reduce Rows in Inventory Snapshot Fact Table

We have a Daily Inventory Snapshot Fact Table with Date, Product (300 rows), Geography(1500 rows) and Inventory_Type(5 rows) Dimensions with 60 day history. So the maximum rows in the fact table can be 78 million, all Products are not present in all Geographies but let's stick with 78 million for now. This table has just one fact, Inventory_Count.

INVENTORY_TYPE dimension has just one column with 5 values that tells whether the Inventory is "In Store", "In Transit", "At Port" etc.

We now need to send this Inventory data to a downstream system for reporting purposes. The downstream system is an In Memory system, its licensing is based on the number of rows and we are on the verge of exceeding the limit already. This system is not a relational database and the data needs to be present in one big table with all facts and dimensions (something like, Select * from Fact_Table JOIN All Dimensions).

So we are exploring different options to store the same data with less number of rows. We tried the Temporal Snapshot Fact Table approach but it won't work here because we need Inventory for a specific day and all data needs to be present in single table.

The only option that seems to be left is to have the Inventory Types as separate columns in fact table itsefl which would immediately bring down the rows to 15.6 million. The fact table will have 5 fact columns - In_Store, In_Transit, At_Port etc. But this will be less flexible to slice and dice the data, like stacked bar charts are hard to create etc. Most of the time users care only about In_Store though.

Are there other options to de-normalize and reduce the row count here?

Any thoughts would be very helpful.

5 columns sounds like the only reasonable solution (short of increasing your license cost). The only other alternative would be to aggregate in some manner (such as consolidating geographies) which I doubt is a desirable solution.

Hi pgali,

I'd go with ngalemmo's advice. Perhaps you can present this via a SQL view e.g.
CASE WHEN INVENTORY_TYPE = 'In Store' THEN Inventory_Count ELSE 0 END AS Inventory_Count_In_Store

etc etc.

In general your current design sounds optimal so it would be a shame to break it for a single downstream requirement.

I'm dying of curiousity - can you name the "downstream system"?

Thanks
Mike

Mike Honey wrote:...I'm dying of curiousity - can you name the "downstream system"?

Thanks
Mike

No kidding. Here's a great BI tool. It's really fast because it uses no disks for accessing data. Oh BTW, you can't really use it, because our licensing will kill your budget if you ever put more than a nominal amount of data in it. DOH!

ngalemmo - yeah consolidating across dimensions is not an option.
Mike, as I said it is not relational so there is no concept of views or joins. It needs data in one big table.

Thanks for the responses, I feel assured that "de-normalizing" INVENTORY_TYPE is the only option (other than of course licensing).

Wow this "downstream system" continues to surprise. I've been working with a variety of BI/cube tools since 1995 - I havent met one yet that couldn't read from an existing SQL view. That's quite a "feature".

Good luck!
Mike