we should load a replica of the identical information into Energy Question. Energy Question provides two methods to get the identical information twice: Duplicate and Reference. Let’s have a look at the distinction between these two options and when to make use of one over the opposite.
Introduction
I’ll must load the identical information twice into Energy Question and subsequently into Energy BI.
This could occur after I should break up the info columns or carry out different transformations on the info, or after I must extract information from a desk in two alternative ways.
Energy Question provides us two options to perform this:
- Duplicate:
This duplicates the M-Code for the desk and creates a brand new desk. - Reference:
This takes the output of a desk and creates a brand new desk. All modifications made to the supply desk are additionally seen within the referencing desk.
You may argue that after I use Reference, the info is learn as soon as from the supply, as I take the output of 1 desk and reuse it for a distinct output.
That is what this text is about: Is that this true or false?
Getting ready the Instruments
I exploit SQL Server as the info supply and SQL Profiler to investigate what occurs within the database.
SQL Profiler is a Instrument that may intercept all of the visitors on a SQL Server occasion.
Thankfully, SQL Profiler is a part of SQL Server Management Studio (SSMS) and is free to make use of.
You’ll be able to learn this piece on Medium for a extra detailed description of SQL Server Profiler: Mastering SQL Server Profiler: A Step-by-Step Guide to Unlocking Database Insights
One other approach to analyze the conduct of those two Options is Energy Question Diagnostics.
I wrote this piece on Medium about Energy Question Diagnostics: Analyzing Power Query with Load Traces
I invite you to learn it to seek out out extra about this device.
However let’s return to SQL Server Profiler and tips on how to begin it and put together it for our particular situation.
I can begin SQL profile from the Begin Menu or straight from SSMS:
After beginning it, I need to choose the connection to my native SQL Server Occasion:
Subsequent, I arrange the Hint.
- I give it a Identify and choose the TSQL template to trace the queries coming from Energy Question.
- I activate the “Save to File” possibility and choose the folder for the Hint file.
I can later open this hint file in Profiler and look into it in additional element if I want. - I swap to the second web page, “Occasion Choice”
- I activate the 2 choices “Present all occasions”.
- Within the record of all Occasions, I choose SQL:StmtStarting and SQL:StmtCompleted to get the SQL code from the queries.
- I deselect all occasions, besides the three beneath SQL.
- I deselect most columns besides these to trace the Question Textual content, Begin and Finish Time, Period, and different statistics.
That is the way it takes care of the setup (With the choice “Present all occasions” deactivated):
Lastly, I arrange a filter on my supply database to hint solely the visitors on that database:
With out this filter, I’ll get the visitors on all databases. This might be overwhelming for a manufacturing occasion as there might be a variety of visitors from different purposes and customers. I would even add a filter to limit the hint to observe just for visitors from my NTUserName (My Home windows Person ID) to exclude all different visitors on the database.
Now I click on on Run to begin the Hint.
Importing the Knowledge into Energy Question
I exploit a View within the database named FactOnlineSales_withCustomer as my supply.
I import this View into Energy Question with out another transformation steps. It will trigger Energy Question to get the info with a easy SQL Question from the database.
I can discover this question with out issue within the Hint Log.
Create a Duplicate and test what occurs.
After importing the info into Energy Question, I create a Duplicate of the imported desk and cargo the info into Energy BI:
As anticipated, I see the identical Question executed twice in SQL Profiler:
You’ll be able to see that the info has been retrieved twice with the identical variety of rows (The final two traces within the hint.
I anticipated this to occur, as Duplicate copies the M-Code to create a brand new desk.
One other key column is SPID. That is the inner session ID on the SQL Server occasion. Two completely different SPIDs point out that Energy Question began to separate connections to get the info twice.
This column might be vital when analyzing the visitors from a Referencing desk.
Create a Reference and test what occurs.
Now, I strive the Reference function.
I first delete the desk “FactOnlineSales_WithCustomer_Duplicate” and create a Reference from the unique “FactOnlineSales_WithCustomer” desk:
In SQL Profiler, I can clear the view to see solely new entries by clicking on the eraser button to clear the hint (This is not going to delete any information from the saved Hint file):
After refreshing the Knowledge from Energy BI, I get this lead to SQL Profiler:
Astonishingly, the info was learn twice within the database.
I can see that there are positively two connections, because the column SPID (Session ID) has two completely different numbers for the 2 SQL:StmtCompleted entries.
Because of this, from the load visitors perspective, there isn’t a distinction between duplicating and referencing a desk.
However when each trigger the identical visitors on the supply, why ought to I exploit Duplicate over Reference in Energy Question?
When utilizing Reference and when Duplicate
A while in the past, I wrote an article about changing a flat desk to a Star Schema with Energy Question: Converting a Flat Table to a Good Data Model in Power Query
On this article, I described that some operations aren’t potential when creating a brand new desk by referencing an current desk.
For instance, Energy Question doesn’t enable merging a referencing desk with the unique desk due to a round reference.
In such a case, I need to duplicate the unique desk.
It is because a referencing desk is at all times based mostly on the final step of the referenced desk.
That is the important thing distinction between “Duplicate” and “Reference” in Energy Question:
- Duplicate is a completely new load with out dependency on the unique desk. Modifications to the unique desk don’t have an effect on the duplicated desk.
- A Referencing desk relies on the end result of the referenced desk. Consequently, modifications utilized to the referenced desk are mechanically utilized to the referencing desk.
To be exact, the modifications aren’t utilized, however the enter desk modifications due to the change within the referenced desk.
However when you should extract a subset from the unique desk with out altering the unique desk, Reference is the best way to go, particularly when it’s important to at all times get the output from the referencing desk.
In order for you a desk from the identical supply however don’t need modifications to the unique desk utilized to the brand new desk, then it’s essential to duplicate the unique desk.
Remember that Duplicate means a duplication of the Load logic. Because of this once you apply a change to the unique desk, you may want to repeat the logic to the duplicated desk as nicely.
Potential for Conflicts throughout load
One other potential situation is that load conflicts can happen when loading information from some sources. Excel is one in every of these sources that may trigger issues.
The supply of the issue is that Energy Question tries to load the info in parallel. Some sources aren’t capable of deal with parallel connections.
In such a case, it’s essential to change a parameter to keep away from parallel loading:
The default worth is 4.
If issues happen, you may must set both a decrease Customized worth or set it to “One (disable parallel loading)” to keep away from any points altogether.
Conclusion
In Energy Question, there isn’t a distinction between “Duplicate” and “Reference” relating to load efficiency or community visitors.
Each load the info independently from the supply with a separate connection.
Due to this fact, I debunked the parable that “Reference” can enhance load efficiency.
Nevertheless, figuring out these two options’ variations is important, as they provide distinct potentialities when loading and reworking information.
Anyway, when loading information from a relational database, I might create two queries or two views for the 2 tables, as a substitute of offloading any transformation to Energy Question.
In accordance with Roche’s maxim of Data Transformation:
Knowledge ought to be remodeled as far upstream as potential, and as far downstream as essential.
However when loading textual content, Excel recordsdata, or different sources to which I can’t ship a question to get the info in the best way I would like it, I need to use both “Duplicate” or “Reference” in accordance with the required consequence.
References
Like in my earlier articles, I exploit the Contoso pattern dataset. You’ll be able to obtain the ContosoRetailDW Dataset without spending a dime from Microsoft here.
The Contoso Knowledge will be freely used beneath the MIT License, as described in this document.
I modified the dataset to shift the info to modern dates.
