Part 2: Converting Roles

Ämne: Business Intelligence

Blog series:

Part 1. Mutlidimensional vs Tabular

  • Introduction on using xslt
  • Converting data source

Part 2. Converting Roles
Part 3. Converting Dimensions (coming soon)
Part 4. Converting Relationships
Part 5. Converting Hierarchy
Part 6. Converting Measures
Part 7. Converting Calculated Measures (Name, Basic Calculation)

In the first part of our series we compared the technical differences between Multidimensional and Tabular mode OLAP cubes and how to convert a data source from one mode to the other. In this episode, we will show you how to convert roles from a legacy mode to a tabular one.

Let’s begin with looking at the XSLT file that was built to convert data sources from previous blog:

In this XSLT file, we brought relevant details of a data source like name, connection string over a new tabular model JSON format. Now we should look at how Multidimensional and Tabular Roles are structured. Below is the role metadata in rolename.role and role access in projectName.database:

On the left hand, is the master data of a role in Multidimensional mode, and on the right hand, it is the details of related role, which consists of access privileges like read, admin or process cube.

To convert this role to a tabular mode, we have to grab these pieces of information and combine them to create a new one. This is a simple task when we have only two or three roles to be migrated, however, it is a much bigger task when there are dozens of roles.

Again, at BizOne Asia, we have come up with a simple solution in which we consolidate all the metadata files into a single file and use XSLT to convert the required roles.

We simply created a new XML which hosts all the information of the legacy Cube, and the parent tag named . Technically, it takes almost no effort to consolidate all separate XML files into a single file using couples of lines of C# code or any language. Don’t forget to eliminate all the namespace to avoid unexpected results.

Let’s create a new XML file named consolidated.xml, with following content:

We also need to adjust the file datasource_xslt.xsl to adapt to this consolidated xml metadata file as following with additional tags of XSL for roles.

Master template to populate role details

But first, consider creating a new template called roles, with the following content and save this XSL in notepad:

Element Description
match = “Database” This is to map this template to the Element Database in the consolidated.xml, which store information about role access privileges
<xsl:for-each select=”/root/Database/DatabasePermissions/DatabasePermission”> Iterate through all the elements /root/Database/DatabasePermissions/DatabasePermission, which keep information of roles access privileges
“name”:”<xsl:call-template name=”role”>
<xsl:with-param name=”roleID” select=”RoleID”/>
<xsl:with-param name=”getType”   select=”‘rolename'”/>
</xsl:call-template>”
Call the template role, which is discussed next, and passing two values:
–          RoleID of current context
–          type: ‘rolename
This is to get the role name, and bring over to JSON
”modelPermission”: ”<xsl:call-template name=”permission”>
<xsl:with-param name=”roleID” select=”RoleID”/>
</xsl:call-template>”
Get the access privilege (read, refresh, admin…) via the template permission, which will be discussed later.
<xsl:call-template name=”role”>
<xsl:with-param name=”roleID” select=”RoleID”/>
<xsl:with-param name=”getType” select=”‘member'”/>
</xsl:call-template>
Get the member list of a role in current context by calling the template role and passing two values:
–          RoleID of current context
–          type: ‘member

 

Now let’s move to create two function templates, which are called in upper XSL template.

Getting role name and member list

To get the role name or member list, making a call to the template role which is constructed as follows and save this XSL in a notepad:

Element Description
match = “Role” This is to map this template to the Element Role in the consolidated.xml, which store information about role name, role ID
<xsl:param name = ”roleID” />
<xsl:param name = ”getType” />
A param to receive a passed roleID from a calling template and type of getting information
<xsl:for-each select=”/root/Role[ID=$roleID]”> Iterate through all the elements /root/Role[ID=$roleID], but only filter a concerned roleID
<xsl:when test=”$getType = ‘member'”> When the param $getType value is ‘member’
Return a list of members who have been assigned to the related role
<xsl:when test=”$getType = ‘rolename'”> When the param $getType value is ‘rolename’
Return the name of role for a related roleID

 

Getting role access privilege

Now we have brought the roles and their members over Tabular mode, what about the access privilege? This is accomplished easily via couples of XSL tags as follow and save this XSL in a notepad:

 

Element Description
match = “Database” This is to map this template to the Element Database in the consolidated.xml, which store information about role access privileges
<xsl:param name = ”roleID” /> A param to receive a passed roleID from a calling template
<xsl:for-each select=”/root/Database/
DatabasePermissions/DatabasePermission[RoleID=$roleID]”>
Iterate through all the elements /root/Database
/DatabasePermissions/DatabasePermission[RoleID=$roleID]”>,
but filter only a concerned roleID.
<xsl:when test=”Process = ‘true’ and ReadDefinition =’Allowed’ and Read=’Allowed’ and Administer=’true'”>administrator</xsl:when>
<xsl:when test=”Process = ‘true’ and Read=’Allowed'”>readRefresh</xsl:when>
<xsl:when test=”Process = ‘true'”>refresh</xsl:when>
<xsl:when test=”Read=’Allowed'”>read</xsl:when>
Under /root/Database/DatabasePermissions/DatabasePermission
There are 4 kinds of element:
– Process
– ReadDefinition
– Read
– Administer
This is used to bring these values over a Tabular mode accordingly

 

Once we have constructed all necessary XSL templates, now it is time to merge these into a single XSLT file as follows, then save it as datasource_xslt.xsl

 

<?xml version=”1.0″?>
<xsl:stylesheet version=”2.0″
xmlns:xsl=”http://www.w3.org/1999/XSL/Transform”>
<xsl:output method=”text” version=”1.0″ encoding=”UTF-8″/>
<xsl:template match=”root” name=”model”> <!—change this match to root instead of / as previous episode –>
{
”name”: ”SemanticModel”,
”compatibilityLevel”: 1200,
”model”: {
”culture”: ”en-US”,
<xsl:call-template name=”datasource”/>
,
<xsl:call-template name=”roles”/>
,
”annotations”: [
{
”name”: ”ClientCompatibilityLevel”,
”value”: ”400”
}
]
},
”id”: ”SemanticModel”
}
</xsl:template>
<!– this template is for mapping datasources between two wolrds–>
<xsl:template match=”DataSource” name=”datasource”>
”dataSources”: [
<xsl:for-each select=”DataSource”>
{
”name”: ”<xsl:value-of select=”Name”/>”,
”connectionString”: ”<xsl:value-of select=’ConnectionString’/>”,
”impersonationMode”: ”impersonateAccount”,
”account”: ”windowsUser”,
”annotations”: [
{
”name”: ”ConnectionEditUISource”,
”value”: ”SqlServer”
}
]
}
<xsl:choose>
<xsl:when test=”position()!=last()”>,</xsl:when>
</xsl:choose>
</xsl:for-each>
]
</xsl:template>
<!– this template is for mapping roles between two worlds–>
<xsl:template match=”Database” name=”roles”>
”roles”: [
<xsl:for-each select=”/root/Database/DatabasePermissions/DatabasePermission”>
{
”name”: ”<xsl:call-template name=”role”>
<xsl:with-param name=”roleID” select=”RoleID”/>
<xsl:with-param name=”getType” select=”‘rolename'”/>
</xsl:call-template>”,
”modelPermission”: ”<xsl:call-template name=”permission”>
<xsl:with-param name=”roleID” select=”RoleID”/>
</xsl:call-template>”
<xsl:call-template name=”role”>
<xsl:with-param name=”roleID” select=”RoleID”/>
<xsl:with-param name=”getType” select=”‘member'”/>
</xsl:call-template>
}
<xsl:choose>
<xsl:when test=”position()!=last()”>
,
</xsl:when>
</xsl:choose>
</xsl:for-each>
]
</xsl:template>
<!– this template template acts as a function to look up detail of role name or members that has been assigned the associated role it is called from template roles –>
<xsl:template match=”Role” name=”role”>
<xsl:param name = ”roleID” />
<xsl:param name = ”getType” />
<xsl:for-each select=”/root/Role[ID=$roleID]”>
<xsl:choose>
<xsl:when test=”$getType = ‘member'”>
,
”members”: [
<xsl:for-each select=”Members/Member”>
{
”memberName”: ”<xsl:value-of select=”Name”/>”,
}
<xsl:choose>
<xsl:when test=”position()!=last()”>
,
</xsl:when>
</xsl:choose>
</xsl:for-each>
]
</xsl:when>
<xsl:when test=”$getType = ‘rolename'”><xsl:value-of select=”Name”/></xsl:when>
</xsl:choose>
</xsl:for-each>
</xsl:template>
<!– this template template acts as a function to look up the access privilege that a role has it is called from template roles –>
<xsl:template match=”Database” name=”permission”>
<xsl:param name = ”roleID” />
<xsl:for-each select=”/root/Database/DatabasePermissions/DatabasePermission[RoleID=$roleID]”>
<xsl:choose>
<xsl:when test=”Process = ‘true’ and ReadDefinition =’Allowed’ and Read=’Allowed’ and Administer=’true'”>administrator</xsl:when>
<xsl:when test=”Process = ‘true’ and Read=’Allowed'”>readRefresh</xsl:when>
<xsl:when test=”Process = ‘true'”>refresh</xsl:when>
<xsl:when test=”Read=’Allowed'”>read</xsl:when>
</xsl:choose>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>

Now, let’s try open the consolidated.xml with a web browser, and see the output JSON content. It should be like this

{
”name”: ”SemanticModel”,
”compatibilityLevel”: 1200,
”model”: {
”culture”: ”en-US”,
”dataSources”: [
{
”name”: ”Test”,
”connectionString”: ”Provider=SQLNCLI11.1;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Test”,
”impersonationMode”: ”impersonateAccount”,
”account”: ”windowsUser”,
”annotations”: [
{
”name”: ”ConnectionEditUISource”,
”value”: ”SqlServer”
}
]
}
]
,
”roles”: [
{
”name”: ”ReadOnly”,
”modelPermission”: ”read”
,
”members”: [
{
”memberName”: ”NT AUTHORITY\Local account”
}
]
}
]
,
”annotations”: [
{
”name”: ”ClientCompatibilityLevel”,
”value”: ”400”
}
]
},
”id”: ”SemanticModel”
}

Copy and paste this JSON into a Model.bim file, then open in Visual Studio. Note: ”NT AUTHORITY\Local account” will cause an error message like this:

An error occurred while opening the model on the workspace database. Reason: Cannot de-serialize Database. The JSON input is not properly formed. Check path ‘model.roles[0].members[0].memberName’, line 36, position 42.

 

This is because of ‘\’, which needs to be escaped. To escape this, simply double the character occurrence like ‘\\’. It should be like ”NT AUTHORITY\\Local account”. I believe you can figure out the way to programmatically escape these character, therefore, I won’t discuss it here.

Finally, save the Model.bim file after escape the character, then open the model again in Visual Studio. Now, you should be able to see data source, role along with its member converted to Tabular mode.

So now we have converted our data source and roles to Tabular mode from Multidimensional. In the next episode (coming soon), we will demonstrate how to convert the dimensions into Tabular mode!

If you want to learn more about the benefits of moving to Azure, read more here.

Are you interested in migrating to Microsoft Azure? Then contact us today!