The email telling me that I had been selected to speak was to say the least a surprise. My session “Soft Skills for Success” had been accepted for Data in Devon 2019. Yes I am very excited about the opportunity to speak in Exeter. My last time in Exeter was a school holiday many years ago, so the opportunity to return that beautiful part of the country is wonderful surprise.
It might seem a little strange that I was surprised that my session was accepted. However, I have been submitting sessions for a while now, most of which have been unsuccessful. This pattern is much like the one I experienced when I decided to moved careers into IT a couple of years ago and I was applying for IT roles. One of the lessons I learned during that time is that you are going to fail more than you succeed. So it pays to keep trying and mostly importantly learn from each failure and success. The current lesson I am learning is all about abstracts, how can I improve them? make them better? As I know if one strategy does not work, then I need review it, make a small change, then try the changed strategy, till I find the one that works, so that’s what I am doing with my submission abstracts.
To be invited to speak is to my mind an honour and a privilege. Why? People who I see as peers and who I respect have reviewed and considered the abstract that I have submitted, then invited me to speak. They are also putting their faith in me that on the day I will be there and will deliver a session which people will want to see / listen to / learn something from. When I get there on the day, yes, no doubt I will be nervous, worried, and have checked everything at least twice. Before the getting to the room on the day, I will have rehearsed the session, reviewed it been through it more than once. Just now I am working on tweaks to the session just for Data In Devon, if you want to know more you will have to attend the session.
The topic “Soft Skills for Success” is something that I am passionate about. So, I will be giving it my all. Like many projects that I work on, what people see is the final, smallest part of the project. Beforehand there will be a lot of unseen hard work to make it look good. The prize is worth the price, which is to be invited to speak to my peers about a topic that I am passionate about.
One of the attractions of being a programmer was that l could find ways to make routine tasks easier and quicker. As our company and clients have discovered the joy of Power BI also the reports l have created. There are more clients are asking for reports. Some of these are the same reports to ensure everyone has the same view of their data. Scenario There are two databases l populate with data, one of these is on my machine, the other is an Azure database. As you would expect l use the local copy to test out various changes to the database. Then using the Redgate tool SQL Compare l can script any changes and apply them to the Azure database. It is possible to change the data source for each dataset easily using the following method. From the main window, on the 'Home' tab click once on the 'Edit Queries' button see Figure 1 Figure 1 - showing the location of the 'Edit Queries' button
This will open the 'Edit Queries' window now to see where the data source for a selected dataset. First select a dataset in the example shown in Figure 2, it is 'DimDate'. Next click on the 'Advanced Editor' button see figure 2.
Figure 2 - Showing location of 'Advanced Editor' button When the Advanced Editor window opens it will look something like the one shown in Figure 3 below. Figure 3 - Advanced Editor window, with M (Power Query Formula Language) query showing the Database Server Name and Database Name The syntax of the statement is relatively simple. In this case we are looking at the Sql.Database data function, the syntax is simple and looks like this -: Source = Sql.Database( "Name of Database Server", "Name Of Database") This is the text shown in Figure 3 let Source = Sql.Database("localhost", "DBdata"), dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data] in dbo_DimDate
To change the data source to point at the Azure database the only changes that have to made are as follows
let Source = Sql.Database("AzureDatabase", "DBdata"), dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data] in dbo_DimDate Job done. This was nice until you have to do it several times, and personally I got bored then wondered if there was a better way? I am glad you asked if there is a better way let me show you. Having seen some examples where the data source was an excel spreadsheet l was aware that it was possible to supply parameters to change the data source. From one spreadsheet to another one. The first step is to set up the parameters. Open the Query Editor window, click on the 'New Sources' button from the list click on 'Blank Query' (see Figure 4)
Having set up the DatabaseServer dataset (parameter value), repeat the process and create a DatabaseName dataset (parameter value). Figure 6 shows what you query editor window would look like if you entered the same details shown in this example.
* DatabaseServer - containing the value 'AzureDatabase' or the name of the database server you are going to connect to. * DatabaseName - containing the value 'DBData' or the name of the database you are going to connect to.
If we look at the advanced properties of the dataset 'DimDate' the data source would look like the one shown below. let Source = Sql.Database("AzureDatabase", "DBdata"), dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data] in dbo_DimDate Next step is to replace the database server name and database name with the names of the Datasets that have just been set up. So the data source will look like the one below. let Source = Sql.Database(DatabaseServer, DatabaseName), dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data] in dbo_DimDate
The advanced query window should look like the one show in figure 7 below. When the data source is queried the values of 'DatabaseServer' and 'DatabaseName' are replaced by the values from the relevant dataset. In this PowerBI desktop report l just replaced all the hard coded instances of the database server name and database name with the relevant parameter. Which allows me to switch between two databases with the ease l wanted, only with PowerBI desktop :-( Figure 7 - Showing the final M (Power Query Formula Language) text (data source) If you click on this link you can download a copy of a .PBIX with the two dataset's shown above. There is more you can do with this you will need to wait till part two of this blog post for more details.
In the previous post (see here) l set up the blank datasets as parameters storing the values of the database server and name. Whilst attempting to create a suitable dataset to a report for an internal customer, l was frustrated that l could not do it using Power Query. Yet on the other hand l know that l could write some TSQL that would give me the dataset l wanted very easily. So l wondered if this was possible to combine the two? So lets return to the SQL.Database function which has this very nice example Sql.Database("localhost", "Northwind", [Query= select * from Customers]) The last setting is "Query as text - Provide a query text that is run on the server to return values". It's almost like someone thought it would be a good idea to allow a user to run a SQL query. A few minutes later l had the query l wanted to run and it looked something like this -:
DECLARE @IssueType AS VARCHAR(10) = 'Problem'; DECLARE @ClientName AS NVARCHAR(100) = 'RainbowPainters'; SELECT DATEPART(MONTH, [Issue_Created]) AS Month_Number ,DATENAME(MONTH, [Issue_Created]) AS Month_Name ,DATENAME(YEAR, [Issue_Created]) AS Year_Name ,COUNT(DATEPART(MONTH, [Issue_Created])) AS Total FROM [dbo].[IssueData] AS ID WHERE [Issue_type_name] = @IssueType AND [ClientName] = @ClientName GROUP BY DATEPART(MONTH, [Issue_Created]) ,DATENAME(MONTH, [Issue_Created]) ,DATENAME(YEAR, [Issue_Created]); Next when back to the dataset opened the 'Advanced Editor' and added the query, you can it in Figure 1 below Figure 1 - Advanced Editor window with the new query in place
To make it a bit easier to read below is the actual text from the 'Advanced Editor' window above. All that has been added is the text in red,and the query text which is in italics
letSource = Sql.Database(DatabaseServer, DatabaseName ,[Query = "DECLARE @IssueType AS VARCHAR(10) = 'Problem'; DECLARE @ClientName AS NVARCHAR(100) = 'RainbowPainters'; SELECT DATEPART(MONTH, [Issue_Created]) AS Month_Number ,DATENAME(MONTH, [Issue_Created]) AS Month_Name ,DATENAME(YEAR, [Issue_Created]) AS Year_Name ,COUNT(DATEPART(MONTH, [Issue_Created])) AS Total FROM [dbo].[IssueData] AS ID WHERE [Issue_type_name] = @IssueType AND [ClientName] = @ClientName GROUP BY DATEPART(MONTH, [Issue_Created]) ,DATENAME(MONTH, [Issue_Created]) ,DATENAME(YEAR, [Issue_Created]);" ]) in Source
When l refreshed the dataset, the data appeared :-) Job done? No, as often happens one client gets something and then everybody wants why they have. So l was asked if we could create the same set of reports for another customer. There was two choices at this point, l could just change the hard code client name, or could l parameterise the client name. Having heard this before l knew about passing parameters from excel to filter datasets see the following posts. https://blog.oraylis.de/2013/05/using-dynamic-parameter-values-in-power-query-queries/ http://blog.crossjoin.co.uk/2015/11/24/using-parameter-tables-to-control-data-refresh-in-power-bi/ Having looked at the blog posts which declare and then used parameters this looked like a simple concept. Kudos to the Power BI team for that. First step was to add additional dataset as a parameter. So l now had three dataset as parameters, the latest one being 'ClientName', which stores the client name
Figure 2 - List of datasets containing parameters So having looked at the blog posts above l realised that l just need to declare a query which would hold the query text and pass in the Client Name parameter to the query text. Following some trial and error, l ended up with the query text below let pQuery = "DECLARE @IssueType AS VARCHAR(10) = 'Problem'; DECLARE @ClientName AS NVARCHAR(100) = '" & ClientName & "'; SELECT DATEPART(MONTH, [Issue_Created]) AS Month_Number ,DATENAME(MONTH, [Issue_Created]) AS Month_Name ,DATENAME(YEAR, [Issue_Created]) AS Year_Name ,COUNT(DATEPART(MONTH, [Issue_Created])) AS Total FROM [dbo].[IssueData] AS ID WHERE [Issue_type_name] = @IssueType AND [ClientName] = @ClientName GROUP BY DATEPART(MONTH, [Issue_Created]) ,DATENAME(MONTH, [Issue_Created]) ,DATENAME(YEAR, [Issue_Created]);", Source = Sql.Database(DatabaseServer, DatabaseName,[Query=pQuery]) in Source
Figure 3 - showing the query text in the Advanced Editor Window Much to my delight this syntax works as expected. So l can now have a way that it is possible to specific parameters which can be passed to SQL server. From reading about M (Power Query Formula Language) there's more to learn. For the moment l am happy with the new found knowledge, whilst l play with some of the other technologies offered by PowerBI. The only downside is this only works in PowerBI Desktop :-(
We all communicate with each other, some more than most, in our house if my partner is not talking to me there is something wrong. It's not usual for the misunderstanding to be something I might have said (or done). Communicating with each other verbally is something that we learn to do from an early age. We learn what words mean, their power, what they can do for us, what to say, and what not to say.
There have been times I have had the pleasure of going to the local garage and speaking to the mechanic regarding whatever challenge is with the car. The mechanic would explain the issue to me, using words which I have to say that wished I understood. It is entirely possible that there is a "big end" in our car just do not ask me where it is or what it does. Or that the timing belt is very important to make the engine run properly.
Every industry, profession, hobby, has their own language. This often makes it easy for professionals to communicate with each other often in a form of shorthand which can sound foreign to someone else even if they speak the same language. Working in the IT industry this is something I am very aware of. If someone asks me what I do for a job what do I say? I might say that I am a BI professional, working primarily with the MS SQL server stack, sometimes using SSIS, and SSRS. I write a variety of CRUD scripts in TSQL and I do some query optimisation. If the person asking is not an IT professional who works in my specific area of expertise, most of my explanation would have sounded like I had spoken in a different language.
What I now say is my job involves three things, data shepherding, data grooming, and data dressage. I might expand a little on these to explain that l move data from one place to another ensuring none of the data gets lost as we move it. Some of the data might need to be polished or groomed to fit in its new home. Then I train data to perform and dance in a way that others can understand it better. What I try to do is use words which people who are do not work with databases can understand and relate to.
When I speak to clients one thing I try to remember is to use words that anyone can understand. If I introduce technical concepts or acronyms in the conversation I will try to make time to explain them. Or use analogies that are simple and easy to understand. This is not an easy thing to do, it is our job to make IT simple and easy to use. There might be lots of complicated moving parts behind the scenes. Like a car, we have a simple dashboard, underneath are lots of complicated moving parts that just work. The hard work of maintaining and fixing those parts I happily leave to the experts.
Personally, I see our job is to make our customers task as simple as it can be. We should present challenges, technical details in a language our customers can easily understand. As Einstein is quoted as saying “make everything as simple as possible but no simpler”. One excellent example of this is by Brent Ozar when explaining implications of RTO & RPO here -> https://www.brentozar.com/archive/2014/05/new-high-availability-planning-worksheet/. The worksheet sets out the terms in language everyone can understand, even better by drawing attention to the targets, so everyone knows what to expect. Is this easy, or simple? No, it is not. Having said that which expert do you feel most comfortable with? would happily go back to time and again? The one that speaks to you in words and terms you can easily understand. Or one that uses language and words that are sometimes not easy to follow or understand? The choice for me is easy and simple, which is why I work that little bit hard to make it as easy as possible for my customers to understand me.
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
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.
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.