we should load a duplicate of the identical information into Energy Question. Energy Question affords 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 must 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 must extract information from a desk in two other ways.
Energy Question affords 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 take advantage of SQL Server as the info supply and SQL Profiler to investigate what occurs within the database.
SQL Profiler is a Software that may intercept all of the site visitors on a SQL Server occasion.
Fortuitously, SQL Profiler is a part of SQL Server Administration Studio (SSMS) and is free to make use of.
You possibly can learn this piece on Medium for a extra detailed description of SQL Server Profiler: Mastering SQL Server Profiler: A Step-by-Step Information to Unlocking Database Insights
One other strategy to analyze the habits of those two Options is Energy Question Diagnostics.
I wrote this piece on Medium about Energy Question Diagnostics: Analyzing Energy Question 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 have 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” 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 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 under 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 site visitors on that database:
With out this filter, I’ll get the site visitors on all databases. This will likely be overwhelming for a manufacturing occasion as there will likely be lots of site visitors from different functions and customers. I’d even add a filter to limit the hint to observe just for site visitors from my NTUserName (My Home windows Person ID) to exclude all different site visitors on the database.
Now I click on on Run to begin the Hint.
Importing the Information into Energy Question
I take advantage of a View within the database named FactOnlineSales_withCustomer as my supply.
I import this View into Energy Question with out every other 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 examine 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 possibly can 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 site visitors from a Referencing desk.
Create a Reference and examine what occurs.
Now, I attempt 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 won’t delete any information from the saved Hint file):
After refreshing the Information from Energy BI, I get this end in 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, from the load site visitors perspective, there isn’t any distinction between duplicating and referencing a desk.
However when each trigger the identical site visitors on the supply, why ought to I take advantage of 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: Changing a Flat Desk to a Good Information Mannequin in Energy Question
On this article, I described that some operations will not be attainable when creating a brand new desk by referencing an current desk.
For instance, Energy Question doesn’t permit merging a referencing desk with the unique desk due to a round reference.
In such a case, I have to duplicate the unique desk.
It is because a referencing desk is all the time 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 wholly 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 you have 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 very 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 you have to duplicate the unique desk.
Bear in mind that Duplicate means a duplication of the Load logic. Which means whenever 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 difficulty is that load conflicts can happen when loading information from some sources. Excel is certainly one 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 will not be in a position to deal with parallel connections.
In such a case, you have 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 any distinction between “Duplicate” and “Reference” relating to load efficiency or community site 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 important, as they provide distinct potentialities when loading and remodeling information.
Anyway, when loading information from a relational database, I might 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 Information Transformation:
Information needs to be reworked as far upstream as attainable, and as far downstream as needed.
However when loading textual content, Excel information, or different sources to which I can not ship a question to get the info in the best way I want it, I have to use both “Duplicate” or “Reference” based on the required final result.
References
Like in my earlier articles, I take advantage of the Contoso pattern dataset. You possibly can obtain the ContosoRetailDW Dataset without spending a dime from Microsoft right here.
The Contoso Information could be freely used underneath the MIT License, as described on this doc.
I modified the dataset to shift the info to up to date dates.
