Technically this data layout is called a star schema.īack to the point of this post. This is the Collie Layout Methodology it gives the user a visual clue that the tables you have to “look up” to see are the Lookup Tables – get it? It is not required, just an easy way to remember. I have placed the lookup tables in my data model at the top of the window and the data table below. I am using the Adventure Works database in the image below. Data tables should always be joined to lookup tables using the Lookup Table primary key column as the join column.

You should not join data tables to other data tables. eg many sales occur on each day, many sales occur for each product. Duplicates are allowed in data table columns.It should be obvious that if you want to join your data table to your product table, you must have a column that defines the product in your data table. In database terms, this column is called the foreign key. There needs to be 1 column that can be connected to relevant lookup tables (assuming you want to connect these tables).There is no requirement that a data table must have a primary key.It could be sales data, budget data, GL data from a financial system, call centre call data, or any other data about activities of some type. I often refer to these additional columns as providing metadata about the primary key.ĭata tables contain (typically) transactional information. Other columns such as Day Name, Month Name, Year, Day of Week number etc all provide detail about the date key. Eg in the calendar lookup table, the date is the primary key. There can be 1 or more additional columns in each lookup table that provide more information about that primary key column.There can be no duplicates in this key column – every row must be unique.In the customer table it would be the customer number, in the products table it is the product code, in the calendar table it is the date, and so on for any lookup table. In database terms this is the primary key. There is always a column of data that uniquely identifies each row of data in the table.Lookup tables always have the following features: Lookup tables contain information typically about 1 business concept/object, eg Customers, Products, Time etc. These two table types are very different and understanding the difference is fundamental to understanding and using Power Pivot and Power BI properly. There are dimension tables (I call them lookup tables) and there are fact tables (I call them data tables). There are 2 different types of tables in a Power Pivot data model. In this post I cover the following scenarios to help explain some of the nuances and benefits of using lookup tables (or not):īefore I move on, it is worth restating the definition of the 2 different types of tables so it is clear in your mind. And not least of all, if you want to get better and advance you skills, you simply must understand these principles well. But it can make a big difference to usability of your model and certainly performance (in some cases). You can be successful using Power Pivot and Power BI without understanding this topic well. It is also worth pointing out that his topic does not mean the difference between “it works” and “It doesn’t work”. This topic is therefore good for everyone, either as a review, a confirmation of what you know, or as a new learning opportunity. Or you may come back and read this again for a second time before it all sinks in. Because there is so much to learn, you may find yourself learning this topic for the first time now even though you are an intermediate user.

However there is so much to learn and you can only absorb so much content before your head begins to explode. I have rated this topic as a beginner topic as it is a fundamental skill to learn on your journey to become a Power Pivot and Power BI Ninja. Today I explain when it is important to create a lookup table and when it is fine to use native columns in a data table. Relationships in Power BI and Power Pivot.

Who Needs Power Pivot, Power Query and Power BI Anyway?.

The Best Way to Install Power BI Desktop.

30 Reasons You Should Be Considering Power BI.

Tabular database columnar database