(scottish) sql bob blog

rss

Some thoughts of your typical data shepard / data groomer / data dance teacher sort of person.


Power BI online user credentials and multiple accounts
Whilst "publishing" some reports for some of our customers from PowerBI desktop to PowerBI online. I made the assumption that the data credentials used in PowerBI desktop would be the same ones used in PowerBI online.

Scenario 
We wanted to share PowerBI reports with some of our customers. It is not possible currently to allow anyone from outwith our Azure AD group to securely access the PowerBI reports. So the only other solution was to get our Office 365 Admin to create an account for each customer. All the customers will connect to the same database to extract their data. On the database an account was created for each customer, so when they connected to the database the customers account could only access that customers data.

First step - Find credentials in PowerBI Desktop 

Open PowerBI desktop at the top left hand side click once with left hand mouse button the menu icon .  The from the menu that appears, go to 'Options and settings' menu item and click once with left hand mouse button (see figure 1)

 
Figure 1 - Showing the 'Options and settings' menu item. 
 
In the 'Data Source Settings' window select the data source (3), that is to say where the data is extracted from. Next press the 'Edit' button (4) this will open the window shown in Figure 2.

Figure 2 - First data source settings window.

In the second  'Data Source Settings' window click on the 'Edit' button 


Figure 3 - Second data source settings window

Now you can get the details eg Username and password used to connect to the data source are stored. If these are required. 
 
Figure 4 - Data source credentials window
 
So we now have the follow credentials
Username = [email protected] 
Password = JoeB123 
N.B. We will be using these details later. 

Tip if using multiple accounts with PowerBI online  
Next step would be to publish your reports to the PowerBI service at PowerBI.com.   If you have multiple accounts which you use to publish your reports to a PowerBI service. Then one tip is log out of the account and log back in as there is nothing in the PowerBI desktop application which indicates which account is logged in. 

Adding credentials in PowerBI Service (Aka PowerBi.com) 
One quick tip here is to open PowerBi.com using Internet Explorer. At the time of writing process shown next did not reliability render in other browsers.  Log into PowerBI.com once successfully log in, go to the top right hand side of the window. Click with left hand mouse button the settings icon, from the menu click on the 'Settings' menu item. 

 
Once the 'Settings' window opens click on the 'Datasets' tab 

 

 Once in 'Settings->Datasets' click once with left hand mouse button on the triangle to the left of 'Data source credentials' 



 Once the 'Data source credentials' have been expanded click on the 'Edit credential' link. 

 

 Once the  credentials configuration window opens the drop down box below the authentication method select the 'Basic' authentication method. 

 

 When you select the 'Basic' Authentication method, text boxes will appear at the bottom of the form. Enter the username and password, then click on the 'Sign In' button. 



 If the user name and password has been successful then you will see a message appear in the top right hand corner of the PowerBi window, indicating the data source has been successfully updated. 




Ordering month order in Power BI desktop

Whilst preparing a simple dataset to with a stacked bar chart in PowerBI l included both the month number and name.  The dataset is shown below

Month_Number Month_Name Total Type
1 January 61 Created
1 January 63 Resolution
5 May 14 Created
5 May 8 Resolution
6 June 24 Created
6 June 26 Resolution
7 July 33 Created
7 July 36 Resolution
8 August 51 Created
8 August 48 Resolution
9 September 126 Created
9 September 109 Resolution
10 October 147 Created
10 October 152 Resolution
11 November 98 Created
11 November 88 Resolution
12 December 65 Created
12 December 77 Resolution

The data set was created in PowerBI desktop, the next step was to plot the data on a clustered column chart.  The month name was the value l wanted to appear on the axis, there was a little challenge with the way data was being displayed.  The month names where being displayed in alphabetical order (see Figure 2)

Figure 2 - Power BI chart after placing data on the canvas

What l wanted was the month names to be sorted according to month numbers so l knew l had missed something.  First step was to go to the data tab to have a look at the data.  So I licked on the the dataset and selected the 'Modeling' tab (see Figure 3).


Figure 3 - Data view 'Modeling' tab selected the 'Month_Name' column selected

Everything looked the way l expected then with the 'Month_Name' column selected l clicked on the 'Sort By Column'.  This was sorted by 'Month_Name' so it would be sorted alphabetically, so l changed it to sort by the 'Month_Number' see Figure 4.

Figure 4 - The first figure shows the default setting for the 'Month_Name' column, the second figure shows the amended selection to sort the 'Month_Name'Column by the 'Month_Number' column

Then l returned to the report view to review the change see Figure 5


Figure 5 - The clustered bar chart report showing the Month Names sorted in the order of the 'Month_Number' column

Success the months where ordering as l wanted them, next I'm off to resolve my next challenge, of which there is never a shortage.


Monitoring replication status using Nagios (using PowerShell script and TSQL)

For various reasons which l have now forgotten, l set up transactional replication for some clients.  The result of this is l am the caretaker of transactional replication for two of our clients, what l lucky person l am !

T-SQL code used to check Transaction replication 
Once l got the process up and running (a very, very long and stressful story).  At that point l realised that I would have to monitor these processes.  Following some goggling this article was found with some TSQL written by SQLSoldier here.  This worked for me and l used this to monitor replication by running the script and checking the results manually.
 
Nagios output 
Our SysAdmin uses a tool called nagios to monitor out IT estate.  So they suggested that a script could be written to monitor the replication process and send an alert if anything needed to be looked at.  This sounded like an excellent idea, how to do it?  The approach that was arrived at, involved using a PowerShell script which would run a SQL query examine the results then respond to Nagios, with the following values  

0 - all ok nothing to see here 
1- something happening nothing to worry about (just now) 
2-yes there is something that really needs some attention

See this page for more guidance.

Stored procedure 

Next we decided to use PowerShell to return the results.  Following some goggling we found this page http://www.madeiradata.com/cross-server-replication-health-check-using-powershell/ which runs a SQL script and returned a data set.  First challenge was the TSQL script from SQLSoldier was rather long for my first powershell script, l wanted some thing smaller.  So l created a stored procedure based on the script, and placed it in my Distribution database on the replicated database server.  Doing this had a two reasons, first less TSQL in the PowerShell script, second changing one of the parameters meant it returns different amounts of data.  The stored procedure takes the following parameters ; 

@Publisher  - The name of the publisher database server instance 
@PublisherDB - The name of the publisher database 
@NagiosOutput - Y = only output return code and short error code( max of 80 characters), N = output all results 

The following script was used to check the results that would be returned to Nagios. 
USE [distribution]; 
DECLARE @Publisher AS sysname 
DECLARE @PublisherDB AS sysname 
DECLARE @NagiosOutput AS Char(1) 
SET @Publisher = 'ReplPublisherServerName' 
SET @PublisherDB = 'ReplPublisherDBName' 
SET @NagiosOutput = 'Y' 
EXEC [dbo].[Check_Replication] @Publisher, @PublisherDB, @NagiosOutput; 

Note the account connecting to the database from Nagios will require execute permissions to the stored procedure otherwise it cannot run the stored procedure.  The code for the stored procedure is here.

PowerShell script 
Having adapted the PowerShell script found here to run the stored procedure.  When the PowerShell script was run by Nagios there was no 'Return Code' returned (this is what Nagios expects).  We did find the solution on this page, and inserted function ExitWithCode, and made a few other changes.  The resulting PowerShell script is below -

## Beginning of Monitor 
##Connection String With Server Variable, Distribution Database name is 'Distribution' 
$con = "server=127.0.0.1;database=Distribution;Integrated Security=sspi" 
 
##Begin SQL Query 
$cmd = "SET NOCOUNT ON; " 
$cmd = $cmd + " USE [distribution];" 
$cmd = $cmd + " DECLARE @Publisher  AS sysname" 
$cmd = $cmd + " DECLARE @PublisherDB AS sysname" 
$cmd = $cmd + " DECLARE @NagiosOutput AS Char(1)" 
$cmd = $cmd + " SET @Publisher = 'ReplPublisherServerName' " 
$cmd = $cmd + " SET @PublisherDB = 'ReplPublisherDBName' " 
$cmd = $cmd + " SET @NagiosOutput = 'Y'" 
$cmd = $cmd + " EXEC [dbo].[Check_Replication] @Publisher, @PublisherDB, @NagiosOutput;" 
 
##Creating DataSet Object 
$set = new-object system.data.dataset 
##Running Query 
$da = new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $con) 
##Filling DataSet With Results 
$da.fill($set) | out-null 
##Creating Table Object and Inserting DataSet 
$dt = new-object System.Data.DataTable 
$dt = $set.Tables[0] 
 
## loop over each column in the DataSet 
foreach ($row in $set.Tables[0].Rows) 

##write out the 2nd row which contains the message text 
write-host $row[1].ToString() 

$exitcode = $row[0].ToString() 
 
## The 'exit code' fragment below was adapted from: 
## http://weblogs.asp.net/soever/returning-an-exit-code-from-a-powershell-script 
##SysAdmin, 2015-Nov 
function ExitWithCode  
{  
    param  
    (  
        $exitcode  
    ) 
    $host.SetShouldExit($exitcode)  
    exit  

 
The output from stored procedure when  is very short.  Note that Nagios only permits a maximum of 80 characters to be returned.  Hence the sample output if run the TSQL will looking this ; 

No issues
Error Code   ErrorMessage  
0 Replication OK 

Issue(s) requiring attention 
Error Code  ErrorMessage  
2  TNotRepl=45 CNotRepl=4 Latency=5 Status=In progress 

Nagios expects a error message of a maximum of 80 characters which is the reason for the brevity of the error message. The error messages are -

TNotRepl=45
 -  'Transactions not replicated' total number of commands queued to be applied to the subscriber database. 
CNotRepl=4 -  'Commands not replicated' total number of commands queued to be applied to the subscriber database 
Latency=5 - This is the time taken to from when a transaction is applied to the publisher database the amount of time it takes till the same transaction is applied to the subscriber database (in seconds).  
Status=In progress - current status of the replication process

When considering this error message it was primarily to give some guidance as to what might be happening with the replication process.  It is not intended to give any guidance on the underlying reason that is causing the issue.  All that is required is that the Nagios process shows that there is something wrong.  What ever the reason it requires some form of human intervention.  Once an error condition has been detected then the issue will be handed to me to resolve.  At least now l do not have to check the process periodically, now l just have to wait for a message from our sysadmin.