Adatis BI Blogs

Working with Manual and Automated KPIs in a Tabular Cube

In a recent project, we had to produce a scorecard using a set of manual and calculated KPIs. To obtain the manual KPI figures, we used Master Data Services (MDS) where the users could insert the values, while for the calculated, we used the base measures created in the tabular cube. So far, this requirement does not look very complicated, however, what if I tell you that the same KPI can either be manually or automatically calculated by the cube? And that we have to present the values for different levels of a hierarchy? And that some of the KPIs are not absolute values but ratios? Now that I got your attention, let’s have a look at the solution we implemented.How to join manual and automated KPIs?Because the client couldn’t always provide the data to calculate the base measures, we delivered an MDS model to, among other functionalities, manually insert the numeric values. You can check this blog if you want to know more about the Survey model ( we were working with different markets, the same KPI could either be manually or automatically calculated, which means, the cube had to to select the appropriate scenario, depending on the selected market. In order to achieve such requirement, we created 3 measures.AutKPI – Using base measures from multiple tables, we defined the DAX code to calculate the KPIManKPI – Knowing all the MDS values were in one table, we defined a simple DAX query to sum the valuesActual – This measure was implemented with an IF statement. Eg.         Actual:=IF(ISBLANK([AutKPI]), [ManKPI], [AutKPI])How to aggregate ratio KPIs?Let’s have a look at the example below, where we are calculating the KPI for two levels of a geography hierarchy.Automated KPI 1 – Europe is naturally aggregating the values from Great Britain and IrelandAutomated KPI 2 - Considering we are using base measures, the cube can properly calculate the KPI at Europe level. Manual KPI 1 – All manual entries were aggregated with a SUM. Because those are absolute values, the figure for Europe is correctManual KPI 2 ­– Following the same logic as Manual KPI 1, we can see the Europe value is incorrect. Because this is a ratio we can't aggregate the value from the lower levels. The simplest approach to resolve this problem was to create a new calculation using an AVERAGE function, however, considering the requirements, we had to introduce a weighted average.Implementing weighted averagesThe first step to this approach is to define a weight for each market. Since the values can change according to the user’s needs, we added a new entity to the MDS model.Now let’s consider the example below showing the weighted approach.Following is the formula to calculate the KPI at Europe level. For a better understanding, I split it in different steps.C1 GB: Manual KPI x WeightC1 Ireland: Manual KPI x WeightC2: C1 GB + C1 IrelandC3: GB Weight + Ireland WeightEurope KPI: C2 / C3The scope of the project stated we had to implement the following logic:· When presenting the KPIs at market level, don’t apply the weighting· When presenting the KPIs at region level, apply the weighting but only for the ratio KPIsThe biggest challenge of this requirement was to overwrite the aggregating logic of the geography hierarchy. To achieve that, we implemented a dynamic segmentation pattern on the ratio KPIs (more details on this link This approach can be split in four steps.First step is the calculation of our numerator.Num Weighted AutKPI – Because the base measures from our automated KPIs are from different tables, we had to firstly group our data by market and region level and only then apply the calculation. Eg.Num Weighted AutKPI:=                  CALCULATE(                    SUMX(                       SUMMARIZE(                          'KPI Value',                           Market[Region],                           Market[Market]                           ),                        [AutKPI] * [KPI Weight]                    )                 ) Num Weighted ManKPI – On this instance, the grouping was not necessary because we only had one measure to consider. Eg.Num Weighted ManKPI:=                  CALCULATE(                    SUMX(                       'KPI Value',                        CALCULATE(                           SUM ( 'KPI Value'[KPIActual] ) * [KPI Weight]),                           'KPI'[KPI] = "Manual KPI"                           )                        )                    ) The second step is the calculation of our denominator.Den Weighted AutKPI – Once again, because the weights were stored in a single table no grouping was necessary.Den Weighted AutKPI:=                  CALCULATE(                    SUMX(                       'KPI Value',                        CALCULATE([KPI Weight])                        )                    ,'KPI'[KPI] = “Automated KPI"                 ) Den Weighted ManKPI – The same logic applies on this instance.Den Weighted ManKPI:=                  CALCULATE(                    SUMX(                       'KPI Value',                        CALCULATE([KPI Weight])                        )                    ,'KPI'[KPI] = “Manual KPI"                 ) The third step is the division of our measures.Weighted AutKPI:= DIVIDE([Num Weighted AutKPI], [Den Weighted AutKPI]) Weighted ManKPI:= DIVIDE([Num Weighted ManKPI], [Num Weighted ManKPI]) The fourth step is the calculation of our Weighted Actual measure, by once again, using an IF function.Weighted Actual:= IF(ISBLANK([Weighted AutKPI]), [Weighted ManKPI], [Weighted AutKPI])Finally, considering we only wanted to use the weighted measures for a subset of our KPIs, we created a new measure using a SWITCH function. Eg.Actuals:=       SWITCH(         VALUES(‘KPI’[KPI]),         “Percentage KPI”, [Weighted Actual],         "Percentage KPI2", [Weighted Actual],         “Absolute KPI”, [Actual],         "Absolute KPI2",[Actual]      ) Hopefully, I was able to clearly demonstrate our problem and how we managed to implement a solution to solve it. As always, if you have any questions or comments, do let me know.

Modelling Survey Style Data

In a recent project, one of the business areas which required modelling was that of survey data. This can often be quite tricky, due to the fact the data is not so quantitative in nature as other business areas such as sales. How do you go about calculating measures against this type of data? In this blog I will go about explaining a relatively simple model you can use to achieve this goal.   Master Data Services To aid with modelling I used Master Data Services (MDS) to help map the questions and responses from the surveys. The idea behind using MDS is that regardless of the questions asked, whether it be in one format or another, English or Spanish, you can map them to a set of master questions. Usually these will align closely to your measures/KPIs and should be relatively finite in number so that the mapping process is feasible for a business user. In my business case, the master set of questions revolved around items such as quality, price, and promotion of products. For responses, I needed to map these to a flag which indicated we wanted the response to contribute towards a measure and ultimately a KPI. I first created the following entities in MDS. Survey (holds the survey name, a unique identifier for the survey, and in my case I also created a master survey lookup to group the surveys together) Source Question  (holds the distinct set of questions assigned to each survey, along with identifying codes, and question orders - each question would also be mapped to a master question)  Source Response (holds a set of response options for each question, along with identifying codes) Master Question (holds the master set of questions and joins to the KPIs) KPI (holds a list of KPIs that you need to address by aggregating response data) Response of Interest (holds a list of responses that are regarded as positive / contributing towards the KPI when answered by the question) Response of Interest Mapping (allows the user to map the response options available on each question to a response of interest) In terms of the response of interest, I was only interested in responses where the answer from the survey was “Yes” so this was all that was required here. However for more complex response options, the model can provide the scalability required. For instance, if you were looking for an answer between 7-10 and the survey had been answered with a 7, 8, 9, or 10 – each of these could be mapped to 7-10 without having to create responses of interest for all particular combinations. This scales well and can cover scenarios for instance where the answer should be between 700 to 1000 in the same way. I also created a Master Question and Response of Interest value for N/A. This way, only the blanks on the mapping entities required populating and the user was never unsure whether a blank represented a question/response that was not of interest, or something that required mapping still. All the entities above apart from Master Question, KPI, and Response of Interest were populated automatically from ETL with a SQL script used to extract the contents of those entities from source. The other 3 entities were populated manually by a business user. I also locked the entities / columns that the user shouldn’t be changing by using read-only permissions. Some examples of the manually populated tables can be seen below:   Data Warehouse For modelling the tables in the data warehouse, I created a separate dimension for each of the Response, Question, Survey, and KPI entities, and a single Fact to capture the responses of interest against these dimensions. The majority of dimension lookups were straight forward along with the response of interest measure which can be seen below: SELECT SU.Code AS SurveyId, SR.Name AS ResponseName, 1 AS ResponseOfInterest FROM mdm.PL_ResponseOfInterestMapping RM INNER JOIN mdm.PL_SourceResponse SR ON RM.SourceResponse_Id = SR.Id AND RM.Survey_Id = SR.Survey_Id INNER JOIN mdm.PL_Survey SU ON RM.Survey_Id = SU.Id WHERE RM.ResponseOfInterest_Code IS NOT NULL AND RM.ResponseOfInterest_Name <> 'N/A' During our ETL runs for the fact we also checked for responses that had not been yet mapped – and did not pull these through. If you then have a cube sat on top of your DW, you can then write measures across the fact to count the number of responses of interest. An example of which can be seen here: Price Activation Standard:= COUNTROWS( FILTER( CALCULATETABLE('Outlet Survey','KPI'[Sales Driver] = "Price"), 'Outlet Survey'[IsResponseOfInterest] = 1 ) ) This was then checked against a Target fact table to calculate the compliance and the KPI was essentially an aggregation of the compliance across geography.   Summary Overall, the model has proved very popular with the business. It’s easy to understand and gives the business control over which responses to count towards the KPI – without having to hard code values into the ETL which had been seen in previous solutions. It can also be extended easily be adding new KPIs and mapping them to new master questions without having to change any ETL. From a development perspective it also means that nothing should go into the DW as Unknown for a Dimension since the SQL to populate MDS, can also be used for the DW and therefore should always match. If you have any questions, please feel to ask them in the comments.

Upgrading your BI Solution (2012 to 2014)

In a recent project I was asked to upgrade a BI solution from SQL Server 2012 to 2014.  The aim of which was to consolidate a number of database sources onto a single version of SQL Server. This blog looks at the steps required in order to achieve the upgrade process, along with issues I hit along the way.   Where do I start? In order to develop on SQL Server 2014, you’ll need to update your development tools! This involves downloading the following applications. Application Comments Link SSDT BI for VS 2013 Allows you to modify and deploy SSIS, SSAS, SSRS files. I would then suggest downloading Update 5 from within the application. This resolved some source control issues for me. SSDT Database Designer for VS 2013 Allows you to modify and deploy SQL related files. Team Explorer for VS 2013 Sometimes this is bundled in together with SSDT but not the case for 2013. BIDS Helper for 2014 This is a must have for any solution with a tabular cube. It allows the user more functionality to customise the cube and organise measures.   You’ll also need to update any custom components within the solution. For me, this was the 2014 Attunity drivers (3.0) to connect to our Oracle sources and some custom Adatis Framework components, both will need GACing. Remember that if you develop using 32 bit components you may need to download the equivalent 64 bit components once your solution is deployed to a server. Once you have the necessary tools locally, you can start the upgrade process. Open up the solution in your new environment.   The Solution Upgrade Process The first thing I noticed was that during the upgrade I lost all my SSIS components layout and comments when opening up the packages in VS 2013 that were developed in VS 2012. The code was still in the package file, but did not seem to do anything. I’m not sure this happens for everyone as the information I found to resolve the issue was not particularly well documented around the internet. Its possible that upgrading to Update 5 beforehand may resolve this issue.  To fix the the problem, I had to open up the packages code and search for the line that says version=“sql11”. This needs to be changed to “sql12”. The next time you open the package, everything should appear again. I also found that if you open up the package first in 2013, the application will automatically change this to sql12 however the comments/layout did not seem to appear. You also won’t be able to amend the file at this point so will need to roll back if using version control such as TFS. Another issue I ran into was that even after I ran this process, a few developers were unable to see the comments still. This is due to the fact they were running the dark themed Visual Studio which did not invert the text colour on the comments therefore making them appear hidden. Again, probably another bug with the base version of SSDT I downloaded off the Microsoft website before upgrading to Update 5. At this point you can start to run the packages through the Upgrade Package Wizard (if you right click on the SSIS packages folder in the project) .       If packages are not appearing in the wizard, then the application is not recognising the packages as coming from a previous version of SQL Server. Its possible that even after running through the wizard, it reports the packages as having failed the upgrade. It’s fairly safe to ignore this, and is usually the result of custom components or similar. Depending upon how many packages you need to upgrade you are now faced with the unfortunate task of replacing any of the custom components in those packages, with the new ones you downloaded to work with SQL Server 2014. In my case, this was the custom Adatis components in the Control Flow (Initiate Task/Terminate Task – remember to set LoadStatusType to Success on the Terminate properties), Data Flow (Clean – Data Cleansing/Standardise/Validate Columns), and Event Handlers (Row Throughput/Terminate Task on Error – remember to set LoadStatusType to Failure on the Terminate properties). In some cases, components before the upgrade display a different icon than afterwards so its easy to tell what version you have. When this is not the case, you’ll have to check the package code.      Following this, I then modified any connection strings I had within the solution to point to our new server. If you are not changing servers during your upgrade, just skip this step. The next step is to update the DB projects to 2014 via the properties of the project. Very simple. The final step needed within the solution is to upgrade the tabular model (if you have one). Firstly, make sure the model is open and then via the model properties, select the SQL Server 2014 compatibility level. Note – that if the model is not open, then this field will display blank.   Master Data ServicesAs part of my upgrade, I also had to upgrade our Master Data Services (MDS) to 2014. One of the pre-requirements for this is to have an IIS Web Server installed. If you’re provided a fresh clean server like I was then you’ll need to install this. This can be done through the Server Manager. Make sure the following features are added. Web Server Common HTTP Features Static Content Default Document Directory Browsing HTTP Errors DO NOT INSTALL WebDAV Publishing Application Development ASP.NET 3.5/4.5 .NET Extensibility 3.5/4.5 ISAPI Extensions ISAPI Filters Health and Diagnostics HTTP Logging Request Monitor Security Windows Authentication Request Filtering Performance Static Content Compression Management Tools IIS Management Console .NET Framework 3.0 Features WCF Activation HTTP Activation Non-HTTP Activation Windows Process Activation Service Process Model .NET Environment Configuration APIs The MDS configuration tool is quite good as it identifies if you are missing any pre-reqs. Once you have the pre-requisites for MDS installed as well as MDS 2014 itself, you can begin the upgrade process – which comes in 2 parts, the database, and the web site Unfortunately, model deployment packages can be used only in the edition of SQL Server they were created in. You cannot deploy model deployment packages created in SQL Server 2008 R2/SQL Server 2012 to SQL Server 2014. Therefore you’ll need to take a database-level backup of your MDS database and restore to the new 2014 instance. In my case, I also needed to give our service account that was running SQL Server security access to the folder in which the backup was stored as it wasn’t able to see it. Once you have successfully restored the previous MDS database to the new instance, you will need to load up the Master Data Services Configuration tool. To start with click on Database Configuration. If you then select the database you have just restored, it will suggest the database is not compatible with the current version of MDS and requires upgrading. Click on the Upgrade Database button and you should now have a compatible 2014 MDS database. I also had to re-set up the MDS mail profile at this point as this does not transfer across with the database. The next step is to configure a new MDS website which can be done via the Web Configuration button. Firstly, select the default website that is created when you install IIS. There is no need to create anything separate – in fact this caused me more hassle than it was worth. You can then go about creating the site which requires you to specify an alias, and the application pool information (name and service account information). You are then required to associate both the database and web site together. At this point, you should be able to load up the new MDS web site and explore your old model. One issue I ran into that stopped me loading into MDS at this point was that the service account you specify above will then need to be added to a local security group called “Log on as a batch job”. This can be done via the Computer Management application, then Local Users and Groups, Groups, Log on a batch job. I also had some minor issues when loading MDS where the page was malformed and I was unable to see the database. I resolved this by providing the service account access to the web config of the web site directly.   Deploying & Testing At this point, all the necessary requirements should be in place for you to deploy and test the upgrade. Deploy the solution as you would normally. Your first step once your at this point is to add a new SSISDB Catalog to your new instance.  If like me you did this via the old 2012 Management Studio, you’ll run into some errors potentially. Instead, load up the new 2014 Management Studio and you should be fine. Depending on how your solution is setup, you’ll need to re-confirm any environment variables or script them out again and then configure the SSISDB projects to use them. Then you will need to re-setup any SQL Agent Jobs you had on the previous instance, again this can be scripted across – just make sure you change the connection details. Following this, make sure you GAC any components required on the new server similar to what you did locally. Lastly, you will also need to add your SSAS Service Account into the new SSAS instance. You can then run the solution out as per usual and check your execution status reports for any irregularities.   Summary Hopefully this covers everything you need to upgrade your solution. Whether its from 2008/2012 to 2014 or 2014 to 2016, the steps should be roughly the same, you might just run into slightly different issues. Generally the process is fairly straightforward, the hardest part was keeping track of the number of steps required and then ticking them off as you do them. If you need to do this for both development and production servers, I’d advise running the steps side by side so they are set up in a similar fashion. Please let me know in the comments below if you’ve had any similar experiences / issues.

Master Data Services Many to Many Relationships

Many to Many Relationships (M2M) are possible in the current version of Master Data Services, but they come with a few workarounds. Thankfully, many to many hierarchies are one of the many new features that can be found in Master Data Services 2016. This post will contrast the M2M approach in the current version of MDS, before showing how its greatly improved in the current MDS 2016 CTP. Customer Example – MDS 2014 The simple example that I’m going to show is Customers and Bank Accounts. That is, a customer can have more than one bank account, but a bank account can also have more than one customer, e.g. a joint account or a business account. First, to show the MDS 2014 approach, there are a few entities to look at, namely Customer and Account: Then there is an other entity that relates to both Customer and Account, called CustomerAccount, which essentially acts as a bridge table, allowing the M2M relationship to naturally exist in the data: Moving away from the entities, I’ve created two derived hierarchies so that we can look at the data by account or by customer. So here we can see, for example, that Bob is keeping his own personal account, as well as the account with his wife…hmmm: Or looking at this another way, we can see the inverted version of the hierarchy, where we see the Customers as the top level, with some accounts belonging to more than one customer: As you may have spotted, there’s an issue with this approach. Whilst we do see the name of the Customer in the first hierarchy, its pointing at a member in the CustomerAccount entity, which is why the code is different each time. Enter SQL Server 2016… Master Data Services 2016 MDS 2016 does a far better job at visualising the M2M relationship. You still need the link entity, but it will allow you to show the actual Customers and Accounts with their correct codes. I’ve used the exact same entity setup as MDS 2014, so lets begin by looking at how we can build a hierarchy that shows customers by accounts. When creating a new Derived Hierarchy, we can see that the initial available levels list is just the same as it would be in MDS 2014: The first step is to drag over the entity called Customer to the Current levels section. Once we perform this action, the Available Entities pane looks very different in SQL 2016 that in would in the current version. Now MDS is allowing us to pick the Account entity, but via another entity. Hence it says “mapped via” on the left hand side: Once we finished the hierarchy build, and explore the hierarchy, we can see the same data as before, but this time its the actual correct customer codes that are displayed: The derived hierarchy by Customer also displays the correct customer and account codes: Summary A derived hierarchy in Master Data Services 2016 now allows the correct display of many to many relationships between two entities. This is one of many new features in MDS 2016, offering quite a number of improvements over the SQL Server 2014 version.

“Master” Master Data: Entity Sync in Master Data Services 2016

When I heard that “entities could be shared” between different models in Master Data Services 2016, my curiosity was piqued.  There is a nice video of this feature and hierarchy management here. The concept of sharing entities is called Entity Sync in Master Data Services 2016. You may ask the question as to why entities need to be shared between models? This is something I have thought about a lot with deciding how to split up the subject areas into different models in Master Data Services.  The conclusion I have come to is that is a better to keep subject areas separated rather than having one very large model in master Data Services with many entities.  Keeping the models separated makes it much easier to manage when working in teams, deployment, versioning etc.  However, there are some use cases where having a single entity with the same definition and data might be needed in different models.  For example… Say different departments have their own data stewards for master data in an organisation maintain their own master data in different models and one needs to secure those models such that different departments cannot see or modify each others data.  Then a department entity may be needed in each of those models Some master data may change very infrequently, but need to be used in multiple models e.g. currency, location, title.  In the ‘old days’ of Master Data Services 2014 and prior one would have to create replica entities and make sure that data is loaded into each of the entities in each of the models.  I have always thought it would be nice to have “Master” master data in Master Data Services.  This is where Entity Sync in Master Data Services 2016 comes in… Setting up a new Entity Sync Relationship Using the ChartOfAccounts, Customer and Product sample Master Data Services models I created a new entity called Disney Characters in the ChartOfAccounts model and want to sync this to the other two models i.e. Customer and Product. What is nice with Master Data Services 2016 is that one can add descriptions to the Models, Entities and Attributes, which is a nice feature to ‘document as you develop’. To set up Entity Sync, go to System Administration, and select ‘Entity Sync’ from the ‘Manage’ menu Create a new Entity Sync and select the Source Model, Entity and Version.  Then select the Target Model and Version.  You can even change the name of the target entity to something different.  I’m thinking ‘role playing’ entities.  You can set a schedule for when the entity gets synced, or ‘Sync On Demand’. So the below screenshot shows the setup of the Entity Sync for the Disney Characters entity from ChartOfAccounts model to the Customer model and renaming it along the way to Cartoon Characters. The entity will not sync from the Source Entity (ChartOfAccounts) to the Target Entity (Customer) until the sync is run.  You can’t seem to select more than one Entity Sync to sync at a time through the Master Data Services web UI. On going to the Customer model and viewing the synced Cartoon Characters entity, all the metadata (including all those descriptions) and data appears.  However it is not possible to add or modify members.  And we cannot add any new attributes to the synced entity.  This is what we want, right?  “Master” master data with a single source in the source entity and model. In the management of entities it is also indicated that this entity is a ‘Sync Target’. Nice. Now that the entity is synced I can use it like I would any other entity in the Customer model.  I can add domain based dependencies and use it in other entities in the model, and I can apply security onto it.  I can make changes to the source entity and re-sync and the new metadata and data will be propagated to the target entities. Deleting Entity Sync Relationship But what if you delete the entity sync relationship? Will the target table get deleted too?  So trying this I removed the Entity Sync for the Disney Characters entity from ChartOfAccounts model to Cartoon Characters entity in the Customer model. Nope. The entity is still there, but now you can edit it (I’ve added ‘Road Runner’), and carry on modelling it like you would any other entity in the model.  Although I did note that it lost it’s setting of ‘Create Code Values Automatically’.  Not sure if this is a feature? I can turn the entity sync relationship back on but specify ‘Existing Entity’.  But this threw an error, it seemed to have a problem with the new member ‘Road Runner’ that I had created.  I deleted ‘Road Runner’ and set up the Entity Sync relationship no problem.  I guess one could also drop and recreate the entity using the Entity Sync. So what happens if I try to synchronise and entity that has dependencies on other domain entities e.g. Trying this out with the SubClass entity from the ChartOfAccounts model gives the following error “Domain based attribute(s) in the source entity will not be synced”. This is what the SubClass source entity looks like with a domain based attribute to the Class entity in the ChartOfAccounts model. This is now what the SubClass target entity looks like in the Customer model.  It has lost all of it’s domain attributes! So I thought I would try to first synchronise the Class domain entity first, then synchronise the SubClass entity. Same “Domain based attribute(s) in the source entity will not be synced” as before and the domain based attributes of SubClass will not be synched. Deployment Considerations With the current SQL Server 2016 CTP 3.2 it does not appear that Entity Sync relationships will be packaged up with creation of packages either from the Master Data Services web UI or using the MDSModelDeploy command line utility.  Although I guess they might not be? As one would have to consider the order of deployment of models with the dependencies that the Entity Sync relationships create.  They are very easy to set up, but then again there is a new meta data table in the Master data Services database called [mdm].[tblSyncRelationship]. There are also corresponding stored procedures that operate on this table. So I guess one could populate the required metadata in [mdm].[tblSyncRelationship] and regenerate the Entity Sync relationships as part of a deployment process. In Conclusion The introduction of the Entity Sync functionality in Master Data Services for SQL Server 2016 is a welcome new feature and opens up all sorts of possibilities with creating “Master” master data.  The new Master Data Services web UI for managing these is great.  So long as the entities you want to sync to other models do not contain any domain based entities. I will definitely be using it, as: It will definitely speed up development – goodbye to all those SSIS packages to load the same data into the same entity in different models! Add value to the end user as everything will be in perfect sync

Generating Master Data Services Subscription Views

Creating subscription views through the Master Data Services web UI is easy. If not a little slow at times if the Master Data Services installation is on a busy or slow server.  Generally one does not have that many models and entities for which subscription views need to be created.  But what if you do?  I am working for a client where they are planning a large Master Data Services implementation with possibly hundreds of entities. Reverse engineering the entities from a SQL Server relational engine to Master Data Services can be done using the MDSModelImport utility on Codeplex.  Theoretically this utility could be extended to create subscription views too.  However there is an easier way that could be used in a couple of other scenarios e.g. Regenerating subscription views when you have updated entities after the subscription view on the entity has been created Some of my colleagues reported that subscription views had ‘disappeared’ when deploying models to a new Master Data Services server. A bug? Regenerating subscription views for a different version of the model So I started doing some digging about in the Master Data Services database and came across a nicely named stored procedure called [mdm].[udpCreateSubscriptionViews]  The content of this stored procedure is very useful and has a wealth of information on what the parameters are.  I put in the required parameters for a leaf level subscription view on one of my entities.  It created the view, but the view did not show up in the Master Data Services web UI. In addition on querying the view no data was returned, even though it had created all the expected attribute columns. Hmm? So, using the above stored procedure as a starting point, and trawling through a number of stored procedures I came upon: the table [mdm].[tblSubscriptionView]. Of course! and the stored procedure [mdm].[udpCreateAllViews] Using information that I gleaned from above mentioned digging, I inserted the required metadata into the table [mdm].[tblSubscriptionView], and then ran the stored procedure [mdm].[udpCreateAllViews]. Voilà!  Using the sample ‘Customer’ model provided with Master Data Services, I wanted to create leaf format subscription views for all thirteen entities.  So using the below code to populate the metadata to the [mdm].[tblSubscriptionView] table to generated Leaf format views.  Also be sure to create a subscription view name that is unique.  I like ModelName_EntityName to keep all the views from a model together.  Generating other kinds of subscription views would use the same method, the metadata tags would have to change.  See contents of the stored procedure [mdm].[udpCreateSubscriptionViews]. In SQL Server 2012 or SQL Server 2014 In SQL Server 2016 (a few minor changes) Then in execute the stored procedure [mdm].[udpCreateAllViews] to update all the views.  This stored procedure will use whatever data is in the table [mdm].[tblSubscriptionView] to create any missing subscription view and to update any that might require updating.  So remember this is a very useful stored procedure in the Master Data Services database. This is what the data looks like in the [mdm].[tblSubscriptionView] table The subscription views appear in the Master Data Services web UI In SQL Server 2014 In SQL Server 2016 And data come back from all the views.  In Summary… So whether this is for ten views or hundreds the process would be the same and just as quick!  It is worth remembering the stored procedure [mdm].[udpCreateAllViews] as it might be best practice to run this as part of the Master Data Services model deployment process to make sure that all subscription views are up to date. Also if any have ‘disappeared’ but the relevant metadata exists in the table [mdm].[tblSubscriptionView] then running this stored procedure will regenerate them. Next on the agenda is to start digging through these views…the possibilities are endless.

Master Data Services Regular Expression Business Rules

As those of you who use MDS will know, the Code attribute within a Master Data Services entity is mandatory, as its used to uniquely identify the members within the entity. Not only is it mandatory, but unfortunately you cannot change its underlying SQL Server data type – it always gets created as an nvarchar. This is useful in some cases, but what do you do if you want to ensure that the Code can only be numeric? This post outlines two options that may help: Master Data Services Security Although it will vary across different MDS implementations, the Code is quite often used as as an auto incrementing integer, which can be achieved easily by changing the entity properties. If all you want is to auto generate the code and prevent users from changing it, then you can just set the Code to be read only, as shown below: Business Rules The security approach will work fine if users aren’t allowed to update the Code. On the other hand, if the Code can be updated, then a different solution is needed. Here an MDS business Validation Business Rule type can help, as one of the Validation rules is called must contain the pattern: By dragging this business rule onto the Action node, its now possible to write the rule action. If we chose the Code as the attribute to validate and then enter ^[0-9]+$ as the regex pattern, then the rule looks as follows: Finally, after publishing the new Business Rule, members with an non-numeric code will now be invalid, as shown with the sample MDS Customer model:

MDS Derived Hierarchy Permissions

The MDS Hierarchy Member permissions have been given a complete revamp in SQL Server 2012. As explained here, the hierarchy member permissions are purely optional and allow you to limit the access to a specific set of members. Even though the actual member permissions are optional, you may have a need to purely allow a user to browse and/or update the Derived Hierarchy. This is also something that’s been affected by the aforementioned change, so I want to explore here how we would grant users access to a Derived Hierarchy. In 2008 R2, on the model tab, you got a Derived Hierarchies node, as shown in the image below: If we contrast that to SQL Server 2012 (albeit with a different model), we don’t have a Derived Hierarchies node: Therefore, to access the actual hierarchy, according to this article, you need to give the user Update permission on the model. That’s not the only way to do it, but certainly working only at the entity level, or the member level, is not good enough. To explore, lets look at an example. I have the sample Customer model installed and I want to give a test user access to the following CustomerType hierarchy, which is just made up of the CustomerType and Customer entities: Going to the User and Group Permissions, just quick confirmation that assigning Update permission to the Customer and CustomerType entities is not good enough. As shown below, the user does not have access to any of the Derived Hierarchies that I wanted: Upping the permissions level a bit by granting read only permission to the model does the trick – the test user can now see the Derived Hierarchy and can also add members. This is thanks to 1) a Read Only permission at the Model level and 2) Update permission on the two entities that make up the Derived Hierarchy: However, the simplified security comes with a slight drawback, which is to get access to the Derived Hierarchy we’ve now given the user Read Only access to all entities in the model: You may not want to grant a user access to all entities, but thankfully it can be avoided by granting an explicit Deny at the entity level. For example, as shown below I’ve set an explicit Deny for the AddressType entity: This will ensure that the user now doesn’t see the AddressType entity on the Entities menu. Now that the user can see the hierarchy, what about restricting access to the members? That’s a bigger topic, so I don’t want to cover it here, but I will just provide a useful tip. When you’re testing your security, don’t forget that member permissions are not applied immediately! The following article explains how to immediately apply the member permissions, which is something that I’d recommend carrying out whilst testing.ellspacing="0" cellpadding="2" width="925" border="0"> Packt have just released a second version of their ‘MDX Cookbook’, titled ‘MDX With Microsoft SQL Server 2012 Analysis Services’, authored by Sherry Li and Tomislav Piasevoli. As I’m one of the technical reviewers of this book, it’s put me in a good position to be able to put together a review of the book in this blog post. The new SQL Server 2012 version of the book is similar in terms of content and structure to the original, presenting a number of solutions to common MDX problems in a ‘recipe’ format. In the first two chapters, the book covers basic MDX techniques, such as the layout of a basic query and a variety of techniques for working with sets. The pace of the book warms up nicely here, starting with a few basic concepts, before gradually getting more complex. After a useful chapter on working with time in MDX, there are some excellent chapters that give what should be useful recipes on common reporting techniques, such as top/bottom members, ranking and averages. As the book progresses, there are some more complex chapters that cover areas such as linear regression, complex sorts and recursion – to name a few. Whether the recipes are simple or complex, they works well, as each recipe is packed with step-by-step instructions and screenshots, all based on the SQL Server 2012 Adventure Works cube. Although it’s also in the first edition, I particularly like the chapter ‘When MDX is Not Enough’, which talks about how modifying the cube design can assist you when writing some MDX queries. Essentially the chapter promotes evaluating if you can modify the cube design in some way, rather than writing very complex MDX, which is something that I completely agree with. The book differs slightly to the 2008 R2 version in that a few more of the basics (e.g. basic MDX query structure) are covered in the introductory chapters. This seems to work well, meaning someone relatively new to MDX can pick up some simple concepts, whereas someone who is more experienced in MDX will also get a massive amount out of the book. So overall this comes highly recommend for your bookshelf or Kindle…

Debug Custom MDS Workflows

The custom MDS workflows, AKA External Action Business Rules or custom MDS assemblies, are one of the more advanced features of MDS. They allow you to extend the standard MDS business rules by getting MDS to fire Entity Member member data at an external assembly of your choice. There are a lot of moving parts to this feature, MDS business rules, the custom .Net assembly, service broker and a windows service! When a business rule meets your condition, then the rule is written to a service broker queue. The windows service interrogates the queue and then passes the information to the custom assembly. I’m not going to show how to do this, this is already done by Johan Machielse and also on MSDN. What I would like to show is how to debug the use of the custom business rules, just due to the number of moving parts and also a couple of quirks that I’ve encountered when using them. Custom Assembly Deployment One of the first things that may catch you out is the deployment of the assembly. To troubleshoot this, I recommend the           “-console” argument of the workflow service. If you run it in command line mode you may encounter a few errors: “Could not load file or assembly 'YourAssembly' or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.” This means that you need to go the properties of your assembly in Visual Studio and change the Target Framework from .NET Framework 4 to 3.5. Another error that you may get is "ERROR: Could not create type. Ignored.”  Assuming that you have actually put the assembly in the bin directory, this is most likely to be that the either the namespace or the class name in your DLL doesn’t match what you have in your config file. Check the contents of Microsoft.MasterDataServices.Workflow.exe.config match your DLL. You may also get, “ERROR: Type does not implement IWorkflowTypeExtender.” This means that your assembly has been picked up correctly, but it does not implement IWorkflowTypeExtender. If the assembly does start up ok without errors, you may still get errors when the exe tries to take a message off the service broker queue. An error such as “Could not dispatch due to missing extender <tag>” means that the tag that you’ve given to the assembly in the config file does not match the “Workflow Type” value of the MDS business rule: <setting name="WorkflowTypeExtenders" serializeAs="String"> <value>MDSTEST=MDS.CustomWorkflow, CustomWorkflow</value> </setting> Debugging the Custom Assembly Once you do get the assembly working, you may find that the code isn’t quite doing what you want. A good way to troubleshoot this is to choose “Attach To Process” from the Debug menu in Visual Studio, as shown below: If you do this, then you can add breakpoints to your .Net code as normal and step through in order to troubleshoot issues. To conclude, this probably isn’t an exhaustive list of all the sort of errors that could occur with custom workflows, but with any luck it will save someone a few hours of troubleshooting.

Unique Values in Master Data Services

I’ve been doing quite a few demos and Proof of Concept (POC) projects recently for MDS. Understandably there are always a raft of questions that come up during demos and POCs, one of which recently was “how do I ensure that this attribute’s value is unique?”. MDS can easily deal with simple uniqueness enforcement, which is something that anyone who has done any MDS development will no doubt know straight away. But if the requirement is more complex, such as ensuring that the attribute is unique in conjunction with other attributes, then I don’t think it’s as well known. Therefore I thought it would be worth a quick post on how to do this. Code Attribute To get the simple case out of the way, each MDS entity is always created with an attribute called Code, which must be unique. Therefore if you try and create/update a member with a duplicate Code, then you get the following error message: Business Rules To enforce uniqueness on any other attribute, you have to use business rules. Therefore, if I want to ensure that the attribute called Name must be unique, then I must carry out the following: Go to the business rules editor and create a new rule Drag the action of “must be unique” over to the actions node Drag the Name attribute over to the “Select attribute” node, meaning that the action should look as follows: Once that’s done, save the rule step and then re-publish the rule. Trying to enter a duplicate value for the Name attribute will now give the following validation error: Business Rules – Multiple Attributes Now for the harder scenario. In the sample MDS product model, there is a Country attribute and also a Product Sub Category. I want to ensure that Name is unique, but only within a Country and Sub Category combination. To do this we go to the same business rules interface, and add the same action as before. Once that is done we carry out the following additional steps: Change the radio button to “In combination with the following” This is the part that I don’t think is that obvious. Drag over the Country attribute and then the Product Sub Category attribute onto the “attributes” node. This should give the following: As before, save the rule step, then publish the rule. Now a duplicate name will be permitted, but only within a country and sub category combination. If we do enter a duplicate value within a given Country/Product Sub Category group, then the following validation message is shown:

Master Data Services SQL Server 2012 Vs 2008 R2

I’ve given quite a few MDS demos recently and one question I seem to get asked often is “What features does MDS 2012 have compared to MDS 2008R2?”. As its happened quite a few times, I thought it would be a good idea to put a quick post together to explain the differences. The table below details all the features MDS has and compares the two versions: Feature MDS 2012 MDS 2008 R2 Front End ·         New Silverlight UI added for the Explorer area. Better performance, with less clicks to get tasks done. ·         Excel Add-in introduced ·         HTML based ASP.NET implementation. ·         No Excel Add-in Modelling ·         Carry out in the web UI, plus some tasks can be carried out in Excel ·         Allows the auto generation of a code without using business rules ·         Carry out modelling in the web UI only Hierarchies ·         Three types of hierarchies ·         Manage hierarchies in the new Silverlight UI ·         Three types of hierarchies ·         Manage hierarchies in the older UI style Collections ·         Add members from Explicit Hierarchies or other Collections ·         Maintain a weighting on a member by member basis ·         Add members from Explicit Hierarchies or other Collections Staging Process ·         A staging table is created per entity that you create in MDS. The table mirrors the exact structure that you see when you view the entity in the front end, making loading easier. ·         A generic staging table is used for all entities. This means, for example, you must load attribute changes in attribute name/value pairs Deployment ·         Deploy the model structure from the front end. ·         Deploy the data from the new command line deployment tool ·         Deploy model structure and data from the front end Security ·         Grant permissions to models, entities, attributes and attribute groups ·         Security cleaned up and simplified (no longer necessary to grant permissions to hierarchies) ·         Grant permissions to models, entities, attributes, attribute groups and hierarchies Business Rules ·         Alter attribute values, validate data integrity, initiate workflow. ·         Some minor validation changes around using business rules to auto-generate Code values ·         Alter attribute values, validate data integrity, initiate workflow.   SharePoint Integration ·         Initiate a SharePoint workflow from MDS business rules ·         New functionality to embed the web UI inside a SharePoint page ·         Initiate a SharePoint workflow from MDS business rules   Data Quality Services (DQS) Integration ·         Connect to a DQS matching policy from Excel, against MDS master data ·         No DQS capability, as DQS is new in SQL Server 2012 Version Management ·         Create multiple copies of your master data and work with them independently as needed ·         Some minor UI improvements have been added ·         As SQL 2012, create multiple copies of your master data and work with them independently as needed Transactions (auditing) ·         Transactions removed from Explorer and now are only available in Version Management ·         Therefore users cannot reverse their own transactions, only administrators can do this ·         Annotations are now permanent and cannot be deleted ·         Transactions viewable in two areas, Explorer and Version Management ·         Users can reverse their own transactions ·         Annotations can be deleted Metadata ·         No support to view Metadata – this is now a deprecated feature ·         Customise the metadata for most objects in MDS ·         View metadata in the metadata explorer Installation & Configuration ·         Installation from the main SQL Server setup program ·         Minor changes to the configuration settings ·         Installation from the SQL Server media, but not included in the main setup program   For me the improvements to the performance and the addition of the Excel Add-in are the most compelling reasons to upgrade. The great thing in particular about the Excel Add-in is that data stewards can manage data with the familiarity of Excel, but the data itself will be stored in SQL Server 2012.

Matching with Master Data Services, DQS and SSIS

If you have an MDM requirement to consolidate multiple sources of Master Data together into a single golden record, then you have a few different ways to achieve this on the Microsoft platform. This blog post gives an overview of the different ways that various matching methods can be used in conjunction with Master Data Services for consolidation in Master Data Management, outlining the pros and cons of each option. In summary, the options are: Data Quality Services (DQS) SQL Server Integration Services (SSIS) Plus Master Data Services itself has a few (not that well known) matching capabilities Data Quality Services A key requirement in all but the simplest MDM solutions is that the matching/consolidation must be carried out in an automated manner, with a Data Steward alerted to take action if needed (e.g. the matching engine incorrectly matches two customers, the Data Steward opens MDS and corrects this). This scenario would be hard to achieve with DQS, as it’s possible to automate the DQS cleaning, but it’s not possible to automate the DQS matching. This is something that I’ve raised connect issues about, here and here. If your data to be matched into MDS is not coming in on a daily basis, and you therefore need to do more ad-hoc matching to produce your golden Master Data records, then DQS could be for you. The MDS Excel Add-in will give you the capability of matching data that you import into Excel with MDS members, harnessing the power of DQS. An overview of how this works is available here. Integration Services SSIS has been around for a long time now and, as many of you will know, contains fuzzy matching components. With the right MDS model design, its possible to carry out a batch based fuzzy match between your master records and end up with a mapping between your the records that exist in your source systems and your MDS golden records. The rough conceptual steps to do this are: Load the new and changed records from the source systems into a staging area. Clean and standardise your data. This is actually something that DQS cleaning can help with. Query your staging area to get the new records that you want to insert/update into Master Data Services. Now the question arises, do we have an exact or close match for these records already in MDS? While the exact matches are easy to deal with, use the SSIS Fuzzy Lookup component to establish whether there are any approximate matches. Link the source records to master records (if the match is high enough) using MDS Domain Attributes. Carry out appropriate inserts and updates into MDS using the MDS staging tables. Ensure that a Data Steward is alerted in some way if necessary (e.g. if the match threshold is below x% confidence). This can be done with Email or MDS Notifications, for example. This process can run in batch overnight, with the Data Steward approving or rejecting the matches that SSIS has carried out the following morning. Whilst the above over-simplifies the process and technical work required, hopefully the process makes sense at a high level. Master Data Services Although you cannot feed MDS your source data and get it to automatically carry out matching for you, it does actually contain the raw components in order to do this. By this I mean the MDS database contains an assembly called Microsoft.MasterDataServices.DataQuality, which gives you a number of fuzzy matching T-SQL functions. These are called from the MDS front end when you carry out some filtering when viewing entities. Using them just for filtering in the front end really isn’t using the functions to their true capability, but thankfully you can use these functions in your own code. You can use the MDS T-SQL functions in a similar way to the conceptual SSIS method outlined above, in order to match and eliminate duplicates. In addition, the MDS web API can also be used to carry out a fuzzy match, as mentioned in this forum post. Retrieving match candidates using a web service may be an attractive option if you’re trying to do real time MDM. Conclusion Essentially until it’s possible to automate DQS matching, we have a choice between SSIS and the MDS matching functions. The following e-book gives a very detailed overview of the matches that both are capable of doing. The MDS T-SQL functions are more flexible than the SSIS fuzzy components as you can choose what fuzzy algorithm you want to use, but the SSIS components let you choose between Fuzzy Grouping and Fuzzy Lookup out of the box, without having to write SQL. Although I tend find that both give very good matching results, the MDS T-SQL functions produce slightly better matches in my experience, plus give you the option of trying different algorithms to suit different scenarios. It’s also worth mentioning that Profisee Maestro (full disclosure, we are a partner) integrates with MDS, offering its own matching algorithms. Maestro also has a front end with the ability to assist with survivorship and approval, which I think is a useful addition to MDS. Speaking of survivorship and approval, there are two options in MDS out-of-the box. The new Master Data Manager web front-end is much improved, but potentially the MDS Excel Add-In allows a bit more flexibility for survivorship carried out by a Data Steward, just due to its natural ability for filling/copying/pasting/filtering. So overall, due to the various improvements, Master Data Services is now capable of tackling more complex MDM scenarios than in the 2008 R2 version.

Master Data Services in SQL Server 2012 RC0

There’s been a whole host of changes to MDS in the SQL Server 2012 RC (Release Candidate) 0 that came out the other week. This blog post gives an overview of the changes, before diving into detail on a few of them. At a high level, the following changes have been made to MDS: Improved Master Data Manager front screen UI and navigation paths Collections interface updated to Silverlight Improved Excel user interface & functionality Auto generation of entity code values, without using business rules New deployment tool SharePoint integration Let’s take a look at each of these changes: Improved Master Data Manager UI Although the UI improvement (shown below) is good, the best thing about the Master Data Manager changes, in my opinion, is that clicking on the Explorer feature no longer takes you into the Model View, but instead takes you straight into your master data for your core entity (e.g. in a Customer model this entity would be Customer). I’m not sure if this would get a bit frustrating if you didn’t want the core entity, but then again everything seems very quick in RC0, so I don’t think it’s really going to matter. There’s also a new button next to a domain-based attribute that apparently has been designed to help with Many-to-Many relationships. I can definitely see that working, but it’s useful to have anyway to jump to the member details for the domain based attribute that you are viewing, many-to-many or not: Collections Interface Updated to Silverlight The collections interface is now much slicker, getting the Silverlight makeover seen in other areas of Master Data Manager. Switching between collections, for example, which could take a while in R2, now happens very quickly, making collections far more useable. The screen shot below shows how you edit collection members by picking members from one of the entities and adding those over into the collection by clicking the Add button: Collections now have the concept of a Weight, meaning that you can alter the weighting value associated with the collection members, which could be useful for reporting purposes. The idea is that you extract the collection members and the weight values in a subscription view. A Weight column is actually included in the 2008 R2 collection subscription views, but there was no front end to modify the weight value, which has of course now changed: Improved Excel User Interface and Functionality The MDS Excel Ribbon has been given a makeover, meaning that you now see the following in the ribbon: It’s interesting to see that the Favourites section has been replaced with the concept of ‘queries’. The idea is that you can send a query file via email to another user, providing that user has the Excel add-in. When the user double clicks on the query file (extension *.mdsqx), Excel will open and make a connection to MDS, using the connection and filter information provided in the file. This will result in Excel opening, with the user prompted if they would like to connect to MDS: It’s also good to see that a few of the domain-based attribute issues have been addressed, namely the display of domain-based attribute names when you filter an entity and also the display of the names and the codes together in the Excel sheet. Here’s a screen shot of the how the attribute names are now visible when filtering the Country domain-based attribute that exists in the Customer model: Auto Generation of Entity Code Values If you wanted the code to be auto-generated in 2008 R2, you had to use a business rule. You can still do that if you want, but the create entity admin screen has changed slightly to include an option to auto generate the code. This works slightly better than business rules in my opinion, at least as far as the Excel add-in is concerned, as the code is returned to the user immediately after publishing, whereas the business rules require you to do a refresh in Excel and of course need more development! Here’s a screenshot of the add entity screen: New Deployment Tool Deployment has been altered in SQL 2012, with the addition of a new deployment tool, plus the fact that subscription views now get deployed. It seems that the current R2 deployment method (in the Administration section of Master Data Manager) is still included but now will not deploy data. To do that you need to use MDSModelDeploy.exe, as explained here. As an example, here’s how you can deploy the sample Customer model using MDSModelDeploy.exe, for default installations: MDSModelDeploy deploynew –package “C:\Program Files\Microsoft SQL Server\110\Master Data Services\Samples\customer_en.pkg” –model “Customer” –service “MDS1” SharePoint Integration and Further Details Master Data Manager now supports a set of parameters that allow the MDS UI to be displayed without the header, menu bar and padding area. This means that MDS can now be incorporated into SharePoint or other websites. For the details on this, as well as more details on the above points, take a look at the following Technet article.

Managing SSAS Named Sets with Master Data Services Collections

Master Data Services Collections are probably not the first feature that come to mind when you think of the MDS product. The hierarchies and member grids tend to be the core functionality, but as this post will hopefully show, MDS Collections are useful also. Collections are essentially managed lists of members that can come from multiple different MDS explicit hierarchies, or also from another collection. The idea is that this “subset of members” can be maintained in MDS by a business user and then fed to external applications for reporting or other purposes. Analysis Services Named Sets One example of how collections can be used is to maintain Analysis Services named sets. Some named sets, such as a Top 50 Customers, don't require any maintenance, as it’s just the top 50 of all customers, based on a particular measure. On the other hand, sometimes named sets can be static lists, e.g. a list of core products that the user wants to see for reporting. In the latter example, if a user wants the definition of a named set to change, they have to get IT to change the MDX script. MDS collections can help by allowing the user to control the named set definition, reducing the burden on IT. Example Scenario  Here’s an example of how this is done. First of all, the end game is that I have a named set in Analysis Services that is currently just for 3 products. Therefore, a user can easily drop this set into an Excel report to get quick access to the products that are important to them: So the challenge is that we need to find some way of extending this named set without doing it manually. This is where MDS starts to come in, so using the sample Product model that comes with MDS, I’ve created a new collection called Favourite Products, as shown in the image below: If I go and edit the Favourite Products collection in MDS, then I can drag and drop more products into this collection, or remove some existing members. In this case, I’ve chosen to add two new products: C#, AMO and the MDS API So the named set represents the target that we need to get to, whereas the MDS collection that’s shown is the source. To get the contents of the MDS collection to update the named set, one way of doing it is to use the MDS API to pick up the collection members, and then to use AMO in order to write the named set into the MDX script. I’m just doing this in a C# windows application, but you could do it via a batch process, such as SSIS. For this post I’m just going to show the code, so here goes: This post is already starting to feel too long so I’m not going to show the basics of the MDS API. For that, take a look at a good posting by the MDS Team blog here. Also, as anyone who knows me will no doubt agree, I don’t tend to get involved in doing C#, so don’t consider this to be production ready! It should give you an idea of the basics though. Anyway, assuming that we’re now connected to the MDS Web Service, I’ve created the following method that will return the members from the collection: private HierarchyMembers ObtainHierarchyMembers(string entityId, string hierarchyId, string modelId, string versionId) { HierarchyMembersGetRequest request = new HierarchyMembersGetRequest(); HierarchyMembersGetResponse response = new HierarchyMembersGetResponse(); request.HierarchyMembersGetCriteria = new HierarchyMembersGetCriteria(); //State that our hierarhcy type is a collection and that we want collection members request.HierarchyMembersGetCriteria.HierarchyType = HierarchyType.Collection; //Pass in the key search criteria to identify the correct collection in MDS request.HierarchyMembersGetCriteria.ParentEntityId = new Identifier { Name = entityId }; request.HierarchyMembersGetCriteria.HierarchyId = new Identifier { Name = hierarchyId }; request.HierarchyMembersGetCriteria.ModelId = new Identifier { Name = modelId }; request.HierarchyMembersGetCriteria.VersionId = new Identifier { Name = versionId }; request.HierarchyMembersGetCriteria.RowLimit = 50; request.International = new International(); OperationResult result = new OperationResult(); //Return the hierarchy members from the service return service.HierarchyMembersGet(request.International, request.HierarchyMembersGetCriteria, out result); } Before we use the above method, we need to connect to SSAS using AMO. That can be done quite easily with the following code: Server server = new Server(); string connection = "Data Source=.;Catalog=Adventure Works DW 2008R2;"; server.Connect(connection); After we’ve done all the usual error handling associated with connecting to a database, we need to pick up the SSAS database and cube: Database database = server.Databases["Adventure Works DW 2008R2"]; Cube cube = database.Cubes["Adventure Works"]; Now we’re ready to call our ObtainHierarchyMembers method, before looping through it to build the named set: StringBuilder mdx = new StringBuilder("\n//Auto generated named set at " + DateTime.Now.ToString() + "\nCREATE SET CurrentCube.[Favourite Products] AS {"); int count = 1; //Loop through the collection to build the mdx foreach (ParentChild pc in hm.Members) { //Add the members to the MDX string //This references the member by name //It would be possible to reference by member key, but would require more work mdx.Append("[Product].[Product].[" + pc.Child.Name + "]"); if (count < hm.Members.Count()) { mdx.Append(", "); } count++; } mdx.Append("};"); Now we need to insert the named set in the correct place within the existing MDX script, bearing in mind it could already exist: string currentScript = cube.MdxScripts[0].Commands[0].Text; //Find the correct place to insert the named set within the MDX script: int start = currentScript.IndexOf("\n//Auto generated named set at"); int end = 0; StringBuilder newScript = new StringBuilder(currentScript); if (start != -1) { end = currentScript.IndexOf(";", start); //If the named set already exists, remove it newScript.Remove(start, end - start + 1); } else { start = currentScript.Length; } //Insert the named set in the correct place newScript.Insert(start, mdx.ToString()); //Update the cube's MDX script cube.MdxScripts[0].Commands[0].Text = newScript.ToString(); Finally we just need to update the cube in order to write the MDX back to the cube: //Call the update methods to update the cube cube.MdxScripts[0].Update(); cube.Update(); User Reports Now that the cube has been updated, if the Excel report is refreshed, then the two new products will appear in the list: Summary Controlling SSAS named sets like this won’t be for everybody. I’ve certainly worked in a few clients where strict process has to be followed to update this sort of thing, but I can think of other companies that I know where this would be really useful. Managing Analysis Services named sets is just one use for collections. Another example might be managing default multi-select parameters for SSRS reports. As collections are just lists of members that can be extracted easily, what you do with them is up to you!

Master Data Services Training in the UK

This is just a quick post to announce a range of SQL Server training courses, organised (and in some cases delivered) by Chris Webb. To start off there’s a SQL Server course delivered by Christian Bolton in December, followed by an Analysis Services course delivered by Chris in February. I’ll be delivering a Master Data Services course in February, before Chris delivers an MDX course in March. The details for all the courses are: SQL Server Internals and Troubleshooting Workshop - Christian Bolton – 6th – 7th December 2011 The Advanced Troubleshooting Workshop for SQL Server 2005, 2008 and R2 provides attendees with SQL Server internals knowledge, practical troubleshooting skills and a proven methodical approach to problem solving. The workshop will enable attendees to tackle complex SQL Server problems with confidence. Full details and registration here. Real World Cube Design and Performance Tuning with Analysis Services – Chris Webb – February 2012 A two day course that takes real world experience in showing you how to build a best practice Analysis Services cube, covering design issues such as data warehouse design and complex cube modelling. Day two then covers performance optimisation for Analysis Services, including MDX optimisation and cube processing. Full details and registration here. Introduction to Master Data Services with Jeremy Kashel – February 2012 An end to end look inside Master Data Services, this full day course will begin with a synopsis of Master Data Management before moving on to an overview of Microsoft SQL Server 2008 R2 Master Data Services (MDS). The remainder of the course will cover the major MDS topics, such as modelling and business rules, which will include a number of practical exercises. More details and registration for here. Introduction to MDX with Chris Webb – March 2012 The Introduction to MDX course aims to take you from the point of being a complete beginner with no previous knowledge of MDX up to the point where you can write 90% of the MDX calculations and queries you’ll ever need to write. The three day course covers the basics, such as sets, tuples, members to more advanced concepts such as scoped assignments and performance tuning. Full details and registration here.

Master Data Services Kindle Contest

This is a quick blog post to announce that Adatis are running a contest in conjunction with Packt around Master Data Services. The winner will receive an Amazon Kindle pre-loaded with our Microsoft SQL Server 2008 R2 Master Data Services book. In order to enter, all you need to do is follow @AdatisBI and copy and paste our eBook on Kindle message and retweet it using your twitter account. The message is: RT & Follow @AdatisBI for a chance to WIN the #Adatis MDS book pre-loaded on a Kindle ! ( Please mark your retweet with the hashtag (#), so that we can find and consider your entry. Only one entry per person will be accepted. You can find out the full details of how to enter, as well as terms and conditions, on the following page. Good luck!

Master Data Services Excel Add-in

Master Data Services in SQL Server Denali now includes an Excel add-in that allows MDS users to add and update data that exists within Master Data Services. For those of you that haven’t had a chance to download the latest CTP, this post gives an overview of what’s possible in the Excel Add-in. Excel Add-in Overview Once you install the Excel add-in (available here for download) you will see an additional toolbar in the ribbon, as shown below: At a high level, the following functionality is available within the MDS add-in: Retrieve members from a master data entity Add or Update members (including annotations) and publish the changes back to MDS Create entities in MDS if you have sufficient permission Run the business rules I don’t want this post to go on for ever, so today I’m going to focus on retrieving and updating members. Retrieving MDS Entity Members The Excel Add-in comes with a sidebar called the Explorer that can be used to connect to an MDS model and retrieve data from an entity within the model. What I quite like is that there is a filter button that allows a user to specify how to filter the data before loading it. In the example below, I’ve connected to the sample customer model, and have filtered the customer entity to only show customers of type 1 and customers from Seattle: On the same filtering window its possible to choose the attributes to display. Therefore in the above example, by clicking the ‘Load Data’ button, a filtered list of customer members will be shown: Editing Members Once the members have been retrieved, editing is just a simple case of typing into the field that you want to change. In the example below I’ve chosen to change the names of two of the customers, which has caused the cells to be highlighted, informing me of the changes that I’ve made: Domain-based attributes are also picked up nicely by Excel. Not that I imagine it would make too much sense, but it’s possible to change the Sales District North Western US (WUSSL) to Central US (CEUS), for example. Excel handles this by rendering a drop down for this domain attribute: As it is in Master Data Manager, within Excel it’s also possible to display a domain attribute’s name. Regardless of the type of attribute that gets changed, the changes will just remain in the Excel sheet until I click the publish button: Clicking the the publish button will result in being prompted to make an optional annotation for each of the changes: The annotations, as well as the member updates, will be committed to the Master Data Services database. The end result is that the transactions can be viewed as per 2008 R2 in Master Data Manager, or by right clicking in Excel and choosing ‘View Transactions’. Summary The Excel add-in doesn’t contain all the functionality available within Master Data Manager, but provides an interface that clearly Excel-based data stewards will be very comfortable with. It’s also much easier to do bulk updates in the Excel front-end, when compared to the web front-end. That’s about if for now. There’s more to the Excel add-in, which I’m aiming to cover at SQLBits 9, plus Master Data Manager has been given a complete overhaul in Denali – hopefully I’ll find the time to cover that soon…

Master Data Services Training

As I’m sure you’ve seen, registration for SQLBits 9 – Query Across the Mersey is now open. This time around I’ll be running a deep-dive training day with Tim Kent on Master Data Services. This will be a full day of training, showing you how MDS can be used to manage the master data in your organisation. We’re going to start by giving an overview of Master Data Management, before moving on to covering the following MDS topics in detail: Installing, configuring, and maintaining Master Data Services Creating and using models Version management Business rules and workflow Importing data into Master Data Services Integrating with other systems Security You can register for the MDS Training day, or one of the other 10 training days, by using the following registration page. Remember, the early bird discount expires at midnight on Friday this week! Update – We are running another MDS course in February 2012. Click here for the details.

A Pattern To Load Data to Master Data Services via SSIS–Part 2

Introduction This is the second part of a series of blog posts intended to show a way to load data in to MDS via SSIS. In part 1 we have loaded the staging tables with new members and attributes for several entities. In this blog post we are going to extend the SSIS package with tasks to move the data from the staging tables into the MDS Product model and validate these newly inserted members. Completing The Solution We need to move the data from the staging tables into the model. This is carried out by executing the MDS staging sweep process. To achieve this we need to add an Execute SQL Task to the control flow of our package. Rename the task – I’ve called mine ‘SQL – Sweep Stage’ and connect it up to the ‘DFL – Load Staging Tables’ task with a success constraint. On the General tab set the connection to MasterDataServices and the SQL Statement as follows: DECLARE @ModelName NVARCHAR(50) = ? DECLARE @UserName NVARCHAR(50) = ? DECLARE @User_ID INT DECLARE @Version_ID INT SET @User_ID = (SELECT ID FROM mdm.tblUser u WHERE u.UserName = @UserName ) SET @Version_ID = (SELECT MAX(ID) FROM mdm.viw_SYSTEM_SCHEMA_VERSION WHERE Model_Name = @ModelName) EXECUTE mdm.udpStagingSweep @User_ID, @Version_ID, 1 Then add the Parameter mapping as shown below: That’s all there is to do to get our data into the model. However this process is asynchronous and before we can validate the model we need to know when the staging sweep has finished. Add a For Loop Container task to the control flow of the package and connect it up to the ‘SQL – Sweep Stage’ task with a success constraint. Rename the task – I’ve called mine ‘FLC – Wait Until Batch Completes’. Add an Execute SQL Task inside the loop container task and rename it. Mine is called ‘SQL – Get Staging Batch Status’. Change the connection to MasterDataServices, change the ResultSet property to ‘Single row’ and then add the following SQL script to the SQLStatement property: DECLARE @Version_ID INT SET @Version_ID = (SELECT MAX(ID) FROM mdm.viw_SYSTEM_SCHEMA_VERSION WHERE Model_Name = ?) SELECT TOP 1 Status_ID FROM mdm.tblStgBatch WHERE Version_ID = @Version_ID ORDER BY ID DESC Add the parameter mapping as shown below: And the Result Set as shown below: Add three more parameters to the package as shown in the table below: Next configure the For Loop Properties as shown in the table below: The InitExpression value sets the @dtMDSLoopTimer to the current time plus the interval set in our @intMDSTimeout variable. The EvalExpression checks if the @strMDSBatchStatus is either not equal to 2 (Success) or the timeout has expired. The For Loop Container can only succeed if the staging batch is successfully loaded. Now we can validate the model so add an Execute SQL Task to the control flow and connect it to the ‘FLC – Wait Until Batch Completes’ task with a success constraint. Rename the task - mine is called ‘SQL – Validate Model’. Change the connection to MasterDataServices and the SQLStatement as follows: DECLARE @ModelName NVARCHAR(50) = ? DECLARE @UserName NVARCHAR(50) = ? DECLARE @User_ID INT DECLARE @Version_ID INT DECLARE @Model_id INT SET @User_ID = (SELECT ID FROM mdm.tblUser u WHERE u.UserName = @UserName ) SET @Version_ID = (SELECT MAX(ID) FROM mdm.viw_SYSTEM_SCHEMA_VERSION WHERE Model_Name = @ModelName) SET @Model_ID = (SELECT Model_ID FROM mdm.viw_SYSTEM_SCHEMA_VERSION WHERE Model_Name = @ModelName) EXECUTE mdm.udpValidateModel @User_ID, @Model_ID, @Version_ID, 1 Set the parameter mapping as follows: Almost done. Just to finish it off lets add a script task to the control flow and connect it to the ‘FLC – Wait Until Batch Completes’ task with a completion constraint. Change the Evaluation Operation of the constraint to ‘Expression and Constraint’ and set the Expression to ‘@strMDSBatchStatus != 2’. Edit the script and add the following line of code under   // TODO: Add your code here: Dts.Events.FireError(0, "SCR - Fire Error", "MDS Timeout Occurred", string.Empty, 0); This task will fire an error event if the MDS staging batch does not complete successfully. The finished package control flow should look similar to the following image: Execute this package and then check the Product entity in MDS. It should look something like the following: Looking at the other entities you will see that we have added members to three entities and validated all these new members. Summary Over the last two blog posts I have shown a way of automating the loading of data to Master Data Services via SSIS. This pattern can be used to cater for most of your loading requirements. That’s it, the completed Integration Services project source code and MDS Model can be downloaded from here – (You will need to create a login first). Your comments are very welcome.

A Pattern To Load Data to Master Data Services via SSIS

Part 1 Introduction Loading new members to a MDS entity will be a common requirement in all MDS implementations. In these blog posts I am going to walk you through building an SSIS package that performs the following processes: Load new members and attributes to several entities via staging tables Validate the MDS model that contains the entities In part one we will load the MDS staging tables ready to take our new members and attributes into our MDS model. For a thorough understanding of the staging process in MDS please see the Master Data Services Team blog post on Importing Data by Using the Staging Process. A pre-requisite is to have the AdventureWorks2008R2 database samples installed on the same instance of SQL Server as Master Data Services. In MDS I have created a model named ‘Product’ with an entity of the same name. The product entity has the following attributes which are set to the default type and length unless specified: Name Code Model (Domain Attribute) Culture (Domain Attribute) Description (Text, 500) We are going to load this entity with Product data from the AdventureWorks2008R2 database using a SSIS package. In addition to this there are two further entities in the Product model: Culture Model These entities have just the code and name attributes and are set to the default type and length. The MDS model and Integration Services project source code can be downloaded from here – (You will need to create a login first). Building The Solution OK enough of the intro let’s get on and build the package. Start a new Visual Studio Integration Services Project and save the default package to a more suitable name. I’ve called mine ‘LoadMDS.dtsx’. Create the following connection managers as shown below remembering to replace the Server and MDS database names. Rename the connection managers to ‘AdventureWorks’ and ‘MasterDataServices’ respectively. Now we need to create some variables so go ahead and create the variables shown in the table below: We are now ready to put our first task into our package. This task will optionally clear the staging tables of all successfully loaded members, attributes and relationships prior to loading, based on the value of the blnClearStage parameter. Add an Execute SQL Task to the control flow of your package and rename it - I’ve called mine ‘SQL – Clear Staging Tables’. On the General tab set the connection to MasterDataServices and the SQL Statement as follows: DECLARE @ModelName NVARCHAR(50) = ? DECLARE @UserName NVARCHAR(50) = ? DECLARE @User_ID INT SET @User_ID = (SELECT ID FROM mdm.tblUser u WHERE u.UserName = @UserName ) IF ? = 1 EXECUTE mdm.udpStagingClear @User_ID, 4, 1, @ModelName, DEFAULT ELSE SELECT 1 AS A On the Parameter Mapping tab add the variables exactly as shown below: Add a Data Flow task to the control flow of the package and connect it to the ‘SQL – Clear Staging Tables’ task with a success constraint. Rename the task to ‘DFL – Load Staging Tables’. Add three further variables to our package as follows: In the data flow of our package add an OLEDB data source task, set the connection to AdventureWorks and the Data Access Mode to SQL Command. Add the following SQL to the SQL command text window: SELECT CAST(p.ProductID AS VARCHAR(10)) + pmx.CultureID AS ProductCode ,p.Name ,p.ProductModelID ,pm.Name AS ProductModelName ,pmx.CultureID ,c.Name AS CultureName ,pd.Description FROM Production.Product p INNER JOIN Production.ProductModel pm ON p.ProductModelID = pm.ProductModelID INNER JOIN Production.ProductModelProductDescriptionCulture pmx ON pm.ProductModelID = pmx.ProductModelID INNER JOIN Production.ProductDescription pd ON pmx.ProductDescriptionID = pd.ProductDescriptionID INNER JOIN Production.Culture c ON pmx.CultureID = c.CultureID Don’t worry if the formatting turns ugly, that’s just what happens. Press the Preview button and you will see that this query will return us the following columns to our data flow: ProductCode Name ProductModelID ProductModelName CultureID CultureName Description We need two more columns in our data flow and to get them we will use a Derived Column transformation task so drag one on to the data flow from the toolbox and connect it up to the data source. Add the columns as shown in the image below: Next the data flow needs to be duplicated into multiple streams so that the different members and attributes can be loaded to the staging tables. This is achieved by adding a Multicast transformation task to our data flow. This task does not require any configuration. There will be six outputs from the Multicast task and these will be used to load the following: Product Members Model Members Culture Members Product Model Attributes Product Culture Attributes Product Description Attributes Each of these outputs needs to be tailored as to whether they will be loading a member or an attribute and also which member or attribute they are loading. Add six Derived Column transformation tasks to the data flow and connect them to the Multicast transformation. At this point our data flow should look similar to the following: For each of the Derived Column transformations add the additional columns as specified below: OK now we have got all the information we need in our data flows to start loading to the staging tables but before we do that there is one more thing to do. As we are loading new members to the Model and Culture entities as well as Product we need to ensure that we have only distinct values for our member codes to prevent staging errors. To achieve this we add and connect Aggregate transformation shapes to the data flows underneath the ‘Add Culture Member Information’ and ‘Add Model Member Information’ shapes. The images below show how to configure these aggregate transformation shapes: Group By Culture                                                             Group By Model We are now ready to load the data to the MDS staging tables. Add six OLE DB destination shapes to the dataflow. Three of the destinations will be to load new entity members and the other three will be to load attributes for these new members. Configure the Connection Manager properties of the destinations as follows: Members                                                                     Attributes Connect the first destination shape to the ‘Add Product Member Information’ shape and configure it as a member destination. Click the Mappings tab and set the Input and Destination column mappings as shown below: Connect the second destination shape to the ‘Group By Culture’ shape and configure it as a Member destination. The column mappings will be the same as above except for the MemberName and MemberCode columns and these will be set to CultureName and CultureID respectively. Connect the third destination shape to the ‘Group By Model’ shape and configure it as a Member destination. The column mappings will be the same as above except for the MemberName and MemberCode columns and these will be set to ProductModelName and ProductModelID respectively. Connect the fourth destination shape to the ‘Add Culture Attribute Information’ shape and configure it as an Attribute destination. The column mappings will be as follows: Configure the next two destinations as Attribute destinations and map the columns as the other Attribute destination replacing the AttributeValue mapping with ProductModelID and Description respectively. Now our completed dataflow should look similar to the following: If you execute the package you will see that we have inserted 1764 Product member rows, 6 Culture member rows and 118 Model member rows into the mdm.tblStgMember table and 1764 attribute rows for each of the Culture, Model and Description attributes into the mdm.tblStgMemberAttribute table in your MDS database. It is worth noting that the data has now been staged only and we will not see it in our MDS entities yet. OK that’s as far as we are going to go in part one. In part two we will extend the package to move the data from the staging tables into the MDS model and validate the newly inserted data.