Multidimensional Modeling w/PQ – Permutations

Table of Contents

• Project Planning – Project Introduction and Project Plan
• Permutations – Merging Queries to Create All Permutations for Calculations
• PQ Calcs – Calculate values in PQ
• PQ Functions – Creating Functions
• Auto-Extend Entities – Creating Auto-Extending Excel Tables for Entities
• Auto-Extend Associations – Creating Auto-Extending Excel Tables for Associations

Introduction

In an earlier post, I shared my permutation formula which calculates all possible combinations of dimensional items which made multidimensional modeling straight forward for as many dimensions and items as we need. This makes Table Based Modeling (TBM) extremely flexible.

In this post we show how formulas are totally unnecessary to achieve this task and we will show how our model calculations can accommodate as many dimensional items as we want without any modeler intervention.

Why is that important?

• Every distinct formula requires auditing or review; thus, fewer formulas result in less work for auditors and reviewers and less chance they might miss something important.
• Every formula cell introduces a potential error to our model; thus, fewer formula cells reduce model risk.
• Every time the modeler must make changes to the model we incur delays and increase costs and the risk of introducing errors.

?

Terms and Abbreviations

• Dimension – Entity types such as periods, products, market sectors, etc.
• Item – A specific entity in a dimension such as January (item) in periods (dimension).
• Entity Table – An Excel table containing one dimension’s items.
• Association Table – An Excel table linking items in two or more dimensions such as distribution percentage of products (dimension #1) by regions (dimension #2)
• TBM – Table Based Modeling – uses tables and structured references exclusively
• MD – Multidimensional Modeling – uses multiple entities to determine a single result
• PQ – Power Query
• Cross Join - ?A type of join that combines each row from the first table with each row from the second table.

? ?

Learnings

In this section we will learn about:

1.?Loading Tables to PQ

2.?Using the PQ Editor

3.?Adding Columns to Queries

4.?Loading PQ Queries to Excel Tables

5.?Adding Formulas to PQ Queries in Excel

Process Overview

Below is the basic process we will follow. We will discuss each step in more detail.

1.?Load entity tables into PQ

2.?Cross join all tables

3.?Load query to Excel

4.?Add formulas to query table

5.?Test??

Materials

To follow along with this discussion, download: BXL MD TBM.xlsx (BXL=Beyond Excel, MD=Multidimensional, TBM=Table Based Modeling) https://www.dropbox.com/s/43bey4197ciux0m/BXL%20MD-TBM.xlsx?dl=1

?

Preparations

1.??Open BXL MD TBM.xlsx and save it as Test.xlsx

2.?Add a new worksheet and name it PQ Process

?

Load Entity Tables into PQ

Find these tables in Test.xlsx on worksheet Inputs. These are our Entity (Dimension) user input tables.

tblSectors

The first table we will load is our Sectors table (tblSectors)

Right click anywhere in tblSectors and select Get Data from Table/Range. This launches PQ Editor and loads tblSectors into a query.

To make things simpler to replicate, click on Changed Type in the APPLIED STEPS list box at the bottom right of PQ Editor and remove that step by hitting the Delete key.?

tblRegions

We can use our tblSectors query to create our tblRegions query. Right click on tblSectors in the Queries[1] pane and select Duplicate. This adds tblSectors (2) to our Queries [2] pane.

We need to rename it tblRegions. To rename it, Find tblSectors (2) in the PROPERTIES list box and type over tblSectors (2) with tblRegions.

We need to change the formula to load tblRegions. To do that, in the APPLIED STEPS list box click Source. That put the Source step into the formula box located above the table display. In this formula, replace tblSectors…

= Excel.CurrentWorkbook(){[Name="tblSectors"]}[Content]

…with tblRegions.

?

tblProducts

In the Queries[1] pane, right click on tblSectors and select Duplicate.

In the PROPERTIES list box change the query name from tblSectors (2) to tblProducts.

In the APPLIED STEPS list box, click on Source

In the formula box, replace tblSectors with tblProducts

?

tblMonths

In the Queries[1] pane, right click on tblSectors and select Duplicate.

In the PROPERTIES list box change the query name from tblSectors (2) to tblMonths.

In the APPLIED STEPS list box, click on Source

In the formula box, replace tblSectors with tblMonths

Cross Join all tables

We have all dimensions loaded into PQ. Now we need to create all possible permutations. to do this we will use a "Cross Join". A cross join combines each row from the first table with each row from the second table. To create a cross join in PQ, all we do is:

1. Start with one table
2. Add a column to the first table containing the second table
3. Expand the new column
4. Repeat steps 2 & 3 for any more tables to be cross joined.

tblSectors

This is our first table. Right click on tblSectors and select Reference. This adds a new query called tblSectors (2).

Change query tblSectors (2)'s name to tblProcess

Remove columns not needed for this process by selecting all columns right of Name (Rebate Percentage, Demand A, Demand B) and then hitting the Delete key.

tblRegions

Cross join tblRegions by adding a custom column by using PQ’s ribbon option Add Column > Custom Column. Enter Region under New column name and =tblRegions under Custom column formula. Click OK.

The new column Region has an odd icon that looks like two arrows pointing away from each other. This is the expand/aggregate icon. Click it and expand this column to add ID and Name (make sure Unit Delivery Costs is unchecked). Click OK.

tblProducts

Cross join tblProducts using PQ’s ribbon option Add Column > Custom Column. Enter Product under New column name and =tblProducts under Custom column formula. Click OK.

Expand Product column by clicking the expand/aggregate icon. Add ID and Name only. Click OK.

tblMonths

Cross join tblMonths using Add Column > Custom Column. Enter Month under New column name and =tblMonths under Custom column formula. Click OK.

Expand Month column and add ID and Name only. Click OK.

Final Touches

We have our permutations, but I’d like the columns renamed and reordered.

To rename our columns, double click the column heading and type over it. Rename our columns: Sector, Sector Name, Region, Region Name, Product, Product Name, Month, Month Name

I prefer the columns reordered with the IDs in the left most columns. To reorder columns, click the column heading once, then click and drag the column to its position

Load to Query Excel

In PQ’s ribbon click Home > Close & Load > Close & Load to …

Select Only Create Connection then click OK.

Open worksheet PQ Process and select cell A6.

If the Queries and Connections pane isn’t displayed on the right side of our workbook, then from Excel’s ribbon click Data > Queries and Connections.

Right click on tblProcess, select Load To... and, this time, select Table then click OK.?

Add Formulas to Query Table

We are going to take the easy way here. Go to worksheet Process and from tblProcess, select the column headings and first row starting at column Region Sales Distribution Per Sector to the end. Copy.

Go to worksheet PQ Process and select the cell just right of column headings and paste.

NOTE! This pasted formulas from the old tblProcess which, in our new table, now reference the old table. To fix that, select the entire table and use keyboard shortcut CTRL-H (or ALT-E-E) to bring up the Find and Replace dialog. In Find what: type tblProcess. Leave Replace with: empty. Click Replace All. This turns all remote references to the old table into references local to our new table.

Test

At this point we should have a query table (named tblProcess_2) in worksheet PQ Process. It has all permutations for every item in all dimensions. It has normal Excel formulas attached to it. If we add items to our entity tables, this table should grow to accommodate them. Let’s see if it does.

Go to worksheet Inputs. Select the last row in the Sectors table. There should be a tiny box in the lower right corner of our selection. Click and drag that tiny box down one more row (Ignore the red warnings. They tell us we have more rows in our Sectors table than what is indicated in our user input for Sectors in the Model Properties table).

Go back to the PQ Process worksheet and scroll to the bottom of the table. Now use Excel’s menu option Data > Refresh. If we did everything correctly the new table grew automatically!

We just added new dimensional items and our model calculations accommodated them with NO MODELER INTERVENTION!

Loose Ends

All of our PivotTables depend on the old tblProcess. We can select each PivotTable and change the data source to our new table: tblProcess_2. After that, we can delete tblProcess and rename tblProcess_2 to tblProcess.

The Excel formulas we attached to our query rely on values in interim calculation tables. Those calculations tables must also extend to include all permutations. We will cover that in our next section.?

#exceltips?#excel?#exceltutorial?#excelskills?#multidimensional?#financialmodeling?#financialmodelling?#financialmodel?#financialanalysis?#businessmodeling?#businessanalysis