Dimensional modeling: Pivot reports with multiple tables

Name: Dimensional modeling: Pivot reports with multiple tables
Rating: 4.1 (40 reviews)

The schema you need to know to build reports in Excel and Power BI with multiple tables using the Excel Data Model.

Created bySander Kollerie

Last updated 5/2015

English

What you'll learn

Design a database based on the star schema for data analysis
Design tables for Excel PowerPivot
Know why you don't use a relational database model for analytics
What a datawarehouse is
What a star schema is
What de-normalization is and why you should use it in the case of analytics
Make more flexible reports

Course content

8 sections • 24 lectures • 1h 47m total length

What are you going to learn in this course?1:59
Learn how to create a data model to make really flexible reports in Excel or any other data driven application.

What are slowly changing dimensions (SCD)?1:43
Sometimes you want to hold on to the past. This video explains how you can do that.
SCD 1: Overwrite1:26
This slowly changing dimension type overwrites data. This video explains when to use it.
SCD 2: Create another dimension record4:17
This slowly changing dimension type adds an additional record to a dimension table to keep track of the past. This is the most commonly used SCD
SCD 3: Create a ‘current value’ field3:08
This slowly changing dimension can only hold the past for one change. If there is a new change any past change will be discarded. I do not reccomend this SCD type for several reasons.
Slowly Changing Dimensions; Pick one

Time dimension3:44
The date dimension is very important as every report needs a time scale. It is a 'normal dimension', but with specific properties.
Causal dimensions2:20
The why? dimension explained.
Monster dimensions0:56
When things get very very big
Mini dimensions3:42
Sometimes you need to split off some data from a dimension
Heterogeneous dimensions4:02
When you get a lot of empty fields, use this dimension
What type of dimension? Pick one

Step by step design: 6 steps to success3:16
Always follow this step by step design to get the best results in building a star model.
Step 1: Company processes5:35
First step: look at your company
Step 2: Facts3:05
Second step, get the fact table right
Step 3: Dimensions3:58
Thrid step. Build the dimension tables.
Step 4: Measurements4:24
Fourth step. Find all the measurements
Step 5: History3:19
Fith step. Look at your dimensions and implement history
Step 6: Other: mini dimensions, indexes, physical storage4:28
Step 6. Fine tune and think of all the other stuff that needs to be done to finish your project.

An example in Excel: SQL Server Analysis Services10:24
This example shows you the implementation of a dimensional model in a SQL Server database. The processing is done by SQL Server Analysis Services and the reporting in Excel, using only pivot tables.

The Excel part works in both Excel 2010 and Excel 2013.
An example in Excel: Power Pivot13:15
In this example we implemented our dimensional model in a regular SQL Server database and use Excel Power Pivot to create a report. We have an Access database as an alternative to SQL Server to make it easier to test.

You can doenload the example Access database to give it a try.

Requirements

Basic knowledge of computers
A basic understanding of databases is a pro
Know the basics of Excel

Description

Do you analyze data? Are you creating reports? Pivot Tables? Or Dashboards?

This course teaches you how you can create the right data models for analytics and get results fast.

With the multiple table option in Excel Power Pivot and Power BI this method is more important than ever!

You will learn how to create a model for Power BI and Power Pivot tables with more than one table. If you look at the Microsoft Examples you will see dimCustomer and factSales. This course will teach you what that is and how to create it.

You will learn a six step method that you can use for any project to get your model for Power Pivot and multi table pivot tables right.

Make your work easier and impress co-workers with better data models to analyze your data and get more flexible results faster and even more accurate. Learn what fact tables and dimension tables are and how to use them within the star schema. Three words already to impress your co-workers.

This course will teach you how to design a dimensional model according to the Kimball guidelines of creating start models for analyzing data. This model has been around for a very long time to create datawarehouses, but now you can use this for Excel Power Pivot. If you use this model for the structure of your tables, Power Pivot will give you great, flexible results.

At the end of this course you will know how to design and implement the model, fill it and use it. There are three demos in this course that show you how you can put the model to work and how to fill it.

Enroll in this course now to learn how to create the right models for Excel Power Pivot, Pivot Tables (using the multi table option) or any data warehouse solution and get that report done!

Who this course is for:

People who do analytics and want to use PowerPivot
Database designers who need to design a datawarehouse schema using the star schema

Dimensional modeling: Pivot reports with multiple tables

What you'll learn

Explore related topics

Course content

Introduction1 lecture • 2min

Dimensional modeling2 lectures • 11min

History4 lectures • 11min

Different types of dimensions5 lectures • 15min

Design a star7 lectures • 28min

Fill the star2 lectures • 17min

Reports Reports Reports2 lectures • 24min

Course wrap up1 lecture • 1min

Requirements

Description

Who this course is for: