3D Inventory Tracking & Visualization, in Power BI

In this tutorial you will learn how to create a custom 3D model of your warehouse that can be linked to quantity information of your inventory, in Power BI.  The tools needed to achieve this are:

• Trimble® SketchUp® Pro as the 3D modelling application.
• 3DBI for SketchUp as the custom extension that is able to convert a SketchUp model to a format readable by our 3DBI visual. 
• Microsoft® Power BI®
• 3DBI, the custom Microsoft Certified visual we developed for Power BI, available on Microsoft AppSource.

1 – Drawing the 3D model – stacks of pallets in a row

By using some basic SketchUp modelling we have created 1 component that in an abstract way represents a stackable pallet. We have stacked 5 of these components on top of each other and have given them a sequential “Instance Name”, ranging from 1 to 5.

Next we have grouped these 5 pallets into a new component, which represents the entire stack. We then duplicate this stack another 9 times and place them in front of each other. Doing so we end up with 10 stacks of 5 pallets. Just like we have given a sequentual number to each individual pallet, we now assign a sequential number to the “Instance Name” of each stack, starting from 1 to 10. In our example we have also suffixed each number with a dash (-). This delimeter will later be used in Power BI to be able to separete the sequence for each level in the hierarchy.

Finally, when all stacks that represent a single row are drawn, we group them together into a new component so we end up with 1 component to represent and entire row of 10 stacks. Again, we now duplicate this row component 9 times. This results in 10 rows, all with 10 stacks of 5 pallets each. Like every previous step, we again assign a sequentual number to the “Instance Name” of each row component. Please notice that in your warehouse your rows might have a different identifier, such as “R01”, or “ABC”, … It is important to use the same name on this level as how the row is known in your WMS.

2 – Installing & Using 3DBI for SketchUp

3DBI for SketchUp is available on Extension Warehouse, which means that you can download and install it directly from within SketchUp.

Once installed, you can open the main (and only) dialog of the tool. Prior to exporting it is important to specify a few options:

1. use “Instance Name” as the “Id Provider”. This signals 3DBI for SketchUp to assign the Instance Name of each exported component to each object in the resulting 3DBI file.
2. Enable “Concatenate”. This ensures that the Id that will be assigned to each resulting object in 3DBI is a concatenation of the Ids of the SketchUp model hierarchy. This means that the first pallet (with Instance Name “1”) in the first stack (witn Instance Name “1-“) in the first row (with Instance Name “1-“) will end up in 3DBI with Id “1-1-1”.
3. Depending on if you manually prefixed or suffixed each Instance Name with a separator, you can also automatically assign a separator between each level of the hierarchy. In our case we will not use an additional separator sinch we have suffixed the Instance Name of each stack and row with a dash.
4. Make sure to enable “Single Table Json Data Generator” in order to also export a file containing all exported 3DBI Ids in JSON format. This file will later be used in Power BI to help understand the hierarchy and sequence of each pallet for each row.

3 – Visualize your 3DBI model in Power BI

Using the standard JSON connector that comes with Power BI we first import the data from the .json file exported in the previous step. You will notice that this file contains much information about the exported SketchUp model, however, for our purpose we are only interested in the “3dbiModelId” column.

Since this “3dbiModelId” column contains information about the hierarchy of our model, separated by a delimiter, we will duplicate and split this column by the used delimiter. Doing so we end up with 3 additional columns, one containing the row identifier, one representing the index of the stack the palett is in, and finally one representing the index of the palett in the stack.

We added an extra column to inform us what the index is of the palett in the entire row. For example, the first palett of the second stack actually is the sixth palett in the entire row.

Finally, you can go back to your Power BI report, import the 3DBI visual from AppSource, please an instance of the visual on your report, assign the “3dbiModelId” column to the “Object Identifiers” field of the visual and hit the “insert model” button to load in yor exported .3dbi file.

4 – Load and combine Inventory Information with your Model Information

The inventory data we used for our demo is contained in a simple .csv file. Every row in the .csv file specifies what the quantity was of a given product type, in a given row on a specific date.

Once this data is loaded into Power BI, we will merge this query with the query from the previous step using the Row column in both tables. This result in a new table where information of each individual palett is enhanced with both quantities of the specific row in the warehouse it is part of as well as date information . Using this combined table we can now add an extra calculated column that determines if a palett is actually physically located in the warehouse on a specific date and thus should become visible in the 3DBI visual. A palett is physically there if the quantity of the row is heigher or equal to the index of the palett in that row.

5 – Only display what is physically there

In the previous step we calcualted an extra column that defines if a palett is supposed to be physically located in our inventory. We assign this calculated column as a filter to the 3DBI visual. Doing so, Power BI will filter out rows from our data model that have a “false” value for the “visible” column, prior to sending the information to the 3DBI visual. As a result, the 3DBI visual will only display those palettes of which Power BI states that they are visible.