BI-NSIGHT – Power BI (Mobile App Update, Prevedere Content Pack, Visuals, Where is my Data Stored) Excel – (Power Query Updates)

Here are this week’s updates, which there are quite a few and I have no doubt that there might be some more updates released tomorrow or in the coming days.

Power BI – Mobile App Update

There have been quite a few updates with regards to the Mobile App in for Power BI and Reporting Services.

It is great to see that they are starting to leverage the capabilities of the phone and use this within the app. As they have done with the Geographic filtering which can filter your reports and dashboards based on your location.

Another great example of this, is the use of the QR and creating augmented reality as is shown with the picture above. This is a great way to integrate things which puts a new perspective on what is capable.

Also great to see that first comes out in the iOS app, which then filters down to the Android App and Windows App, is the functionality for improved slicers on the small mobile screen, notifications and bug fixes.

You can find all the details here: Power BI Mobile Apps Update – June 2016

Power BI – Prevedere Content Pack

It has been quite a while since there has been a new content pack and it is great to see that is one around predictive analytics. And for customers who use Prevedere it will be a welcome addition.

You can find more details here: Visualize your Prevedere data with Power BI

Power BI – Visual – Pulse Chart

It is great to see such an awesome visual that will allow you to pause and then be able to see the impact. I feel that this will really engage the audience and grab their attention.

You can read about it here: Visual Awesomeness Unlocked: Pulse Chart

Power BI – Visual – Narratives Update

This is another visual which has got a great update. I have not used it in the past, but it is great to see that they are working on this, as well as making improvements to use additional visuals.

You can find out about it here: New features available for Narratives for Power BI

Power BI – Where is my Data stored

From the picture above there is a very quick and easy way to see where your data is stored.

Click on Help and Support and then About Power Bi as shown below

You can also find the blog post here: Power BI – Which data center hosts my data? Where is my data stored?

Excel – Power Query Updates

As you can see above some of the changes that have already been implemented in Power BI are making their way into Excel with Power Query. I have to say that the other day I was doing the Conditional Column in Power BI by using the “Add Column” and once I was finished figuring it out, I then had to make another change. And when I clicked on the step it brought the image as shown above. It was so much easier and quicker to get it done.

I also like the ability to be able to change the order around within the GUI. As in the past this sometimes was a challenge but now it is a simple drag and drop.

As well as now it shows you in the Power Query window what the data type of the column is, which makes it very quick to understand what you are working with.

You can find all these details and more here: June 2016 updates for Get & Transform in Excel 2016 and the Power Query add-in

SSIS – Creating a process which will check a condition, if it is not ready wait and try again.

So we had a requirement where we wanted to create a waiting for loop, so that if it was not ready it would wait, try again, and if still not ready wait and try again until it got to the Max Number of tries. We also did not want it to error whilst waiting.

Example:

  • Check to see if the database is online.
  • Wait for 1 minute to retry.
  • Try 10 times before failing
  1. The first that we need to do, is to create some Variables which will help with the process.
  2. From the above this is what they are for:
    1. Max_Count – This is how many times you want to retry
    2. State – This is our check to see if the databases are online or not.
    3. SuccessfulRun – This is used to see if it ran successfully
    4. Value_Counter – This is used as the counter.
  3. Then below are the steps.
    1. The first thing is to assign a value to our Max_Count Variable.
    2. This is done using an Execute SQL Task and assigned it with the following below:
      1. As you can see above in the SQLStatement we have set our Max_Count to 10
      2. Then in the ResultSet we have mapped our results to our Variable
    3. Next is where we configured our For Loop Container with the details shown below:
      1. As you can see above we have assigned it with our Value_Counter Variable.
      2. You will note that in the EvalExpression we have got our Max_Count variable as well as the SuccessfulRun
        not
        equal to zero. This will be explained later how this value is derived.
        1. NOTE: This is also required so that it will exit when it is actually
          successful.
    4. Next in our process within the For Loop is where we start with our Waiting Period as shown below using the Execute SQL Task
      1. All that we are doing here is using a TSQL
        statement to wait for 1 minute.
      2. NOTE: The reason we do this at the start, is so that if it has to loop through, it will wait on the second
        loop before checking.
    5. Next is where we have our condition (which based on our example is to check if the database is online) as shown below:
      1. What our SQLStatement is doing above is checking to see the State of our database.
        1. And if it is online it will have a state of equal to zero
      2. Then in the ResultSet we have mapped our results to our Variable
    6. Next is where we have our Script Task which we use to populate the SuccessfulRun variable as shown below:
      1. We went into Edit the script with the following below:
      2. As you can see above we also assigned the same variable
        SuccessfulRun to the ReadWriteVariables
      3. NOTE: We did use the Microsoft Visual C# 2012 as the script
        language.
      4. From the above all that we added was to give the Variable SuccessfulRun = 1
    7. And then we set our database
      back into Multi User Mode after it was successfully back online as shown below:
    8. The final thing that we had to do, which actually ensure that it loops through without
      erroring is to put in a precedence
      constraint
      between the Check if Database is Online and Check to see if Successful as shown below:
      1. We then configured it with the following for the precedence
        constraint as shown below:
  4. So once this is all done it looks like the following:
  5. You can now run this, potentially
    changing your condition (Check if Database is Online) that you want to check for. As well as you can also change how long you want to wait for before
    checking
    again.

BI-NSIGHT – Power BI (Notifications, Help Improve Power BI) – Excel – (Power Query Update)

Another week and some more updates to the world of BI

Power BI – Notifications

It is great to see that we now have got some notifications in a similar way in which we have had in the Mobile App.

As often I am sure that I have shared dashboards with people and they either are not aware or missed the email. Now they can easily see it when they log into the Power BI Service.

Also at the bottom of the blog post it is great to see that they will be enabling real-time data alerts as you can currently do within the Mobile App. As this is something that will be very handy for people to ensure that if there is a specific movement that they are made aware of this.

You can find the details here: Power BI Notification Center

Power BI – Help Improve it

There is a blog post to take a survey to help and improve Power BI.

Click on this link below to see the post and take the survey. As well as a chance to win a $50 gift card from Amazon.

Help Improve Power BI Desktop

Excel – Power Query Update

There appears to be an update for Power Query, which is great to see it going back into Excel, as I am sure that a lot of these changes have already been implemented in Power BI

You can find the download here: Microsoft Power Query for Excel

Automating running PowerShell script which required Administrator elevation via SSIS

What we are going to explain below is the following via an example.

Example – We are required to run a PowerShell script which will run with elevated privileges.

So in order to complete this we have to complete the following below but use a simple PowerShell script which will get all the FileNames from a Folder.

  • Create a BAT file which can execute the PowerShell script.
  • Create a Scheduled Task which in turn will call the BAT file.
  • Create an SSIS Package which will call the Scheduled Task

NOTE: The reason that we did this above, was to ensure that we could automate the snapshotting of the database using SSIS. This would enable us to get a snapshot of the data on a required schedule.

Changing the UAC

Below is a quick step to disable the UAC, as I think that this might affect the later steps when the BAT file is trying to run the PowerShell script.

NOTE: You will need to be an Administrator on the Server to turn this off.

  1. Go into the Control Panel.
  2. Then search for UAC.
  3. Then click on the Change User Account Control settings as shown below:
  4. Change it to Never Notify as shown below:
  5. Then click
    Ok
  6. It will ask you one last time if you want to make the change to the Computer.
    1. Click on Yes

Creating the BAT File which can execute the PowerShell script

In the steps below we are going to explain how we create the BAT file which executes the PowerShell script. And also show that at first we can run the PowerShell script manually to ensure that it works.

Testing the PowerShell Script

  1. The first thing to test is to ensure that the PowerShell script actually runs and completes as it should.
  2. Click on Start and then type in PowerShell and from the search results right click on Windows PowerShell ISE and select Run as Administrator
  3. Now when the PowerShell ISE opens you can see from the Window at the top that you are running as Administrator
  4. If you want to follow our example you can then copy and paste the following PowerShell script

    Get-ChildItem
    -Path
    ‘C:\Program Files\Microsoft SQL Server\MSSQL11.PRODBI\MSSQL’
    -Recurse
    |
    Export-Csv
    -Path
    ‘C:\Program Files\Program Files.csv’
    -Encoding
    ascii
    -NoTypeInformation

    1. What we are doing above is to get the File Names for all the files and folders under Program Files and then our SQL Server MSSQL Folder.
    2. And then export it to a CSV.
    3. As with our example once run you should see the CSV file as shown below:
  5. Now we know that our script runs we can now save it to a location on our Server.
  6. As with our example we saved it to a new folder on the C:Drive
  7. We will now use this in the next steps to create the BAT File.

Creating the BAT file which will run the PowerShell script.

In the steps below we will be creating the BAT file which will run the PowerShell script.

  1. Open up Notepad or Notepad++ and copy in the following, which will be explained below:
    1. c:\windows\system32\WindowsPowerShell\v1.0\powershell.exe
      -ExecutionPolicy ByPass -command
      “. ‘C:\XIOSnapshot\Export-MSSQL-Files.ps1′”
    2. What we are doing above is to first put in the full location of PowerShell highlighted in GREEN
    3. Next we are putting in some parameters for PowerShell, which is to bypass the execution policy and the command that is to be run highlighted in PURPLE
    4. And then the final part is where we are calling the PowerShell script which we created above. It is highlighted in RED
      1. NOTE: In order to run the command, we have to put it all in double quotes, as well as it has to have a period in order for it to be executed.
    5. Now we can save this BAT File in the same location with a name.
    6. As per our example we saved it with the following:
  2. Now what we will need to do is to run the BAT file from the command line to test that it all works.
  3. You can now open command prompt and run the bat file as we have shown with our example below:
    1. NOTE: You can see that we are not logged into the Command prompt as Administrator.
    2. We can also see that it completed successfully.
    3. And finally if we look on the file system we can see the file got created.
  4. One final thing to note, is that this will run when we execute the BAT file as we have administrator privileges.

Creating the Scheduled Task which will call the BAT File

Now we are going to create the Scheduled Task which will call the BAT File.

  1. Go into Server Manager, then click on Tools and select Task Scheduler
  2. Once this opens on the right hand side click on Create Task…
  3. The first thing that you have to give your task is a name
    1. As with our example we are going to give it the name as shown below:
    2. Now a VERY important step
      is under Security options make sure to select the Tick box as shown below:
      1. NOTE: With this it means that it will run with the highest privileges and will run as Administrator context
    3. Next if you are going to run this via SQL Server Agent, you are going to have to put in the same domain account
      context that SQL Server Agent Runs Under.
      1. NOTE: When you click ok for the creation of the Scheduled Task it will prompt for the password.
  4. You can leave Triggers blank, because we are going to execute this via SSIS.
  5. Click on Actions
    1. Then click New
    2. Then configure the New Action as shown below, with an explanation afterwards.
      1. Here we are just running our BAT File.
    3. Click Ok.
  6. You can now click Ok for this Scheduled Task to be created.
  7. NOTE: When you click ok for the creation of the Scheduled Task it will prompt for the password.
  8. The final step is to then test the scheduled task by running it within Task Scheduler.
    1. Click on Run
    2. You will briefly get the command prompt Window popping up, which you will not see when running via SSIS.
    3. To confirm this you can view the Last Run Result in Task Scheduler as shown below:
    4. As well as the file on the file system:
  9. Now we are done with the Task Scheduler, calling the BAT file, which is then calling the PowerShell script.

Automating the running of the Scheduled Task in SSIS

In the steps below we are going to show how to automate the running of the Scheduled Task within SSIS

  1. Either create a new SSIS Project or create a new SSIS Package.
  2. Then drag in the Execute Process Task into the Control Flow.
  3. Double click to go into the Properties.
    1. We gave it the following name:
    2. Then click on Process to put in the details to call the Scheduled Task
    3. Now what you will see below is the details to run the Scheduled Task which will be explained afterwards:
      1. From the above under
        Executable, this is where the Scheduled Task exe is saved on our Windows
        Server 2012.
      2. Then under Arguments is where we specified for the Scheduled Task to firstly run (/run) and then the Task Name (/TN) to run
      3. Then click
        Ok.
    4. You should then see the following in your SSIS Package:
  4. Now to test that it all works, execute your SSIS
    Package.
    1. It should then run successfully and you should see the following:
    2. NOTE: That the SSIS will come back immediately saying that was successful if the command is correct.
    3. So if you have something else that is reliant on this step I would suggest putting in something to delay it going onto the next step.
      1. I did this by using the WAITFOR DELAY in SQL Server.
    4. Now we can see the file being updated as shown below:
  5. The final step is to then deploy this to your SSIS
    Server and create the scheduled task and test it.
  6. NOTE: If you have configured the Scheduled
    Task with the same
    domain
    account as your SQL Server Agent
    account it will succeed.
    1. NOTE: This domain account should not have a password that will change.

BI-NSIGHT – Power BI (Smarter Auto Generated Insights, OkViz) – SQL Server 2016 (SSDT GA) – Microsoft Flow (Updates)

Not a whole host of updates this week, but still some great things to look into.

Power BI – Smarter Auto Generated Insights

Another great service update in Power BI, with regards to the Quick Insights. You now have the ability to put in complex filters and then generate insights based on your complex filters.

This can enable you to quickly and better understand your data and what it is telling you.

You can find more details here: Smarter Auto-Generated Insights with Complex Filters

Power BI – OkViz

As you can see from above the guys at SQLBI have been really busy and have developed some great additional visuals to complement their Synoptic Panel.

As you can see that have got the Smart Filter, Bullet Chart, Card with States and Candlestick, which are all great additions to have in your visual gallery.

You can find all the information here: OkViz

SQL Server 2016 – SSDT Generally Available

It is great to see now that you can get the total BI development experience with now also having SSDT for SQL Server 2016.

It details all the updates and enhancements that we have been blogging about, as well all the new features in SQL Server 2016

You can find all the details and how to download here: SQL Server Data Tools GA update for June 2016

Microsoft Flow – Updates

It is great to see that Microsoft Flow is also getting better and better with new additional updates to the flows, as well as connectors as you can see with the picture above.

I now see that they have also enabled Custom APIs which is great as often you have something that has been developed for your specific requirement, and this gives you the capability to integrate that with other connectors.

You can find more information here: May updates to Microsoft Flow

BI-NSIGHT – Power BI (Desktop Update – May, New Visuals, Gateway Enterprise Update, Azure ML Predictions, Planned Work started) – SQL Server 2016 (Generally Available, Mobile Report Publisher,SSMS

Here are the weekly BI Updates, with a lot of great things within Power BI as well as the major release for SQL Server 2016

Power BI – Desktop Update for May


Well this week there is a whole stack of updates to the Power BI Desktop which once again is great to see so many updates and actual user requests being added to the product.

As I have done in the past I am going to highlight the ones that I feel need to be highlighted below.

First off are the Report View updates, and by far the most requested option as is shown in the picture above is the conditional formatting. This is great to see and will help for people to quickly identify with the conditional formatting.

Next is also the customizable tooltips because at some points there is either not enough information or not what you want to show to the user. And now you can complete this.

The scrolling option to load more data in charts is great when it is required. Because at times the way the charts work, is you want to see more details but it was not possible until now.

Then under the Analytics section the option to do a Quick Calc for % of grand total. This is great as often this makes it easier to compare values. I have no doubt that as time progresses there will be more options added to Quick Calcs.

Then there is a whole stack of goodness under the Data Connectivity, starting with some new connectors which is always great to see and very welcome for getting in additional data.

Another great addition is the ability to do Inline Input controls for Functions. This makes it a lot easier to see, understand and use the functions that are created.

One of the biggest or best updates are to the Query Parameters where you can now convert a query to a parameter and vice versa. What this means is that now you are able to create a dynamically loading parameter, which can then be passed to another dataset or query. And I think (even thought I have not currently tested) that you are able to then pass it to another parameter. Also the ability to do URL parameterization or multi-part URL’s means that you can simply insert into your URL the dynamic piece or parameter.

Also now the ability to be able to Save As a Power BI Template so that this can be shared with other people is great.

The support for reordering Query steps by using the drag and drop is another really handy feature because in the past I used to have to delete the steps and then re-create them. So this makes things a lot easier when wanting to shape your data.

Using a Date Picker always makes things a lot easier and simple when working with data. And now you can use this when shaping your data.

And finally a new context menu to create new queries from the query pane.

You can find all the details here: Power BI Desktop May Update Feature Summary

Power BI – New Visuals

As you can see above there are a whole host of new visuals for Power BI, it has been quite for some time, but it is great to see them there.

You can find all the visuals here: Power BI Custom Visuals

Power BI – Gateway Enterprise Update for May

It is great to see that they are also continuing to update the Power BI Gateway for Enterprise with additional data sources and UPN Mapping and CustomData property.

You can find the details here: Power BI gateways – May Update

Power BI – Using Azure ML and R for analysis

Below are the quite complex details in which you can leverage both R and Azure ML in order to get data into Power BI, analyze the data and then view it in the Power BI Service.

Here is the blog post: Power BI & Azure ML Better Together

Power BI – Planned Work Started

We can see that they have started working on the R visuals within the Power BI Service which is great to see.

SQL Server 2016 – Generally Available

Yesterday or last night if you live in Australia, they released SQL Server 2016.

I have blogged previously with regards to all the enhancements as well as new features that they have brought into SQL Server 2016. And a lot of great things that we can look forward to within the BI space.

I have already started a download to test and then look to roll this out to production in the near future. There are too many wonderful things which can enable our business to not get this up and running as soon as possible.

You can find all the release information here: SQL Server 2016 is generally available today

SQL Server 2016 – Mobile Report Publisher

Please find the link below for the General Availability for the SQL Server Mobile Report Publisher

Microsoft SQL Server Mobile Report Publisher

SQL Server 2016 – SQL Server Management Studio Release

It is great to see that there is now also a generally available SSMS for SQL Server 2016, with a few bug fixes and other wonderful updates.

You can find all the information here: Announcing SQL Server Management Studio – June 2016 Release