Tecflix

Excel BI: Basic Concepts Purchase the entire course

4 September 2013 · · 4573 views

Pivot Tables, Measures, Attributes, Dimensions and Hierarchies

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.

Log in or purchase access to play the video.

  • How to Make Enterprise Dashboards in Excel 29-min Watch with Free Subscription

  • Excel BI: Basic Concepts 34-min

  • Selecting, Filtering, and Sorting Data in Excel 47-min

Purchase a Full Access Subscription

 
Individual Subscription

$480/year

Access all content on this site for 1 year.
Purchase
Group Purchase

from $480/year

For small business & enterprise.
Group Purchase
 
  • You can also redeem a prepaid code.
  • Payments are instant and you will receive a tax invoice straight away.
  • We offer sales quotes/pro-forma invoices, and we accept purchase orders and bank transfers.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.
  • See pricing FAQ for more detail.
In collaboration with
Project Botticelli logo Oxford Computer Training logo SQLBI logo Prodata logo