Reduce Rows in Inventory Snapshot Fact Table

View previous topic View next topic Go down

Reduce Rows in Inventory Snapshot Fact Table

Post  pgali on Mon Nov 25, 2013 3:54 pm

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.

pgali

Posts : 6
Join date : 2012-06-12
Location : USA

View user profile

Back to top Go down

Re: Reduce Rows in Inventory Snapshot Fact Table

Post  ngalemmo on Mon Nov 25, 2013 4:43 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Reduce Rows in Inventory Snapshot Fact Table

Post  Mike Honey on Mon Nov 25, 2013 9:45 pm

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
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: Reduce Rows in Inventory Snapshot Fact Table

Post  BoxesAndLines on Tue Nov 26, 2013 10:16 am

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!
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Reduce Rows in Inventory Snapshot Fact Table

Post  pgali on Tue Nov 26, 2013 11:42 pm

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

pgali

Posts : 6
Join date : 2012-06-12
Location : USA

View user profile

Back to top Go down

Re: Reduce Rows in Inventory Snapshot Fact Table

Post  Mike Honey on Wed Nov 27, 2013 5:58 pm

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
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: Reduce Rows in Inventory 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