Managing Data Warehouse Dimensions with MDS (part 1)
About This Video:
This video demonstrates the power of MDS and BI integration, empowered business users to manage their dimensions in a DWH / DataMart in a very agile manner, reducing the dependency on IT and lowering TCO.The managing data warehousing scenario has 2 parts. In this part 1, you will see how business users can manage a simple data warehouse hierarchy.
This video is available with closed captions. To view closed captions, click CC on the video control bar.
Hi, my name is Tyler Graham and I'm Senior Program Manager for the Master Data Services Team.
I'd like to talk to you about a scenario where you can use Master Data Services to empower your business users to update the Data Warehouse within your organization.
On the screen in front of you, you can see a PowerPivot report.
In this report, there are a couple of problems we can see.
One is that under Clothing, there are Mountain Bikes, which is skewing the total, and then there are a number of things at the bottom that are unaccounted for in the hierarchy.
In the past, when users wanted to deal with this, they would either fix it directly in their code on their computer on the data they'd pull from the data warehouse, or they would have to send an email to the IT team to actually update the data warehouse and to fix the problems in the data warehouse.
This is both time consuming and challenging as the people most responsible for the data and understanding it the best are not the ones that can update the data at the source.
By adding Master Data Services to the overall solution, we can streamline this process empowering the business users to make the change directly and giving IT oversight over these changes.
Let's see how we can integrate Master Data Services into a simple data warehouse scenario.
First, I'm going to go in and create a new model to store our information. In this case, I'm going to call it BI Sandbox.
Now, I'm going to go in and I'm going to open a new spreadsheet.
Now, in this spreadsheet, I'm going to use the Master Data Services Excel Add-in that ships with SQL Server 2012.
This is a free Add-in that will work with the SQL Server 2012 Master Data Services Hub and can be downloaded from the web.
In this system, let's connect to Master Data Services.
Now, this is the tab where we can see all of things we can do. We can create an entity from here.
One of the benefits that we get from an Excel Add-in is that Excel is a great place for IWs to get their data, whether it already exists in the lists and forms or whether they want to bring it from another source.
So I'm going to use the Data tab and I'm going to go get data from the SQL Server Database.
This is going to be my data warehouse.
Click on ProductCategory.
I've brought that data into the system.
Now, I'm just going to select the table of data that has been provided, go to my Master Data Services tab, and I'm going to create an entity.
I'm going to pick that BI Sandbox Model that we just created and call the new entity Category, and use the Key and pick the name.
Master Data Services is now taking that data and loading in a new entity, creating all the attributes, making the right type, bringing it into the system.
My data is now loaded into the server and is available on the server.
I'm going to go grab data from two other sources. I already went and grabbed Subcategory Data.
Do the same thing.
So this data's coming into the system and it's going to look at each of these attributes and make it the right type.
So you're going to have some numbers, we're going to have some strings and then this one is a number right now.
But, as you can see, this is the ProductCategoryKey so I want this to go back and integrate with the other systems.
So I go into Attribute Properties and I'm going to update this to Constrained List and I'm going to pick Category.
Very quickly, it's going to make this a constrained list of values that come from my Category entity.
So we've linked these two tables together and while it still looks like an Excel Spreadsheet, we're getting much more power if these two sheets are linked together and must pull from here.
Let's do the last one, which is Product. Bringing Products into the system.
All the data's now being loaded into the system, all of my entities, and I'm now in a couple, matter of minutes, created three entities.
Now, I'm going to link this subcategory entity and very quickly link these two together.
Alright, so we now have linked Product, Subcategory and Category, but I can create additional entities or linkings as well. So FinishedGoodsFlag, I can update this.
I can make a new entity by using a Constrained List, but this time I'm going to use the values in the selected column.
I'm just going to call this a True/False Pick List.
I can quickly iterate through all of these columns, making the right type or the right number of decimals.
I'll be managing this data effectively for my users.
So very quickly I can start providing immediate value to my business users for loading their data into the system.
But I also have the data off of their desktops, in some cases, and managed in a SQL Server environment.
So, let's go to the UI again.
With this data, I now have taken a Relationship, and let's look at that Relationship again.
Over here, you can see that I have Categories to Subcategories to Products.
We want to show this Relationship to our users, because this is how they see that Relationship.
While an IT professional's going to understand that Relationship in a list of tables and in Cube Relationships, a business user's going to have a harder time of visualizing that for themselves.
So let's go into the UI and create a new derived hierarchy based on that Relationship.
So, I'm going to call this category To Product. I'm going to start by pulling the Product over.
Pull the Product Subcategory, and as I do this, you're going to see it start to build my hierarchy in the Preview screen.
So I have Product Category to Subcategory to Product.
You can see now, if I drill in, I can see the same hierarchy that I was seeing over in my other screen.
This is going to be available to people within the WebUI.
So if I come in here, my business users can see this hierarchy within the UI and really be able to monitor and measure those changes right here.
So let's go in here and make the actual changes that we need to.
So the first one is, we want to take Mountain Bikes and make it a part of Bikes.
I can see now I have Road Bikes, Touring Bikes, and Mountain Bikes all showing in Bikes and I need to pass that data to the other system.
So in here, let's go to Integration Management and create a view, a couple of views, to bring data back.
So we have to integrate the two systems together. So first, let's pick Version. We'll talk about Version in another video.
For right now, we're just going to stay with Version 1, the initial Version.
I'm just going to create a Product Subscription View for the Product Entity and show the Attributes of Product. Just going to save that.
I'm going to create one more while I'm in here for Subcategory.
I would create these for each layer of changes I'm going to make.
Now, for our current demo, I'm only going to make those at Product and SubCategory, but you could make it for Category as well or any other information that we're storing in the system, such as color or other pieces. So let me save.
Now, what has happened is that behind the scenes, if I go into my database, I can go into my Views and I can see that I've created a Product and a Subcategory Subscription View.
If I look at these Views, they're just going to look like some Version information at the front.
It talks about the Version Number and Version Flag.
Then, it's going to be all the data that was passed into the system as well as some user define data as to who the last person to change things was, who entered it, the last change date time, and whether it's valid or not.
For right now, we're not going to worry about any of the validation or any of the other uses.
We're going to bring the data directly into our Data Warehouse and we'll open a Query that I've already written that should do this.
So I'm going to select these two Statements.
They're just very simple Update Statements that I can then execute.
These are going to update my AdventureWorks Data Warehouse with data from that View.
I can make these Update Statements so they run hourly or, in SQL jobs, run whatever time I want; hourly, every day, whatever I want, and they bring those things over very quickly into the system.
Now the two things are intrinsically linked. So let's go back in our report if we've actually fixed that issue.
We go to PowerPivot, look at the data itself, and we can click Subcategory and Refresh it.
Then, we'll Refresh this report and immediately, you can see that my Bikes now has Mountain Bikes in it.
But I not only affixed my report, I have actually fixed the source. So that's a big improvement over the process.
Now, let's try this other problem and for this, we're going to use the Excel Add-in again.
So if we go into the Excel Add-in, we can then filter to figure out which ones there are.
We can see them at the top, but if we wanted to, we can just use a filter.
Use some of the power that Excel gives us and just look for all the blanks that exist.
You can see here that it's two of them. We can expand this so we can see what they are and we'll say that one is a chain.
Then, one is a piece of the brake.
Now, I want to publish these changes into the system so I’m going to click Publish.
I can give each of these things their own annotation.
I pass this information back in. Both of them are true. Everything's been taken care of within the system.
Now, we want to go back here, run these two processes again.
Now we can go back to our report and hopefully, we've adjusted this problem.
So we'll go into the actual data. We'll click on Product this time.
We will Refresh that data and then go Refresh our report and we fixed the problem.
Now, everything is properly aligned. Everything is in the actual place it should be and my report looks like it ties perfectly.
I now see how important it is for each Product to have an associated Subcategory to it.
So, we want to provide additional validation so in the future, I can be notified and fix these problems before they come to a report so I can be proactive with my data as opposed to reactive once I see problems in the report.
So let's go into the MDS System.
We're going to go back to System Administration and we're going Manage Business Rules.
We're going to pick our Product entity and we're going to Add A Rule.
In this case, we're going to have certain fields that are required.
So Master Data Services, again, is trying to empower the business user to be able to build their own rules.
So we've given them this very, we've encapsulated stored procedures that will run against your data and validate against your data into a very common scenario of, if certain conditions are true, then certain actions occur.
In this case, we have no conditions, because we want to happen all the time and I'm just going to pick my Product Subcategory and say that Product Subcategory Is Required.
I save that rule. I come back and Publish this into my Rule Set and we can now see that it's active.
Now, if I wanted to send out email notifications whenever this was not active,
I can provide some notification information here. We'll talk deeper into Notifications in a later video.
So if I go into my Excel Sheet and I can now see that I've brought data back, we'll get them all.
You can now all these items I have, which, of course, none of them are a problem and I can Apply Rules.
If we look at the status, you can see the validation succeeded for all of them.
That lets us know what the status of these members are, so I'm going to Refresh my data to make sure I have the latest. In this case, we're going to add a new one.
I've now added a new Product into the system. I'm going to give it a Product Key.
I'm not going to provide it a Subcategory for right now, and we'll just pick a couple of other values just to give it some.
Then, I'm going to Publish this record back into the system.
It's going to go back through all of the system, and if I look down at my new bike, I can see now the validation has failed and it's going to tell me exactly what the issues are, Product Subcategory Is Required.
So I've not given enough information for it. So I have two ways I can do this.
I can go here and I can Sort or I can even Sort on this column. So in this case, I just want to see Validation Failed.
I see the one bike that I need to fix. I can select that it's a Mountain Bike and Publish.
Now, if I look at this row as I can do with any row, I can View Transactions and I can see when they added in the new bike.
I can see that they've added the Subcategory.
I can see any annotations that have been added for any of these different pieces; the new Super Bike was added, and everything is kind of coming together in my annotations and I have a history of what has happened with this bike over time.
I can add any other annotations I want.
But now, the Business Rules are also giving me feedback as to what needs to be filled in for my things.
So I hope in this initial demo we've shown you how you can leverage Master Data Services and their new Excel Add-in in SQL Server 2012 to manage your Data Warehouse Dimensions more effectively.
Presented by: Tyler Graham