BI-NSIGHT – SQL Server 2016 – Power BI Updates – Microsoft Azure Stack

Well I have to admit that it seems that the Microsoft machine has been working flat out to get out new products and updates.

If my memory serves me, this is the third week in a row that Microsoft has released new products and updates. I am really enjoying it! But hopefully this will slow down, so that we can catch our breath and actually play with some of the new products and features.

So let’s get into it. There is quite a lot to go over!!

SQL Server 2016

So with Microsoft Ignite happening this week, the wonderful guys from Microsoft have started to announce what we can expect to be in the next version of SQL Server.

I am going to focus mainly on the BI (Business Intelligence) features, and there are quite a few! Some of what I am detailing below I have only seen pictures on Twitter, or I have read about it. As well as the preview not even being released yet, I am sure that there will be some changes down the line.

SQL Server Reporting Services

It finally appears that Microsoft has been listening to our cries and requests for updates in SSRS (SQL Server Reporting Services)!

Built-in R Analytics

I think that this is really amazing, even though I am not a data scientist, I think it is a smart move my Microsoft to include this. What this means in my mind is that you can now get the data scientists to interact and test their R scripts against the data as it sits in the transactional environment. And from there, this could then be used to create amazing possibilities within SSRS.

Power Query included in SSRS

From what I have seen people tweeting about as well as what I have read, it would appear that Power Query will be included in SSRS. This is fantastic and will mean now that virtually any data source can be consumed into SSRS.

New Parameter Panel, chart types and design

I am sure we can all agree, that SSRS has required an overhaul for some time. And it seems that finally we are going to get this. It would appear that the parameters panel is going to be updated. I do hope that it will be more interactive and in a way react similar to the way the slicers do in Excel. As well as getting new chart types. Here again I am going to assume that it will be similar to what we have in Power BI!

And finally there was also mention that the reports will be rendered quicker, as well as having a better design. I also am hoping that they will deploy this using HTML 5, so that it can then be viewed natively on any device. It is going to be interesting to see what Microsoft will incorporate from their acquisition of Datazen.

SQL Server Engine

Built-in PolyBase

Once again, this is an amazing feature which I think has boundless potential. By putting this into the SQL Server Engine this means that it is a whole lot simpler to query unstructured data. Using TSQL to query this data means that for a lot of people who have invested time and effort into SQL Server, now can leverage this using PolyBase. And along with this, you do not have to extract the data from Hadoop or another format, into a table to query it. You can query it directly and then insert the rows into a table. Which means development time is that much quicker.

Real-time Operational Analytics & In-Memory OLTP

Once again the guys at Microsoft have been able to leverage off their existing findings with regards to In Memory OLTP and the column store index. And they have mentioned in their testing that this amounts to 30x improvement with In-memory OLTP as well as up to 100x for In-Memory Column store. This is really amazing and makes everything run that much quicker.

On a side note, I did read this article today with regards to SAP: 85% of SAP licensees uncommitted to new cloud-based S/4HANA

I do find this very interesting if you read the article. What it mentions is that firstly 85% of current SAP customers will not likely deploy to the new S/4HAHA cloud platform. Which in itself does not tend well for SAP.

But what I found very interesting is that to make the change would require companies to almost start again for this implementation. In any business where time is money, this is a significant investment.

When I compare this to what Microsoft has done with the In-Memory tables and column store indexes, where they can be used interchangeably, as well as there is some additional work required. On the whole it is quick and easy to make the changes. Then you couple this with what Microsoft has been doing with Microsoft Azure and it makes it so easy to make the smart choice!

SSAS (SQL Server Analysis Services)

I am happy to say that at least SSAS is getting some attention to! There were not a lot of details but what I did read is that SSAS will be getting an upgrade in Performance usability and scalability.

I am also hoping that there will be some additional functionality in both SSAS OLAP and Tabular.

SSIS (SQL Server Integration Services)

Within SSIS, there are also some new features, namely they are also going to be integrating Power Query into SSIS. This is once again wonderful news, as it means now that SSIS can also get data from virtually any source!

Power BI

Once again the guys within the Power BI team have been really busy and below is what I have seen and read about in terms of what has been happening within Power BI

Office 365 Content Pack

I would say that there are a lot of businesses that are using Office 365 in some form or other. So it makes perfect sense for Microsoft to release a content pack for Office 365 Administration

As you can see from the screenshot below, it gives a quick overview on the dashboard to see what activity is happening. As well as details of other services. I am sure that this will make a quick overview of your Office 365 systems really easy to see. And also if there are any potential issues, this could also be highlighted!

Visual Studio Online Content Pack

Another content pack that is about to be released is for people who use Visual Studio Online, I personally do not currently use this. But it does look great for people to once again have a great overview of what is going on.

You can read more about it here: Gain understanding and insights into projects in Visual Studio Online with Power BI

And as you can see below, what you can view once you have got it setup within Power BI.

Power BI planned updates

Below are the updates that I had previously voted for in Power BI. It is great to see that the Microsoft team is actively listening to their customers and implementing some of the idea’s. I have to say that I do not think that there are many other software companies that are doing this currently. And also being able to roll it out as quickly as Microsoft is.

Set Colors and Conditional Formatting in visuals

  • This is great as it will allow the report authors to have more control in terms of how their reports look.

Undo / Redo button in browser & designer

  • This might seem like a small update, but I know personally from working with the Power BI reports, that sometimes you just want to see what a different report looks like. Or adding another element. And with the Undo / Redo buttons, it just saves that little bit of time, as well as to make the report authoring experience that much more enjoyable.

Power BI Announcements from Microsoft Ignite

Below are some announcements that I have read up about either on Twitter or one of the blogs that I follow. It is really great to see so many things in the pipeline.

This means that there is a lot to look forward to, as well as ensuring that we have new and wonderful things to show.

I got this picture via Twitter, which someone must have taken at the Microsoft Ignite Conference. As you can see it is not very clear, but it does show the next update in the Power BI Designer.

You can also see the undo and redo buttons.

It also appears that in the Power BI service, there will be support for SSRS files, namely the .rdl files! Here is another picture taken from Microsoft Ignite.

 

Then there is the Many to Many relationships and bi directional cross filtering will be supported in SQL Server 2016 tabular models, which I am sure will also be included in the new Power BI backend. As this is where it stored all the data.

 

For Hybrid BI, there will be support for live querying for SSAS, currently this is already in place for SSAS Tabular.

 

It also looks like there will be a scheduled refresh for SQL Server Databases as a source. Which is great for people who either do not have either of the SSAS cubes, or want to get some of their data into Power BI.

Microsoft Azure Stack

While this is not exactly BI, it is related to BI, in that with Azure Stack you can get the Azure functionality on your own hardware which is fantastic. And I am sure for a lot of businesses this will be welcomed.

You can read more about it here: Microsoft Brings the Next Generation of Hybrid Cloud – Azure to Your Datacenter

 

SSIS – Configuring SSIS Package to run job after data loading has completed

  • In our example below, we are going to be using a SharePoint 2013 Data Alert job which was created in SQL Server Agent, and then insert this into an existing
    SSIS
    Project.
  • The reason for doing this, is so that every time our SSIS Project runs, as part of the process, it can then fire off a SQL Server Job.
  • Essentially in the past the SQL Server Agent Jobs run
    individually and are not tied into when our data has loaded.
  • By doing it in this manner, it enables the related job to be run after every data load. Which then enables the business to get the relevant data at the relevant times.

     

NOTE: You can modify this for any SQL Server Job.

Find the related SQL Server Agent Job Name

Below details how to find the related SQL Server Agent Job name. As typically when you create a SharePoint Data Alert, or SQL Server Reporting Services (SSRS) Data
Driven Subscription or Email Subscription, it creates the job name with a GUID.

This makes it rather difficult to find the correlating Job Name.

  1. The easiest way to find the SQL Server Agent Job Name, is once you have created your SharePoint Data Alert, or SSRS (Data Driven Subscription or Email Subscription) is to go directly into your SQL Server Agent Job Monitor where the Job was created.
  2. Then open the Job Activity Monitor
  3. Then scroll through the list until you find a job with the following
    properties
    1. The name will be in a GUID format:

    2. The Last Run Outcome is set to Unknown
      1. And the Last Run is set to never

    3. NOTE: If there is more than one job with the above properties, then look to see when you scheduled the start time of your
      job.
      1. If that still does not help you might have to go into the job
        properties and into the schedules to see how often it runs to find the correct job.
  4. If this is in a Test
    environment, you can try and run the job to ensure that you do have the correct job.
  5. Make a Note of the Name of the Job
    1. In our example it was: 68CAE336-3D38-42A6-9526-F32F4D501AA4

 

Modifying the SQL Server Agent Job

Below are the steps to modify the SQL Server Agent job, so that it will not run on a schedule.

NOTE: The reason we are doing this is because we want the job to be run from within our SSIS Project after it has loaded our relevant data.

  1. Go into the properties of your SQL Server Agent Job and click on the Schedules
    Page

  2. Then in the Schedule
    List
    click on the Schedule and select
    Remove.
  3. Once completed there will now not be any schedules for the job.

  4. Then click Ok.

 

Creating and configuring your SSIS Package to run the SQL Server Agent Job for you

Below are the steps to create and configure your SSIS package to run the SQL Server Agent Job as part of the SSIS Project.

  1. Create your new SSIS Package.
  2. Drag in an Execute SQL Task
  3. We renamed our Execute
    SQL
    Task to the following:

    Run SharePoint Data Alerts


  4. Then we went into the properties.
    1. We then put in our Connection to our SQL Server Database, which is the same as the where the SQL Server Agent Job is
    2. Then under the SQL Statement we put in the following:

      EXEC msdb.dbo.sp_start_job N’68CAE336-3D38-42A6-9526-F32F4D501AA4′ ;

      1. NOTE: The thing to note here is that we are using the MSDB
        Stored
        Procedure to start our Job.
        1. And that we are using the Job
          name which we noted in previous steps.
  5. Now that you have completed your Execute SQL Task, it is time to test it.
  6. Right click and select Execute SQL Task.
  7. If successful it should come back looking like the following below:

  8. As well as actually getting the email in your Inbox, as is the case with our SharePoint Data Alert.

For SQL Server 2014 – Use the following link below to use an oData Source to get data from SharePoint List into your table

Below are the steps if you want to use SQL Server 2014 to get data from a SharePoint list into your database.

The URL below explains how to download and install the OData Source for SQL Server

1.       Click on the following link below

a.       http://www.microsoft.com/en-us/download/details.aspx?id=42295

2.       Then once you load the page click on Download

a.       This will then bring up which components you want to download.

b.       Scroll down and select the following:

c.        clip_image001

3.       Then download and install the component.

a.       You can just accept all the defaults in the Wizard.

4.       Then if you now open SSDT, and drag in a new Data Flow Task, then click on the Common Section you should see the following:

a.       clip_image002

5.       NOTE: We would want to use the X86 version for our testing in SSDT, due to SSDT being a x86 version.

6.       NOTE II: You will also have to install the component on your SSIS Server and configure your SSIS package to run in 32bit mode, so that when it is run as part of the job in SSIS it will function correctly.

Configuring your OData Source in SSIS

NOTE: You might have to create your SQL Table, to get the data from your SharePoint List into the table before you complete the steps below. Or you can create the table after step 2 below.

 

1.       Once you have followed the steps above, the one thing to note is how to get the correct URL so that we could create our connection to our Lists

a.       With the example below our URL was:

http://Server/sites/testingdemo

b.       Now in order to get the URL for the lists, we had to put in the following:

                                                               i.      clip_image003

                                                             ii.      NOTE: In your Site, you just need to add the following:

/_vti_bin/Listdata.svc

                                                            iii.      Also ensure the account that you use has access to the SharePoint List.

c.        And then you have your connection completed.

d.       After which you can then select your List from the drop down in your

2.       And then we configured our OData Source with the following:

a.       clip_image004

b.       Under Use collection or resource path, ensure that you select Collection.

c.        Under Collection, click on the drop down, and ensure that you select your SharePoint List that has been created in SharePoint

                                                               i.      In our example it was called: CrimeReportingSubscriptions

d.       You can then click on Preview to see that you are getting the expected data.

e.       Then if you click on Columns you will then see all the SharePoint related columns, as well as the columns from your List

f.         clip_image005

g.       NOTE: Ensure that you do have some data already in your list.

h.       Then click Ok to have your OData Source completed.

3.       Then once completed it will look like the following in your Data Flow