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 could occur after I should break 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 special output.
That is what this text is about: Is that this true or false?
Making ready the Instruments
I take advantage of SQL Server as the info supply and SQL Profiler to research what occurs within the database.
SQL Profiler is a Software that may intercept all of the site visitors on a SQL Server occasion.
Thankfully, SQL Profiler is a part of SQL Server Administration 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 Information to Unlocking Database Insights
One other solution 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 instrument.
However let’s return to SQL Server Profiler and easy methods to begin it and put together it for our particular state of affairs.
I can begin SQL profile from the Begin Menu or immediately 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” 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 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, 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 probably be overwhelming for a manufacturing occasion as there will probably be a number of site visitors from different purposes and customers. I’d even add a filter to limit the hint to look at just for site visitors from my NTUserName (My Home windows Consumer ID) to exclude all different site visitors on the database.
Now I click on on Run to begin the Hint.
Importing the Knowledge 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 some other 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 verify 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 totally different SPIDs point out that Energy Question began to separate connections to get the info twice.
This column will probably be necessary when analyzing the site visitors from a Referencing desk.
Create a Reference and verify 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 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 totally 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 Knowledge Mannequin in Energy Question
On this article, I described that some operations will not be doable when creating a brand new desk by referencing an present 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 have to duplicate the unique desk.
It’s because a referencing desk is at all times 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 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 relies on the 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 might want to extract a subset from the unique desk with out altering the unique desk, Reference is the way in which to go, particularly when it’s important to at all times get the output from the referencing desk.
If you’d like 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 while you apply a change to the unique desk, you may want to repeat the logic to the duplicated desk as effectively.
Potential for Conflicts throughout load
One other potential situation 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 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, figuring out these two options’ variations is crucial, as they provide distinct prospects 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 response to Roche’s maxim of Knowledge Transformation:
Knowledge needs to be reworked as far upstream as doable, and as far downstream as obligatory.
However when loading textual content, Excel recordsdata, or different sources to which I can’t ship a question to get the info in the way in which I want it, I have to use both “Duplicate” or “Reference” in keeping with the required final result.
References
Like in my earlier articles, I take advantage of the Contoso pattern dataset. You’ll be able to obtain the ContosoRetailDW Dataset without spending a dime from Microsoft right here.
The Contoso Knowledge will be freely used below the MIT License, as described on this doc.
I modified the dataset to shift the info to modern dates.