The 5 Things Every Excel User Needs to Understand About Power BI
Concept 2: Data Tables
Data tables are the building blocks of Power BI. Understanding data tables and data models is absolutely vital for using Power BI, and perhaps the concepts with which new users have the least familiarity. For spreadsheet users, one link between Excel and Power BI is the Power Pivot. Power Pivots, just like Power BI, make use of joins to link different tables together. If you’ve built Power Pivot reports in Excel, then building Power BI data models will be very similar. For users who aren’t familiar with data tables and models, the information below provides a brief overview.
Concept 2: Data Tables
Data tables are the basis for all Power BI reports. A table has a number of discreet elements:
Columns, which have a header name and a single type of data (e.g. Text, Decimal Number, Whole Number).
Rows, also known as records, describe individual events or entities. Often the first column of a row contains a unique identifier, which is called a primary key. Primary keys are necessary to join tables together.
Cells exist at the intersection of rows and columns and contain unique pieces of information about a record.
Power BI uses two types of tables: Fact Tables and Dimension Tables. As the name suggests, Fact tables contain key information such as Sales Amount, Product Counts, Units Sold, etc. Various attributes which can be used to describe the key information are also included in Fact Tables. Commonly included attributes are location, dates, products, and segment. These attributes connect to Dimension Tables.
An efficient fact table is one where only the most detailed element of a dimension is included. Using the time dimension as an example, where we are looking at monthly sales, an efficient data model has only a single date column with month as the most detailed element. That month column is used to join to the Date Dimension table, which will also contain Quarter and Year columns. Including quarter and year columns in the Fact Table unnecessarily increases table size.
Power BI works most efficiently when fact tables are long and skinny, meaning that there are many rows and few columns. Understanding how to build long, skinny fact tables is important when creating reports. An easy way to reduce the number of columns is through the use of dimension tables.
Dimension tables have two essential functions in a data model. The first of these is provide additional context for a dimension, e.g. month, quarter and year in the time dimension, or postal code, city, state and country in the geographic dimension. Instead of adding all these columns to fact tables, they can be included in dimension tables, leading to a much more efficient data model. The second function of dimension tables is to join multiple fact tables together. Fact Tables are joined with Dimension Tables using Primary Keys.
Primary Keys are unique identifiers for each row in a table. In the screenshot below, the ProductID column is the Primary Key. The most important feature of the key is that each value in column is unique and appears only once. This uniqueness is necessary for creating relationships between tables.
Comments