Project Botticelli

Excel BI: Basic Concepts Purchase this course

4 September 2013 · 4494 views

Pivot Tables, Measures, Attributes, Dimensions and Hierarchies

Basic Excel Business Intelligence Concepts: Measures, Attributes, Dimensions, 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 2013 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 below to the premium version of this content.

Purchase This Course or Full Access Subscription

Single Course

$200/once

Access this course for its lifetime*.
Purchase
Subscription Best Value

$480/year

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

from $480/year

For small business & enterprise.
Group Purchase

  • Redeem a prepaid code
  • Payment is instant and you will receive a tax invoice straight away.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.

* We guarantee each course to be available for at least 2 years from today, unless marked above with a date as a Course Retiring.

Online Courses