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.
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
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.
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.
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
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 ;
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.