Hi guys!
In this lesson I will show the different ways to connect to databases, and also why you should avoid using Direct Query only. Instead of this, you can use composite models where you have aggregate tables in import mode and also detailed tables in DirectQuery.
All my videos tend to be longer than usual because I like to go deep on the matter so you can learn for real.
We are going to repeat Power BI Week event on Jan 4th to 8th 2021. Don’t forget to enroll!
Before we get started, let me share some important notes with you:
- My videos in English has a [EN] as tag in the beginning of the video title;
- Every Thursday there will be a new Live session here on Youtube at the same time;
- Lessons will stay Public only for 7 days;
- After this, the videos go to the course as a Bonus to the students of Power BI eXperience;
- Lessons #1 to #4 will stay public and you can check them in the playlist in the description below;
- Materials will be shared only in my Telegram channel;
- The greatest new of all: Second edition of Power BI Week on January 4th to 8th;
- In the Blog you can find several articles that will help you even more with Power BI. Including our lessons here that will be also an article.
Hope you enjoy!!
Remembering the 7 pillars of Power BI:
The 7 pillars for Mastering Power BI
- Extraction (the data from the source)
- Transformation
- Modelling (create table relationships)
- Calculation (where we build KPI’s)
- Visualization
- Distribution (publishing)
- Automation
Today we are going to talk about pillar number 1 – Extraction.
How to connect to databases
There are two ways to connect to database:
1) Import Mode:
– Very fast model, because Power BI caches the data into the memory (which means the visual appears fast – supposing everything is correct);
– It applies a compression and encoding (so it can reduce the size of your data);
– There is a limit of 1Gb per dataset for Pro account
– You can refresh the dataset 8x a day (you can schedule it)
2) DirectQuery:
– Near real time (an advantage that this function has, and you don’t need to schedule);
– It is very slow, because the data still resides in my server (do not perform DirectQuery in transactional databases, otherwise your system can crash);
– PBIX is small (I will explain more as follows).
Extracting Data
Okay, so let’s start opening Power BI Desktop and extract our data.
A new window will open up then you choose “DirectQuery”.
Just as shown in the example, we have two tables “DirectQuery” and “Import Mode” with the same selected tables, and we can see that the size difference is huge, right?! We will see that also the performance will be different (for better).
So, for us to see the performance we can check:
“View” → “Performance Analyzer” → “Start Recording”
Pretty quick, huh?!
Now let’s compare with DirectQuery.
“View” → “Performance Analyzer” → “Start Recording”
What a difference, huh?
So, to optimize you can delete the information you won’t need, just by deleting the column.
Tip: In order to make your development faster, you must work with “Incremental refresh”. When you use incremental refresh and use a small amount of this data, thing will be much easier in the development phase. You will only need the whole date once you publish it.
Even after you do all this and still doesn’t work, then you will have to use “Aggregations”.
To make you all understand better, let’s open Power Query so we can work on our database.
We are going to do the same as we did in the beginning:
Okay, so let’s start opening Power BI Desktop and extract our data.
A new window will open up then you choose “Import”.
Now after making the changes we can click on “Close & Apply”.
DQ + Import = Composite Model
First, let’s start creating a new table called “FactsAggs” then create a relationship.
After creating this relationship between “DimProductSubcategory” table and “FactAggs” we can see that these tables are connected but their source is different since one is from “DirectQuery” and the other one from “Import”.
To make the tables work between them, we can adjust the “Storage mode” by clicking on the table → “Properties” → “Advanced” → “Storage mode” → choose the option “Dual”.
With that been done, just like the example used in the video, let’s use the “Manage aggregations” function.
Okay! After doing so, let’s create a measure:
Total Sales =SUM ( FactOnlineSalesXL[SalesAmount] )
Tip: Always when you are creating measures use “Detailed Tables”. Because Power BI will know from which table should be imported.
For today that’s it, guys!!
There are more links to help with what we talked about today:
https://docs.microsoft.com/pt-br/power-bi/transform-model/desktop-aggregations
https://docs.microsoft.com/pt-br/power-bi/connect-data/desktop-use-directquery
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about
https://powerbi.microsoft.com/pt-br/blog/tag/incremental-refresh/
We are going to repeat Power BI Week event on Jan 4th to 8th 2021. Don’t forget to enroll!
See you next Thursday!!
Cheers,
Leonardo
