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

let

Source = 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 :-( 

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)

Figure 4 - Creating a new blank query

Since this one caught me out and a few other people it seemed like a good idea to blog about this.

Sometime ago the Azure portal got a make over, which l have say make the portal look very pretty.  Whilst setting up a proof of concept for PowerBI l thought it was also a good time to look at setting up an Azure SQL database.  The last time l did this was in the previous portal.  So l duly set up the database, one of the last steps was to allow the IP address from my work organisation access to the database.  So l goggled and found this page - https://azure.microsoft.com/en-gb/documentation/articles/sql-database-configure-firewall-settings/

The Wrong Way
Open the new style Azure Portal 
Click on browse (1)
From the list of blades click once on SQL databases (Figure 1)

Figure 1 - Opening the SQL databases blade

Click on the 'SQL databases' blade this will open a list of the all the SQL databases that are in your account (Figure 2)


Figure 2 - List of SQL databases showing area where to click to show settings

Clicking on the ellipsis will cause a new menu window to appear (Figure 3)


Figure 3 - Clicking on the ellipsis to get the settings link

Click once on the settings link this will then open the settings window (Figure 4)

Figure 4 - Settings for the database 'JiraData'

As can be seen in Figure 4 there is no firewall settings.

The Right Way

Open the new style Azure Portal 
Click on browse (1)
From the list of blades click once on SQL servers (Figure 5)


Figure 5 - Opening the SQL servers blade

Click on the SQL Servers blade and this will open a list of all the SQL servers in your account (Figure 6)


Figure 6 - List of SQL servers

Move the mouse over the row which is the SQL server that require to see the settings for.  Click once with left hand mouse button the selected SQL server in Figure 6 this is SQL server 'kjsgbci03i'.  This will then open Settings window on the far right hand side (see Figure 7)


Figure 7 - Settings page for SQL server

Click once on the firewall row and this will open the 'Firewall settings' window (see Figure 8)


Figure 8 - Firewall settings window

Final Note

Splitting the settings for SQL servers and SQL databases, does make sense, the same logic is used within SSMS.  That said in the old style portal, the link to the page for setting allowed IP Addresses is under 'SQL databases' (see Figure 9).  Hence why l must have looked at SQL databases blade first.  At least that's my excuse and l am sticking to it ;->


Figure 9 - SQL server page with link to Manage allowed IP addresses (Firewall rules) in the old style Auzure portal.