Power BI Reporting with Reader-Writer cube Implementation.

Recently I have implemented the 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 cubes is a reader and another one is processing and flagged as a 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 the Reader cube so he gets the latest data while the writer cube is still processing.
4. Once the Reader cube becomes a writer reports should fetch the data from another cube which is now the reader cube.
5. The writer to the reader has 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 report in a database called ReportServer. We will modify the data source with PowerShell. First, we will find the report ID by using this command. In this example, I am taking the example of the 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

Step4: 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 an ETL which can call this.