Have you loaded data into a new workbook lately? You probably noticed something different on the Data Source tab. Data can be in Base tables or Related tables, and you can relate data across shared dimensions. I wanted to see how it’s used in doing an inventory analysis.
Preparing an inventory analysis has been difficult, requiring nesting table calculations and including LODs to avoid creating a cyclical calculation (note that the same is true for cash flow, or production planning). I wanted to see how “Shared Dimensions” could be used to simplify the process.
Let’s start by looking at the data and the analysis goals. In the following fictional example, I have created data tables that would be typical of those downloaded from an ERP system – yours may vary but the concept is the same:
Table |
Contents |
Sales History |
Sales quantity by customer, product, and order date |
Customer Master |
Customer Name, Channel, Location, Price list |
Product Master |
Product hierarchy |
Price List |
Price list ID, Product, Price |
Product Standard Cost |
Product Name, standard cost, MSG |
Production Schedule |
Product name, production date, production quantity |
Date ladder |
Sequential dates need to scaffold |
Beginning Inventory |
Product Name and quantity |
There could be many more, but this will suffice. For an inventory analysis, we want to look at the difference between the quantity of product sold and the product produced across a timeline – The dates are different – (production and order dates) so we will need to place them on a common calendar before accumulating the totals over time.
Additionally, the data are in different tables – no single table contains all the dimensions needed to complete the analysis so we will need to “share the dimension” across different paths when relating the tables. It will be easier to see with the specific example – starting with a single path based on the Sales History:
Pretty easy, just add the Sales History as a Base table to the canvas – (it is the same process you have used in the past – just didn’t consider it a Base Table)
A typical text table might look like this:
With all the data coming from a single table, drag dimensions to rows or columns and add a measure to the marks card
Next, you may want to group the products into product lines and product groups and the customers into channels or regions – you could use “Group” for a single level but run into problems with multilevel grouping. There is an easier way by using the Master data from the ERP system.
Add in the Customer Master
To get the Channel information, drag in the Customer Master and connect on the Customer Name (note it can be different in each data set) and you have added a “Related Table” to the view. Additionally, the customer location information and the Price list associated with the customer are also now in the combined data set (Note – pricing often varies by channel and by customer within the channel for the same products – hence different price lists)
Now break out sales by channel and customer from the Master data and combine that with the Product Name from the Sales history:
The dimensional data comes from two different tables to show more detailed Sales quantities.
Similarly, the Product Master can connect on Product Name – note this now creates an additional path stemming from the sales history because the Customer Master and Product Master do not have a “Shared Dimension”
As a result, we can group the product data along a hierarchy and add it to the chart.
OK, so far it’s been straightforward – and the connections have been intuitive – join the Customer data on Customer Name and the Product data Product Name. Now I want to add in pricing information – but pricing is on Price Lists broken down by Product – which is part of the Product Master and on Price List ID which is on the Customer Master – 2 different paths in the data structure –
Connecting the Price List to either the Customer Master or Product Master will treat the data like a blend – connecting the Price List to the Product Master on the Product
Will total the quantities across all customers
The Price List is related to two unrelated dimensions in the structure – we want to reduce that by one – A traditional join between the Customer Master and Price List tables on Price List ID will do what we need.
The Join looks like this: Open to the physical table and join on the Price List.
And now the pricing can be at the individual customer/product combination level.
Pricing comes from the Customer Master (combined table) and Quantity from the Sales History.
We can look across each path of the data structure using the “shared dimension” Product Name
Time to add a level of complexity with a completely new Base Table – in this case, the Production Schedule –
the first approach is to connect the production data to the Product Master
And that appears to work until we add the Order date field to the view – and we get a warning message :
The question is what to do with the two date fields – Order Date and Production date –
they are independent and need to be placed on a common calendar – and for that, I have added the Date Ladder – as a base layer and Cartesian joined it to the Sales History
Now the Scaffold Date can be directly related to the Order Date and the Production Date and used to create the Running Sales and the Running Production totals – the difference being the change in inventory
The Production and Sales by Product Name and month of the Scaffold date can be calculated directly
Production
{ FIXED [Product Name (Production schedule )],[Production date],[Date]
:avg( if [Production date]=[Date] then [Production Qty] end )}
Sales
{ FIXED [Order date],[Date],[Product Name1]:
sum( if [Order date]=[Date] then [Quantity] end)}
In text chart form it would look like this where the Date is from the Scaffold and the Product Name is from the Production Schedule
To determine the ending inventory each month we need to connect the Starting Inventory by product and then add the monthly production and subtract out the sales
The ending inventory each month results from this table calculation:
window_sum( sum({ FIXED [Product Name (Production schedule )], DATETRUNC(‘month’, [Date]):
sum(zn([jd production])) -sum(zn( [jd sales quantity]) ) + sum(zn( [jd starting inventory ])) }),FIRST(),0)
which is the running total of the starting inventory and production less the sales each month
A portion of the detailed text chart would look like this.
A line chart of the ending inventory by-product would look like this:
Negative inventory ending levels are colored Red to stand out.
So, what have we learned –
Multi-fact table and shared dimensions are very useful in performing cyclical calculations. After all, this was much easier than using nested table calculations. At the same time, they are not a total solution in themselves.
We still need to use a join to add the Price List IDs to the Customer Master and then use the result as a shared dimension and needed a date scaffold to create a common calendar to view the production and order data by date.
Base Tables – Frequently updated transactional data tables make good base tables – data is changing or
amended like the Sales History data here.
Shared Dimension Tables – Master data or data that is constant (infrequently changing like assignment of sales reps to accounts) made to shared dimension tables.
Connecting across paths – Will issue a warning about unrelated tables – the connection can be done but be aware that the aggregation will be like a “Blend.”
Connecting 2 dimensions in a shared dimension table –– plan on combining the 2nd dimension by using a conventional join – it will act like a vlookup
Creating a common calendar for 2 (or more) independent date fields – create a date scaffold to use as a base table and Cartesian join it to other tables – at least for now.
The workbook used here can be downloaded from Link to workbook.
I hope this helps – I think it is easier than the nested table calculation that I presented several years ago – but you are the judge.
Jim
