BI on SQL Azure - Yes you can

Just 4 years before, it was not possible to have end to end Business Intelligence (BI) solution on SQL Azure. Please read my blog here which was actually written 4 years back.

Now things have been changed dramatically. If you go through my blog than under conclusion at last section I have mentioned that “As per current scenario Microsoft Cloud Platform seems to be positioned as one of the leading platforms for DW/BI because it demonstrates great potential in early stage. We expect a great future of SQL Azure for DW/BI if Microsoft promises to enhance it continuously to overcome its limitation.”

Microsoft shows an awesome approach to address my conclusive remark and now we can truly do BI with some constraints on SQL Azure by using Microsoft Azure Virtual Machine gallery includes images that contain SQL Server installations. To have more details on the same read here.

 3 cheers to Microsoft and Azure!

Master Data Services 2016 CTP 2.2 - Explorer functionality quick fix

Microsoft has recently released SQL Server 2016 CTP 2.2. I was concerned about the Master Data Services and was exploring the new features. I got surprised when I see that Explorer functionality was not working. Generally it is used to manage members for Entities and Hierarchies. It is one of the main functionality and frequently used in MDS. Please note that I have used the SQL 2016 Server CTP 2.2 from window azure environment. I feel this issue could be the same if you download and install CTP 2.2 on virtual machine.

Please refer to below screen shot:

You can conclude from above screen shot that that Explorer functionality is disabled as well as you can’t select the version of the model. If you navigate through Version Management or Integration Management, you are allowed to select the version. This is quite strange.

It was not possible to view the members at all. I have complaint about it at some site as well on twitter, but no response till yet.

Alternatively, I used Excel add in to view and modify the members and test couple of deployment scenario. But it was quite annoying. I tried to find a workaround for it and luckily I got succeeded.  I am sharing this to all of other MDS users so that everybody can use and test the MDS CTP 2.2 without any interruption.

To access the explorer functionality, you just have to replace the proper value enclosed in ‘<>’ and paste into address bar.

<Your MDS Server URL>/Explore?MID=<MUID of the Model>&VID=<MUID of Version of the model>#/ExplorerEntity?MID=<MUID of the Model>&VID=<MUID of Version of the model>&EID=<MUID of Entity which you want to explore>

Once you replace ‘<>’ with proper value, Explorer functionality will be up and running. To replace the proper value for above string I created some SQL Script. Once you execute it, it will give you the exact explorer MDS URL for the specific entity. You just have to paste it into the address bar.

Execute below script against MDS database.

DECLARE @MDSServerURL  VARCHAR(100) = 'http://sql2016ctp222:81/' -- <Your MDS Server URL>

DECLARE @ModelName     VARCHAR(50) = 'CustomerSample'           -- <Model Name>

DECLARE @EntityName    VARCHAR(50) = 'Area'                     -- <Entity Name>

DECLARE @MDSExploreURL VARCHAR(8000)

DECLARE @MUIDModel         uniqueidentifier

DECLARE @MUIDModelVersion  uniqueidentifier

DECLARE @MUIDEntity        uniqueidentifier

-- Get MUID for the Model

SELECT @MUIDModel = MUID

FROM mdm.tblmodel

WHERE Name = @ModelName

-- Get MUID for the version of the model

SELECT @MUIDModelVersion = v.MUID

FROM mdm.tblModelVersion v

       INNER JOIN mdm.tblmodel m

              ON v.Model_ID = m.Id

WHERE m.Name = @ModelName

--Get MUID for the entity you want to explore

SELECT @MUIDEntity = e.MUID

FROM mdm.tblEntity e

       INNER JOIN mdm.tblmodel m

              ON e.Model_ID = m.Id

WHERE m.Name = @ModelName

SET @MDSExploreURL = @MDSServerURL + '/Explore?MID='

                                  + CAST(@MUIDModel AS VARCHAR(255))

                                  + '&VID=' + CAST(@MUIDModelVersion AS VARCHAR(255))

                                  + '#/ExplorerEntity?MID='

                                  + CAST(@MUIDModel AS VARCHAR(255)) + '&VID='

                                  + CAST(@MUIDModelVersion AS VARCHAR(255)) 

                                  + '&EID=' + CAST(@MUIDEntity AS VARCHAR(255))

--copy the below result and paste it into address bar and you are there

SELECT @MDSExploreURL

I just copied the URL and paste it into browser and I got below screen:

Now you can choose any entity and explore J

Now it is cake time J Enjoy reading.

Entity Sync Relationship - Master Data Services 2016

Entity Sync Relationship  is a new feature in Master Data Services 2016. It enables you to share entity data between different models. You can keep a single version of truth in one model and reuse it in an another model.

I was wondering where should I use it? Do I have any business scenario where it can be applicable?

I have many models which use Product master data. I could have 2 ways to handle it. I can create multiple models and add Product as an entity to those models. I am just repeating Product entity in all my models and ask me genuinely , I don’t like it. If I follow the best practices than I can create other models as an entity under Product model itself. This is good way to handle it but it has its own limitation.  Sometimes all other entities is independent and it just want to use the Product. So I would prefer to create independent models and want to use the Product entity across models.

Actually Entity Sync Relationship is quite useful feature.  Just relating above with an example. Assume you have a model for Product where Product is itself an entity. Now you want to build a model for Customer where you have a necessity to use Product entity. In this case you have to create a new entity called Product under model name Customer and you have to add members for the same. If product table is a huge table than you might create an ETL package preferably SSIS to first bring data from Product Entity in Product Model. You also need to run this package incrementally to sync the data between Product entity from source model (Product) to target model (Customer).

Now life becomes easier due to Entity Sync Relationship.

To create an entity sync relationship

In Master Data Manager, click System Administration.

Please refer to screen shot below:

Now click on the Model View page, from the menu bar, point to Manage and click Entity Sync.

Please refer to screen shot below:

 

On the Entity Sync Maintenance page, click Add as shown below:

 

Now  choose appropriate models and entity and version from source and target. Please refer to below screen shot:

Note – There are some constraints as well as prerequisites with respect to Entity Sync Relationship . To know in more detail, please refer to msdn.

 

 

Versions (Master Data Services) - Where to use

Multiple versions of the master data within a model can be created in Master Data Services(MDS). When you create a model for the first time, Master Data Services (MDS) creates a default initial version of the model data.

While working with MDS, I was trying to find when to use Versions. I have googled it and I didn’t find sufficient answer, so I tried few things from my side. I have considered that I already have a model called Product which is developed and moved to Production after testing it very well. Now I want to use the Versions feature supported by MDS. So question is where it will fit in or what could be the best case to use it?

Before getting into details of scenario let us know little bit about Versions. We can create sequential or simultaneous versions of our model in MDS.

Sequential Versions  - Version can be created one after another in sequence manner. For example, you can copy Version 1 of your model and name the copy Version 2.

Simultaneous versions  - Create simultaneous versions of your model when you want to work on two or more versions of your data at once. It means this can also be created simultaneously, so multiple versions can exist in parallel.

Let us take the best out if simultaneous version  which can help business in such a manner that  one consistent version can be dedicatedly available for consumption by the all consumers of Product master data. Another version of Product master data can exist in parallel that can be used for adding or deleting or modifying members or for testing the model data.

Now the questions is how should be attain this?

Suppose you have the Product model which looks like below in Production environment.

Environment	Model	Versions	Version Flag	Status	Subscription View	Copied From Version	Comments
Production	Product	V1	F1	Open	S1	-	Version in use

 Now to attend our scenario we have to follow below steps:

1. First create 2 flags F1 and F2 for version V1 if it does not exist
2. Add flag F1 to version V1
3. Lock V1 if it is used by multiple users
4. Validate V1
5. Commit V1
6. Create new version V2 from old version V1
7. Map Flag F1 to newly created version V2
8. Version V2 with Flag F1 will be current version in use
9. Create new version V3 from old version V1
10. Map Flag F2 to newly created version V3
11. Version V3 with flag F2 will be version where you can make modifications

For Assigning, Locking, Validating and Committing versions, please refer to MSDN. I don’t want to repeat those steps as it is already available on internet.

After following above steps the Product model will look like below in Production environment:

Environment	Model	Versions	Version Flag	Status	Subscription View	Copied From Version	Comments
Production	Product	V1		Commited	S1	-	Locked
Production	Product	V2	F1	Open	S2	V1	Version in use
Production	Product	V3	F2	Open	S3	V1	Version available for making modifications

 It means now V2 with F1 is open for use for consumers of Master Data and V3 is available for making changes as well as testing the model. Now assume you are done with your changes in V3 and you want that consumers should start using V3. How you will make it happen? It is simple and you have to follow below steps:

1. Validate V2 and V3
2. Lock V2 and V3
3. Commit V2 and V3
4. Create new version V4 from old version V3
5. Map Flag F1 to newly created version V4
6. Version V4 with Flag F1 will be current version in use
7. Create new version V5 from old version V3
8. Map Flag F2 to newly created version V5
9. Version V5 with flag F2 will be version where you can make modifications

After following above steps the Product model will look like below in Production environment:

 

Environment	Model	Versions	Version Flag	Status	Subscription View	Copied From Version	Comments
Production	Product	V1		Commited	S1	-	Locked
Production	Product	V2		Commited	S2	V1	Locked
Production	Product	V3		Commited	S3	V1	Locked
Production	Product	V4	F1	Open	S4	V3	Version in use
Production	Product	V5	F2	Open	S5	V3	Version available for making modifications

I have also come across multiple constraint while experimenting above steps. It is good to know about it before you start blindly using  it.

Constraints ( Very Very Important)

• As the subscription views are created based on the Version Flags and not on Version. So we don’t have to change the subscription views for the newly created version. THIS IS ONLY APPLICABLE IF you add members to entity.
• When you add attribute to entity then all the subscription view referring to that entity, needs to be regenerated to reflect this change. The old view will still be working but will not show the newly added attribute.
• You can't delete attribute if you have already defined subscription view for the same entity and model. If you still want to delete it then you have to first delete the subscription view than delete the attribute and then recreate the subscription view.