Monday, November 28, 2022
HomeBusiness IntelligenceSlowly Altering Dimension (SCD) in Energy BI, Half 2, Implementing SCD 1

Slowly Altering Dimension (SCD) in Energy BI, Half 2, Implementing SCD 1

Slowly Changing Dimension (SCD) in Power BI, Part 2, Implementing SCD 1

I defined what SCD means in a Enterprise Intelligence answer in my earlier put up. We additionally mentioned that whereas we don’t count on to deal with SCD2 in a Energy BI implementation, we are able to deal with situations much like SCD1. On this put up, I clarify how to take action.

State of affairs

We have now a retail firm promoting merchandise. The corporate releases the record of merchandise in Excel format, together with record value and supplier value, yearly. The product record is launched on the primary day of July when the monetary 12 months begins. We have now to implement a Energy BI answer that retains the most recent product knowledge to analyse the gross sales transactions. The next picture exhibits the Product record for 2013:

Products List 2013 in Excel
Merchandise Listing 2013

So annually, we obtain the same Excel file to the above picture. The information are saved on a SharePoint On-line web site.

State of affairs Defined

Because the earlier put up explains, an SCD1 all the time retains the present knowledge by updating the outdated knowledge with the brand new knowledge. So an ETL course of reads the info from the supply, identifies the prevailing knowledge within the vacation spot desk, inserts the brand new rows to the vacation spot, updates the prevailing rows, and deletes the eliminated rows.

Right here is why our state of affairs is much like SCD1, with one exception:

  • We don’t really replace the info within the Excel information and don’t create an ETL course of to learn the info from the Excel information, determine the adjustments and apply the adjustments to an middleman Excel file
  • We should learn the info from the supply Excel information, hold the most recent knowledge whereas filtering out the outdated ones and cargo the info into the info mannequin.

As you see, whereas we’re taking a really totally different implementation method, the outcomes are very related with an exception: we don’t delete any rows.

Implementation

Here’s what we should always do to attain the aim:

  • We get the info in Energy Question Editor utilizing the SharePoint Folder connector
  • We combite the information
  • We use the ProductNumber column to determine the duplicated merchandise
  • We use the Reporting Date column to determine the most recent dates
  • We solely hold the most recent rows

Getting Information from SharePoint On-line Folder

As we get the info from a number of information saved on SharePoint On-line, we now have to make use of the SharePoint Folder connector. Observe these steps:

  1. Login to SharePoint On-line and navigate to the location holding the Product record Excel information and duplicate the location URL from the browser
Getting SharePoint Online Site URL
Getting SharePoint On-line Web site URL
  1. From the Get Information within the Energy BI Desktop, choose the SharePoint Folder connector
  2. Click on Join
Connecting to SharePoint Online Folder from Power BI
Connecting to SharePoint On-line Folder from Energy BI
  1. Paste the Web site URL copied on step 1
  2. Click on OK
Connecting to SharePoint Online Folder from Power BI using the SharePoint Folder connector
Connecting to SharePoint On-line Folder from Energy BI utilizing the SharePoint Folder connector
  1. Click on Remodel Information
Transforming data in Power Query Editor
Remodeling knowledge in Energy Question Editor

  1. Click on the filter dropdown on the Folder Path column
  2. Discover the Merchandise Listing folder internet hosting the Excel information and choose it
  3. Click on OK
Filtering a folder in SharePoint Online Site in Power Query Editor in Power BI
Filtering a folder in SharePoint On-line Web site in Energy Question Editor
  1. Rename the question to Product
Renaming a query in Power Query Editor in Power BI
Renaming Query1 to Product

Thus far, we’re linked to the SharePoint On-line Folder in Energy Question Editor. The subsequent step is to mix the Excel information.

Combining Information

We have now a number of choices to mix binary information in a desk from the Energy Question Editor. On this put up, we use essentially the most simple technique:

  1. Click on the Mix Information button from the Content material column
Combining Excel Files in a Table in Power Query Editor in Power BI
Combining Excel Information in a Desk in Energy Question Editor
  1. Choose the ProductList desk
  2. Click on OK
Selecting the object to be extracted from each file on the Combine Files window in Power Query Editor in Power BI
Deciding on the item to be extracted from every file on the Mix Information window in Energy Question Editor

The above course of creates a few queries grouped in separate folders, as proven within the following picture:

The results of combining Excel files
The outcomes of mixing Excel information

Thus far, we now have efficiently mixed the Excel information. The subsequent step is to maintain the most recent knowledge solely.

Maintaining the Newest Information

Within the subsequent few steps, we glance nearer on the knowledge, and we implement a mechanism to determine the most recent knowledge, hold them and cargo them into the info mannequin.

  1. Trying on the outcomes of the mixed knowledge exhibits a Supply.Title column that we do now require to maintain, so we take away it by choosing it and clicking the Take away Columns button from the Dwelling tab
Removing Columns in Power Query Editor in Power BI
Eradicating Columns in Energy Question Editor

Thus far, we now have linked to the SharePoint On-line Folder and mixed the contained Excel information. Let’s take a look at the info and see what we’ve received. I sorted the info by ProductNumber to raised perceive the info adjustments. The next picture exhibits the outcomes:

Repeated merchandise in several lists

Because the above picture exhibits, there are a number of merchandise showing in a number of lists. That’s precisely what we anticipated to see. The aim is to maintain the most recent product knowledge solely based mostly on the Reporting Date. So we should always get the ProductNumber and the utmost of the Reporting Date. To realize this, we use the Group By performance in Energy Question Editor. Utilizing the Group By from the UI within the Energy Question Editor makes use of the Desk.Group() operate in Energy Question. Because the Group By course of doesn’t want the info to be sorted we take away the Sorted Rows step. With that, let’s get the job performed.

  1. Choose the ProductNumber column
  2. Click on the Group By column from the Remodel tab
  3. Enter Reporting Date for the New column title
  4. Choose Max from the Operation dropdown
  5. Choose the Reporting Date from the Column dropdown
  6. Click on OK
Table.Group() Group By functionality in Power Query for Power BI
Group By performance in Energy Question

The next picture exhibits the outcomes:

The results of the Group By operation in Power Query for Power BI
The outcomes of the Group By operation

We now have all product numbers with their newest reporting dates. The one remaining piece of the puzzle is to affix the outcomes of the Grouped Rows step with the info of its earlier step. For that we use the Merge Queries performance which runs the Desk.NestedJoin() operate in Energy Question.

  1. Choose the Grouped Rows step from the Utilized Steps record from the Question Settings pane
  2. Click on the Merge Queries button from the Dwelling tab
  3. Choose the Product (Present) desk from the dropdown. Notice that we’re choosing the present question (Product)
  4. On the highest desk, press the Ctrl button in your keyboard and choose the ProductNumber and the Reporting Date columns sequentially
  5. Do the identical for the underside desk. Notice that the sequence of choosing the columns is essential
  6. Be sure that the Be part of Variety is Left Outer (all from first, matching from second)
  7. Click on OK
Left outer join (Merging) in Power Query for Power BI
Left outer be a part of (Merging) in Energy Question

As talked about earlier, the merge operation makes use of the Desk.NestedJoin() operate, which accepts two tables (highlighted in yellow within the expression under), an inventory of their key columns to make use of on the be a part of (highlighted in purple within the expression under), a reputation for the brand new column of kind desk and the be a part of form. Within the above operation, because the Grouped Rows is the final transformation step, we joined the outcomes of the Grouped Rows transformation step by itself. Right here is the code generated by Energy Question Editor after going by the step 21 to 27:

Desk.NestedJoin(#"Grouped Rows", {"ProductNumber", "Reporting Date"}, #"Grouped Rows", {"ProductNumber", "Reporting Date"}, "Grouped Rows", JoinKind.LeftOuter)

However that’s not what we would like, we don’t want to affix the outcomes of the Grouped Rows transformation step by itself. We have to be a part of the outcomes of the Grouped Rows transformation step by the outcomes of the Eliminated Columns step. Due to this fact, we now have to change the above expression as follows:

Desk.NestedJoin(#"Grouped Rows", {"ProductNumber", "Reporting Date"}, #"Eliminated Columns", {"ProductNumber", "Reporting Date"}, "Grouped Rows", JoinKind.LeftOuter)

The next picture exhibits the modification made within the expression and the outcomes:

The results of joining the results of the Grouped Rows transformation step by the results of the Removed Columns step in Power Query for Power BI
The outcomes of becoming a member of the outcomes of the Grouped Rows transformation step by the outcomes of the Eliminated Columns step
  1. Click on the Broaden button on the Grouped Rows column
  2. Deselect the ProductNumber and Reporting Date columns to maintain the opposite columns chosen
  3. Untick the Use authentic column title as prefix choice
  4. Click on OK
Expanding a structured column in Power Query for Power BI
Increasing a structured column in Energy Question

All performed! The next picture exhibits the ultimate outcomes:

The final results of implementing SCD1
The ultimate outcomes of implementing SCD1

We are able to now apply the adjustments to load the info into the info mannequin. With this method, when a brand new Excel file (a brand new Product Listing) lands in SharePoint On-line, Energy BI goes by the above transformation steps to make sure we all the time have the most recent Product knowledge loaded into the info mannequin, which is similar to the behaviour of an SCD1.

Have you ever used this technique earlier than? Do you’ve gotten a greater method to deal with the same state of affairs? You’ll be able to share your ideas within the feedback part under.

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments