Categories
Uncategorized

Lookup Tables and Dimension Tables



Some tables are just a list of lookup values. They hold no further information, so instead of making them dimensions we combine them into the base table. Here the linked values in the Customer Categories, Buying Groups and Delivery Methods tables will be substituted in the Customer table for their foreign key values. The Customer table will then hold those values directly and no longer need the links to the lookup tables.

Next we build a “Geography” dimension that we can attach to a city id and that will let us filter by city, state, country and continent.

And then we add our “Customer” dimension and our “Geography” dimension to our fact tables.

We have added the redundant City Id of the customers to both the Orders and Invoices fact tables. We can then link our Geography dimension to orders and to invoices. Filters flow from the 1 to the many in Power BI so if we filter by State we will get both Orders by state and Invoices by State. If we filter by Customer we will be all the Orders for that customer and all the invoices for that customer.


Last but not least, we add a calendar table.

We end up with a double star. Orders and invoices have (sometimes) different dates. They make up 2 different fact tables.

The 2 fact tables share 3 dimension tables, a geograpy table of cities/states/etc and a customer table and a calendar table. The calendar table is related to the order date for the orders and the invoice date for the invoices, so any filter placed on it will filter both fact tables at the same time.

Our next step will be to give our tables and their fields business user friendly names.

Categories
Uncategorized

Star Schemas


Load your data as a star schema. This type of schema is named because one table holds the events or facts that you want to report on. Around it are other tables that hold information about the fields in the fact table. When these tables are related to the central fact table the relationship diagram looks like a star.

For example: If your central fact table is about sales, you might have dimension tables about customers, about products, about stores.

Arranging your data as a star schema will make everything else you do in Power BI so much easier.

The examples for Slogan 1 are also examples for this slogan

I reworked the 3rd normal form schema of Adventure Works into a Star Schema for reporting.

Categories
Uncategorized

Why use a Star Schema ?



Shape your data into a star schema before you load it into Power BI. It will make EVERYTHING easier and simpler.

Here’s a quote from Russo and Ferrari:

This simple concept bears frequent repetition: Star schemas are good, but everything else might be bad. If you are facing a modeling problem, before doing anything else, ask yourself if you can rebuild your model to move toward a star schema. By doing this, you will likely go in the right direction.

Ferrari, Alberto. Analyzing Data with Power BI and Power Pivot for Excel (Business Skills) (p. 40). Pearson Education. Kindle Edition.

This example is from the sample World Wide Importers database. There is a fact table in the middle that lists Order events. Each order is linked to 4 dimension tables that provide values you can use to filter the fact table and create reports against it.

You can import your data in almost any kind of model and then write DAX code to report on it…..but if you first shape your data into a star schema you will write much less DAX code and the code you do write will be much simplier.

There are many resources on the web for learning about star schemas. Here is one that goes over simple star schemas and some of their more complex variations. How to build a star schema

I promise you, every hour you spend mastering star schemas will save you 100 when you’re writing reports.

Here is a link to a good post by Microsoft on the subject: https://docs.microsoft.com/en-us/power-bi/guidance/star-schema



Categories
Uncategorized

Convert Adventure Works to a Star Schema



Here we have the schema for Microsoft’s sample database Adventure Works

These are just the tables in the “Sales Schema”. They show the classic 3rd normal form relationships that a SQL database is expected to have.

We will work thru the steps, one by one, that transform this into a data schema that will be much easier and more fruitful to run reports against in Power BI

The first thing we will do is combine the header/detail tables for Orders and for Sales.

We remove the extra key for OrderId from Sales_Orders and also the fields that have to do with who picked the order and who was the last person to update it, because we have decided that those fields will not be used in our reports.

We do the same thing for Invoices

Here we also remove the fields for last updates and the comment fields.

Now we load our combined tables into Power BI

For those who might like more technical information on the advantages we get from this strategy, Russo and Ferrari have a great article: https://www.sqlbi.com/articles/costs-of-relationships-in-dax/

We will continue our reshaping adventure in the next post.



Categories
Uncategorized

15 Rules for Report Design



Report and Dashboard design can be very artistic. It’s easy to get carried away and produce something that is stunning but hard to understand.

This page by Russo and Ferrari introduces their free lesson on report and dashboard design. They have reduced them to 15 rules. Be as creative as you like, but first make sure your reports and dashboards follow these simple rules.

https://www.sqlbi.com/blog/marco/2017/05/31/how-to-design-beautiful-dashboards-in-power-bi-the-cure-15-rules-for-everyone-powerbi/

Check out all your reports and dashboards. The human mind has a hard time “thinking of everything” and comparing your work to the 15 rules will undoubtedly show you something you forgot to think about in your design.

Example for slogan 7:simple reports



Categories
Uncategorized

Simple DAX Tricks


Format your Dax Code

Any time you write a measure or calculated column go to DaxFormatter.com.
Paste in your code and the site will format the code according to the standard practice for DAX code.

Complexity is a mind killer.

Copy the newly formatted code and paste it back into your measure.

You’ll thank your self later….and so will anyone else who tries to read your measures.
Example for slogan 6:Format Your Code

Always Use Variables

They make your code easier to read, and easier to debug

They make it execute faster.

Example for slogan 6:Variables

Use a Naming Convention

When writing DAX code never use table names for measure references: [Total Discount] instead of Sales[Total Discount].

Always use table names for column references: Sales[Quantity] instead of [Quantity]

–from Marco Russo sqlbi.com

Example for slogan 6:naming conventions/

Build up Large Measure out of Smaller Ones

You can use DAX Measures inside of other measures. Break your calculations down into small units and write measures for them. Then call those measures inside of your more complicated measures.

This will make your code easier to debug, because it it is in smaller blocks. It will make your code easier to understand, because the way you name your smaller measures can tell what their purpose is.

Example for slogan 6:Measures in Measures

Categories
Slogan

Match you Problems to Dax Patterns

The free Dax Patterns site contains a wealth of material about the different kinds of problems you might find yourself facing in DAX.

It does not provide cut and dried answers but gives you a lot of information about the various ways to solve a given problem. It provides code examples and some thoughts on the pros and cons of different approaches.

Before you dash into solving some reporting problem go to https://www.daxpatterns.com/ and find the Patterns that most closely matches your problem. Reading the pattern FIRST before you try to invent your own solution will save you lots of time and headaches.

This is probably the hardest slogan to follow for a beginner. It takes a fair amount of context to understand the patterns. You might not be able to find the pattern that fits your problem. Just looking for the pattern will build up your experience and increase the chances that you will be able to find a pattern to match your next problem.
Example for slogan 5:look up dax patterns

Categories
Slogan

Make your life easier. Change Power BI’s default settings.

Turn off Power BI’s automatic date intelligence and relationship detection features.

Example for Auto date/time

They can cause ambiguity and confusion in your model.

There are some cases when they are necessary, but those are exceptions to the general rule.
Example for slogan 4:turn off automatic relationships/

Once you turn off Power BI’s auto calendaring functions you will want to add your own Calendar table to all your Power BI data models
Example for slogan 1 and 4:calendar tables/

Turn on “Column Distribution” and “Column Quality” in Power Query

Categories
Slogan

A Data Dictionary makes Great Documentation



Even if you don’t have a data warehouse, using Power BI marks the point where you should start thinking about the difference between reporting data and the day to day data that records transactions.

One simple difference is that all your fields should have their “business names” in Power BI and not the names that they have in your databases (which can often be abbreviated or a little cryptic).

Another difference is that the people construction reports in Power BI don’t need to see all the fields in the database tables. Somewhere in the process of exposing the data in Power BI you should remove fields that will not be useful for reports.

A Data Dictionary helps you accomplish both these goals. It should contain the original table and field names for all your data and then the table and field names that the data displays in Power BI. Perhaps a note field to describe why certain fields were not exposed in Power BI, or why the names of some fields were changed. You can create it in an Excell worksheet.



Categories
Slogan

Follow the Golden Rules

Alberto Ferrari gives us these golden rules.

“Expert modelers always follow these rules:

  • Use a star schema. Always.
  • Hide all the columns in the fact table. Only show measures in the fact table.
  • Expose visible attributes only through dimensions.
  • Test your formulas on small sets of data that you can master and understand at a glance.

These are golden rules. Expert modelers may decide to override the rules, but they do so with a deep and thorough understanding of what they are dealing with. Inexperienced modelers oftentimes choose to avoid rules. Doing so is exciting, if you want to feel the thrill of wandering into a totally unexplored and dangerous maze of complexity. On the other hand, if you want to deploy a sound model for your customers, then obeying these simple rules is a very good step in the right direction.”

https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/