Designing Analysis Services database for Inventory type applications

When designing Analysis Services database for inventory type applications, there are few design options to choose from. Which option to choose depends entirely on your specific report requirements. Purpose of this article is to describe these options and guide you in choosing one that fits best for your needs.

In inventory type application you are trying to answer following questions:

• What inventory you had at the specific point in time
• What was inventory movements during the specific time interval

Below I described options and specific design issues. For simplicity, I assumed that all you are interested is just basic count measure.

1. Load all data as transactions

Design specifics:

• Single measure group with all measures using default aggregation function "SUM".
• It would be highly recommended to have dimension "Transaction Type". This could be as simple as "Increase", "Decrease", "Initial load", or it could be much more complex with specific details ("Shipment to Customer", "Shipment to wholesaler", "Received from warehouse", "Return from customer", etc)
• To start you create first transaction that specifies starting inventory for data warehouse analysis. Example: if your data warehouse will be analysing data from year 2007, you can create initial inventory transaction for 2006-12-31. This way your inventory movement report for year 2007 will not be distorted. Also, you can have special transaction type "initial load" that would differentiate these transactions.
• You have an option to sign or not to sign transaction measures. In case when you are signing measures, you are using positive measures for inventory increase transactions and negative measures for inventory decrease transactions.
• If you decided to sign transaction measures, then sum of measures will from the start to a point in time will give you inventory information for that point.
• This type of design works well for reports that mostly are interested in inventory movements and not in actual inventory at the point in time. Calculations for inventory at the point in time could be too slow, as all historical transactions could be used. 

2. Load all data as periodic snapshots

Design specifics

• Single measure group with measures aggregated using aggregation function "LastChild"
• You choose snapshot period based on your requirements. If you need to track your inventory monthly, then create just monthly snapshots.
• Having frequent snapshot can increase database size substantially.
• Having proper date based measure group partitioning can minimize amount of data accessed during querying. For example, if you are looking into inventory for year 2007, just partition 2007-December will be queried by SSAS (assuming monthly partitions).
• There is no easy way to track inventory movements, except just by calculating deltas from the last period. This might be enough for certain type of applications.

3. Load all data as snapshots at the point of data change

Description for this option

• Single measure group with measures aggregated using function "LastNonEmpty"
• You create snapshot record every time there is a change in the inventory.
• This design works well when there are infrequent inventory changes. 
• There is no easy way to track inventory movements, except just by calculating deltas from the last period.
• Partitioning measure groups by date might not work in this case. For one product Analysis Services can find last non empty measure in current month partition, but for another product it might have to go back to the very first partition. So there might be problems with performance.
• To improve performance you might consider repeating latest snapshot record at the first day of the year. In such case the earliest partition that Analysis Services should go to get LastNonEmpty child would be first month of that year. This is just a suggestion, not tested by me.

4. Combine snapshots with transactions

In my opinion this is an most flexible option with reasonable execution performance. In SQL Server you should create 2 fact tables: one contains transactions for inventory movements and another fact table contains snapshots. This could be periodic snapshots or snapshots a the point of data change. You create cube with 2 measure groups - one for transactions and another for snapshots. All queries for inventory movements should use measures from transaction measure group. All queries to get inventory at the point in time should use measures from snapshot measure group. You can easily write calculations to combine measures from both measure groups and give you results with reasonable query performance. For example, if you have monthly periodic snapshots and need report for a inventory at the 15th of the month, you take inventory from the previous month snapshot and add transactions from 1st to 15th day.