Carmel Gunn, a Data Management Specialist from Prodata SQL Centre of Excellence in Dublin introduces the fundamental concepts of Excel BI, which has features specially designed for enterprise users, who need in-depth data analysis, and interactive reporting, by using BI models, such as multidimensional OLAP cubes, or tabular models, often provided by corporate IT divisions.
Measures are business metrics which you wish to analyse, such as the sales amount. However, to make such metrics understandable, we need to break them down by various characteristics, for example by a region or a date, which are known as dimensions, which, in turn, have individual attributes, and which can be displayed in the table or a chart. Those are often part of a meaningful hierarchy, such as country-region-city. One very important type of a dimension, with its own hierarchy, is the date dimension, and it is used in almost every report and a dashboard. Carmel explains them in much detail, please make sure to fully understand those concepts, as they are critical to getting your BI reports right.
After introducing the terminology, and having explained the layout of the Excel BI client, including the Pivot Table, Pivot Table Fields List, and the Fields Section, you will see a step-by-step demo of creating a rich pivot table.
When analysing data, we spend much time looking at measures, summarising them, showing values in different formats, for example as a percentage of the total, while still maintaining the correct context of the hierarchy within which the measure is being viewed. The demo shows all of this, before showing calculated measures, a more advanced concept, which allows you to add metrics that are otherwise absent from the data model you were given. In the demo, Carmel shows how to add a simple MDX Calculated Measure for a gross profit margin.
Finally, you can quickly get a professional-looking report with the provided templates, giving a nice finish to your pivot tables, which Carmel shows in the demo. She also builds another, more advanced dashboard, suitable even for mobile devices, her “How to Make Enterprise Dashboards in Excel” video, make sure to watch it, too.
