Multidimensional Modeling w/PQ – Project Planning

Unbelievable flexibility is possible with TBM. Let's see how.

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

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

Introduction

In Table Based Multidimensional Modeling we discussed how TBM gives modelers a straight-forward process for incorporating, essentially, unlimited dimensions in a model. While that alone sets TBM in a class by itself in terms of flexibility, adding dimensions is a relatively rare event when compared to adding items to dimensions.

In my experience, adding periods or products to a model is a given.

In traditional modeling, this could require the modeler to manually make changes to several worksheets or reserve seas of formulas in anticipation of what the maximum number of items might be for any dimension that might change. Those seas of formulas bloat model size. They can also cripple model performance and make navigating and understanding models more difficult. Wouldn’t it be great if models could auto-extend, without modeler intervention, to accommodate any number of items for any dimension everywhere the model is impacted without project bloat?

With TBM and PQ this is easy, assuming you have the skills. So let’s acquire some skills!

Define Requirements

Before starting a project, I create a plan with our goal, resources, and requirements.

Our goal is to automate extending a multidimensional table-based model (MD TBM) when clients decide to add items like more products.

Our resources include the model discussed in Table Based Multidimensional Modeling, Power Query, and my PQ snippets library from which we will copy some reusable functions.

To accomplish our goal requires:

1)?????A place for users to enter the number of items needed for changeable dimensions

2)?????A means for taking those inputs and expanding dimension input tables

3)?????A means for combining items in all permutations and expanding calculations as needed

Define Deliverables

From the requirements we define, conceptually, what needs to be delivered.

1) The requirement to have a place for user entries was met in the original, non-PQ model which has named inputs for:

• Sectors
• Regions
• Products
• Months

2) For the requirement to use inputs to expand tables we will create:

• A PQ function to import inputs from the spreadsheet.
• A PQ function to set the number of rows in a table.
• PQ queries to expand entity tables using functions a. and b.
• PQ queries to expand association tables using function a. and b.

3)?For the requirement to combine all tables we will create PQ queries that:

• Load entity table entries
• Load association table entries
• Perform interim calculations
• Merge tables to create a single table with all dimensional item permutations

Course Outline

I’m going to order this course from easiest and most impactful to most difficult and least impactful.

1.?Project Planning

• Defining project requirements
• Breaking down requirements into deliverables conceptually

2.?Permutations

• Loading Tables to PQ
• Using the PQ Editor
• Adding Columns to Queries
• Merging Queries (Joining Tables)
• Loading PQ Queries to Excel Tables
• Adding Formulas to PQ Queries in Excel

3.?PQ Calculations

• Adding calculations to columns

4. PQ Functions

• Creating a blank query
• Using the Advanced Editor
• Creating Functions
• A few functions from PQ’s library
• A few PQ operators

5.?Extending Entities

• Using Functions

6.?Extending Associations

This concludes Project Planning. The next section is creating Permutations.