Power BI Reporting with Reader-Writer cube Implementation.

Recently I have implemented Reader Writer cube scenario with Power BI reporting with the following requirements:

  1. There are two cubes and at one point in time One of the cube is reader and another one is processing and flagged as writer.
  2. These cubes are hosting a time critical report in Power BI Report Server.
  3. When a user is viewing a report he should always be connected to Reader cube so he gets a refreshed data while writer cube is still processing.
  4. Once the Reader cube becomes a writer reports should fetch the data from another cube which is now reader cube.
  5. Writer to reader is switched automatically for all the reports without user intervention.

Here is the step by step instruction to achieve this:

Step1: In order to achieve this scenario we need to change the report data source dynamically.

PowerBI Report server stores all the information about a reports in a database called ReportServer. We will modify the data source with Power shell. First we will find the report ID by using this command. In this example I am taking the example of default report Sales Order. Please note down the UserName,Password and ConnectionString values.

Select ItemID, UserName,Password,ConnectionString from DataSource where ItemID in
(select ItemID from Catalog  where name = 'Sales Order' )

Step2: Now we will connect the report to the second cube and again run the same command. Please note down the ItemID,UserName,Password and ConnectionString values.

Select ItemID ,UserName,Password,ConnectionString from DataSource where ItemID in
(select ItemID from Catalog  where name = 'Sales Order' )

Step3. Now we already have the UserName,Password and ConnectionString values for both the cubes. We will use the following PowerShell script to change the values.

# First of All Install SQL Server Powershell Module
Install-Module -Name SqlServer -AllowClobber -verbose
#Import SQL Server Module
Import-Module SqlServer
$PSVersionTable.PSVersion

#This is the server where Report server is hosted
$dataSource= 'ReportServerName'
$username='Domain name\Service Account'
$password='Password’
$database='Reportserver’
#Connection string to connect to SQL Server
$connectionString = "Server=$dataSource;uid=$username;pwd=$password;Database=$database;Integrated Security=False"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString=$connectionString
$ConnectionString = 'Server=localhost;Database=ReportServer;Trusted_Connection=true"
#Preparing the Query for two cubes 

$FirstCube =
"UPDATE [ReportServer].[dbo].[DataSource] set
Connectionstring= convert(VARBINARY(MAX),'Image DataType Value coming from connection String field',1)
,UserName= convert(VARBINARY(MAX),'Image DataType Value coming from Username field',1),
Password=convert(VARBINARY(MAX),’Image DataType Value coming from password field',1) 
where ItemID = 'ItemID Taken from Step1’"

$SecondCube =
"UPDATE [ReportServer].[dbo].[DataSource] set
Connectionstring= convert(VARBINARY(MAX),'Image DataType Value coming from connection String field',1)
,UserName= convert(VARBINARY(MAX),'Image DataType Value coming from Username field',1),
Password=convert(VARBINARY(MAX),’Image DataType Value coming from password field',1) 
where ItemID = 'ItemID Taken from Step1’"

# Now Invoke the prepared query remotely to the Report Server Machine to change the connection string to First Cube
Invoke-Sqlcmd -Connectionstring $connectionString -Query $FirstCube

# Now Invoke the prepared query remotely to the Report Server Machine to change the connection string to Second Cube
Invoke-Sqlcmd -Connectionstring $connectionString -Query $SecondCube

Step3: Call the above Powershell after enabling the remoting on the cube server and PowerBI report from the server which is responsible to change the flag from Write to reader cube. This can be a script or a ETL which can call this.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.