we should load a duplicate of the identical information into Energy Question. Energy Question presents 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 could have to load the identical information twice into Energy Question and subsequently into Energy BI.
This may occur after I should cut up the info columns or carry out different transformations on the info, or after I have to extract information from a desk in two other ways.
Energy Question presents 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 adjustments 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 research 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 option to analyze the habits 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 search out out extra about this device.
However let’s return to SQL Server Profiler and the way to begin it and put together it for our particular state of affairs.
I can begin SQL profile from the Begin Menu or instantly 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 Title and choose the TSQL template to trace the queries coming from Energy Question.
- I activate the “Save to File” choice 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 change to the second web page, “Occasion Choice”
- I activate the 2 choices “Present all occasions”.
- Within the checklist 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, Length, 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 will likely be overwhelming for a manufacturing occasion as there will likely be a whole lot of visitors from different purposes and customers. I’d even add a filter to limit the hint to look at just for visitors from my NTUserName (My Home windows Consumer ID) to exclude all different visitors on the database.
Now I click on on Run to start out 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. This can 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 strains 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 will likely be vital when analyzing the visitors from a Referencing desk.
Create a Reference and test what occurs.
Now, I strive the Reference characteristic.
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 won’t 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 undoubtedly two connections, because the column SPID (Session ID) has two completely different numbers for the 2 SQL:StmtCompleted entries.
Which means that, 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 will not be 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 all the time primarily based 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 is predicated on the end result of the referenced desk. Consequently, adjustments utilized to the referenced desk are mechanically utilized to the referencing desk.
To be exact, the adjustments will not be utilized, however the enter desk adjustments due to the change within the referenced desk.
However when it is advisable to 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 all the time get the output from the referencing desk.
In order for you a desk from the identical supply however don’t need adjustments to the unique desk utilized to the brand new desk, then it’s essential to duplicate the unique desk.
Bear in mind that Duplicate means a duplication of the Load logic. Which means that 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 subject is that load conflicts can happen when loading information from some sources. Excel is one among these sources that may trigger issues.
The supply of the issue is that Energy Question tries to load the info in parallel. Some sources will not be 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 have to 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” concerning 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 improve load efficiency.
Nonetheless, understanding these two options’ variations is crucial, as they provide distinct potentialities when loading and reworking information.
Anyway, when loading information from a relational database, I’d create two queries or two views for the 2 tables, as an alternative of offloading any transformation to Energy Question.
In line with Roche’s maxim of Data Transformation:
Knowledge needs to be remodeled as far upstream as potential, and as far downstream as vital.
However when loading textual content, Excel information, 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 keeping with the required final result.
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 may be freely used underneath the MIT License, as described in this document.
I modified the dataset to shift the info to up to date dates.
