PASSBAC 2015 Revisited

As promised at my session on Excel’s CUBE functions at PASSBAC 2015 in Santa Clara on 22nd April, here are the files that I used at that session.

It was my intention to have just two Excel workbooks, a starting book with the data which I would then build the reports iteratively in the session, and a final workbook showing a fully completed report, on the assumption that we would run out of time to show everything.

Due to my experience with the presentation gremlins, Excel continually crashed on me at an earlier dry-run, I built other workbooks showing the various stages of the presentation. Should Excel continued to misbehave I could then pick up close to that point. I only had one incident at my session, but I think the workbooks are useful to show the different steps demonstrated. All of them are contained within the attached zip file, a small summary of each will align them to my presentation.

• Reporting with Excel CUBE Functions – 0 Start.xlsx
  This file contains a flat table of all of the data as it would typically be found in an Excel workbook.
  • the flat table was split into a partially normalised form of tables, such as States, Cities, Stores, etc.
    The purpose of this was to load those tables into Power Pivot to create the cube worked with in the session (I used Excel tables rather than a database as a precaution against those presentation gremlins)
• Reporting with Excel CUBE Functions – 1 Datamodel.xlsx
  The previous start file tables loaded into Power Pivot, with table relationships established
• Reporting with Excel CUBE Functions – 2 Pivot.xlsx
  A simple pivot table of years against values types (Target or Actual) against states
  • used to check our results back to, and
  • to show how the CUBE function report differed from a standard pivot
• Reporting with Excel CUBE Functions – 3 Static Members.xlsx
  The  beginnings of the report  emerge, but with hardcoded values
  •  CUBEMEMBER formulas for a few named states in the rows, the datamodel years in the columns,
  • CUBEVALUE formulas to show the sales value for the corresponding state and year
  • remember that the sales value at this point is showing an aggregate of the Target and Actual sales values for each state and year as the value type is not yet included
• Reporting with Excel CUBE Functions – 4 Dynamic Erros.xlsx
  In this workbook, the report starts to become dynamic
  • it uses CUBESET and CUBERANKEDMEMBER, together with the ROW()/COLUMN() functions as indexes, to build the row and column headers dynamically
  • It also sets a measure cell with the a parameterised measure type (cells A4 and A3)
  • Where the report rows/columns are greater than the data requires, it shows cell errors
• Reporting with Excel CUBE Functions – 5 Dynamic No Errors.xlsx
  As for the previous workbook, with the cell errors handled
• Reporting with Excel CUBE Functions – 6 Sales Actual v Target.xlsx
  This workbook differentiates between actual and target values, identifying which year should show which
  • Two extra measures are added, Sum of Actual Sales and Sum of Target Sales, both created in Power Pivot, one using an autosummed calculated column (actual), the other a straight DAX measure to show both approaches (although both could and would normally be created as straight DAX measures)
• Reporting with Excel CUBE Functions – 7 Region Slicer.xlsx
  A region slicer is added for this workbook:
  • the CUBEVALUE formulas are amended to include the slicer as a member expression.
  • At this point, the states list does not respond to the slicer
• Reporting with Excel CUBE Functions – 9 Final.xlsx
  The final workbook with the fully completed report. This workbook has the following finishes:
  • all of the intermediate calculations are moved to a separate worksheet named scratch
  • a regional hierarchy created in the Power Pivot model on the States table
  • the index in the CUBERANKEDMEMBER formulas are also adapted to manage any row deletions or insertions (for example, instead of ROW(A1) for the state headings, it uses ROW()-ROW($A$3)) and similar for the year headings)
  • sparklines are included in the table, something you cannot do in a pivot
  • a simple chart of the table is added
  • a value dropdown, sales, cost, or profit, as setup in 4 above,
  • utilises  some extra MDX to show to only the states that are covered by the region(s) selected in the slicer (this is all managed on the scratch worksheet)

The zip file contains a PDF file of the presentation slides. The final file is something I briefly mentioned in the session, that is a workbook which uses Power Query to split the initial flat table into all of the dimension tables used to load the data model. This is not really relevant to the session, but it is included for anyone who thinks that they might find it of interest.

That sums up the files, all of which can be found in the zip file here .

Finally, I showed a slide with a few links for further reading. This is that list, with a few extra details.

• A CUBEMEMBERPROPERTY Equivalent With Power Pivot
  A blog demonstrating a CUBEMEMBERPROPERTY equivalent With PowerPivot
• MDX CUBEd
  A blog post on using MDX in CUBE functions
• Introduction to MDX for PowerPivot Users
  A 5 part series by Chris Webb on using MDX in Power Pivot, useful for understanding how to use MDX within CUBE functions
  1. Part 1
     The first part lays down the foundations
  2. Part 2 – Basic Sets
     The second part covers Excel’s named sets
  3. Part 3 – The Members() and Crossjoin() functions
     The third part covers cube members and the Crossjoin function
  4. Part 4 – Filters
     The fourth part, showing how to restrict the data returned using the various filtering functions
  5. Part 5 – MDX Queries
     The lastin the series, running MDX queries against the Powe rPivot model
• Jan Karel Pieterse’s NameManager
  A free Excel addin that is far superior to Excel’s built-in NameManager
• Excel Formula Parser
  Another free addin that takes a formula in the current cell and displays it in a nice formatted but read-only structure
• FastExcel
  The ultimate Excel performance tool. It is not free, but as well as many facilities for measuring the performance of your spreadsheet, it has an enhanced version of the NameManager tool mentioned above, and now has a formula parser that will write-back to the Excel cell.

We ran out of time to go in detail to adding MDX, but I was asked a question afterwards about creating a list of the top 10 items by some criteria (usually value) using the CUBE functions, which is resolvable with MDX. I will blog this topic at the end of the week.