BI-NSIGHT – Power BI Content Pack Adobe Analytics – SharePoint 2016 IT Preview

There is not a lot on the go this past week, but here are the latest updates.

Power BI Content Pack – Adobe Analytics

0525.Dashboard

Another great Power BI Content Pack with regards to Adobe Analytics. It once again gives the users of the Adobe Analytics a great platform to view there data as well as interact with their data.

You can read about it here: Exploring your Adobe Analytics data in Power BI

SharePoint 2016 IT Preview

SharePoint-2016-Preview-tilted (1)

Today I read about the new release of the SharePoint 2016 IT Preview. It does appear that they are integrating quite a bit of what they have learnt and implemented in SharePoint Online into SharePoint 2016.

It is also good to see that they are looking to create a Hybrid scenario, which is similar to what they are doing with SQL Server 2016.

The one thing that I did find out is that they are depreciating support for Excel Services in SharePoint 2016. And I am sure that this will cause some concerns with existing SharePoint 2013 customers. I am not sure if I read it properly but it would appear that this might be a hybrid feature. But this still means that you would need to upload your Excel files to SharePoint Online, as well as finding ways to refresh the data in the Excel files.

You can read about the SharePoint 2016 IT Preview here: Announcing availability of SharePoint Server 2016 IT Preview and cloud hybrid search

And you can find the link here for what has been depreciated from SharePoint 2016: What’s deprecated or removed from SharePoint Server 2016 IT Preview

 

 

 

BI-NSIGHT – Excel 2016 Waterfall Charts – Datazen Updates – Power BI Content Packs (SQL Sentry, Circuit ID) – Power BI Distribution Groups

These past two weeks there has been a massive amount of news and reviews on the updated Power BI Desktop as well as the Power BI Service. I have been reading quite a lot and found some people doing some really amazing things. I honestly feel that Power BI is going from strength to strength.

Excel 2016 – Waterfall Charts

I recently read up about a blog post from Microsoft about the Waterfall chart in Excel 2016.

As you can see from the above picture they have put in a lot of work to get this working. Not only that but also the ability to be able to customize the colors, so that you can see from the above when you have an Income, a Loss / expenditure and Totals. And it does this in a very visual and simple manner.

Meaning that the person viewing the report will quickly be able to have a view and see what money is being spent where from their balance sheet.

The blog post also goes into details explaining how to get to this final chart with an example. I found it really useful and I am sure that I will be using it going forward.

You can read about the Excel 2016 Waterfall chart here: Introducing the Waterfall chart—a deep dive to a more streamlined chart

 

Datazen Updates

Microsoft / Datazen recently updated the Datazen server, which is great because I have to say that I had a similar problem as above when I was evaluating Datazen and using MDX queries. With the new updates it means that the Datazen reports in general will have a much better look and feel due to the fact that you can now customize your Display Names.

The good thing to note is that is not only with SSAS sources but for all other sources also.

Also they have created an application for Windows 7, which I think is really great as I know a lot of people are not yet running Windows 8 or Windows 10 (But that might change a bit with the free upgrade to Windows 10)

I am certain that there are a whole host of other changes that were deployed with the latest Datazen build that are under the hood!

So if you do have an Enterprise version of SQL Server with Software Assurance you can download the latest Datazen build here: Datazen Enterprise Server 3.0.3305

Power BI – Content Pack SQL Sentry

This is yet another great content pack to have at your disposal from Microsoft and Power BI.

I know of a few companies and I am sure that there are many more who all use SQL Sentry on a daily basis. And ideally they would like to get an overview of how their SQL Systems are performing and to potentially highlight SQL Server systems with issues. Likewise, they could also look at SQL Server systems that are performing really well and see if what the differences are?

I also think with the new Power BI changes, you could change the above in terms of colors so that it would highlight potential issues to the report consumer. Just making it easier to highlight problems.

You can read about the SQL Sentry Content pack here: Monitoring your SQL Sentry data with Power BI

Power BI – Content Pack Circuit ID

Another great content pack released is for people and customers who use Circuit ID.

Circuit ID is a cloud communications company and if yourself or your customer uses cloud communication this is a great way to be able to have an overview of how it is being utilized.

You can read about the Circuit ID Content Pack here: Visualize your Circuit ID Data with Power BI

Power BI – Distribution Groups

There has been an update to the Power BI service, which now allows you to share your Dashboards with Distribution groups. As per the blog post, if you have Office 365 email, you will then be able to find your companies email distribution groups.

You can read about the Power BI Distribution Groups here: Easier dashboard sharing with Distribution Groups

SSIS – Using SSIS Variable within a SQL Script with Temp Tables – Error at Data Flow Task : No Column information was returned by the SQL Command

Below details what I was trying to do within SSIS and how I got it to work.

 

Overview

  1. I was using a SQL Script which contained some temp
    tables which was used in the result set.
  2. When I then put this into an OLE DB Source I would get the error shown below:

 

  1. I then tried to see if it would work when I created a SQL Command from Variable.
    1. And this would result in the same error as above.
  2. If I did click on OK, I would then the following in the Columns window as shown below:
  3. So below is a solution which I found to work to get the data from a SQL Query that uses temp tables.

 

Example Data

For our solution below we are going to use the following sample data.

  • The first
    variable that we are going to be using will be for a DayNumber
    • NOTE: This is because we want to go back in time, so we want to go back and loop through the past 10 days.
  • The second
    variable that we are going to be using is our actual SQL Query.
    • So this will be very simply put with the following below:
    • NOTE: This is a very simple
      query and I want to just use this as an example. I am certain in most of your scenarios it will be a much more complex query that is giving you the above error within SSIS.

 

Solution

  1. The first thing that you will need to do, is to create an ADO.NET Source.
  2. Next you will need to ensure that you have two
    variables created
    1. The first
      variable that you want to pass into your SQL
      Statement
    2. The second
      variable is your SQL query.
  3. As with our example above we created the following variables within SSIS
    1. From the above you will see that we have created some additional
      variables as explained below:
      1. DayNumber is the day number variable that we want to pass to our SQL Query.
      2. StartNumber is the starting variable in our For Loop Container
      3. EndNumber is the ending variable in our For Loop Container
      4. Query_GetDayNumber is the query which is going to extract our Day Number and put this into a variable.
      5. Query_ExtractData is the SQL Query where we are going to extract our data including passing the variable.
  4. Next we will create and configure our For Loop Container with the following:
    1. NOTE: This is so that we can then know when to exit our of our For Loop Container
    2. NOTE II: Typically, you will have your StartNumber and EndNumber
      populated by an Execute SQL Task so that it can always be dynamic.
  5. Next we will configure our variable for the Query_GetDayNumber so that it gets populated with the correct details as it loops
    through each time.
    1. Click on Variables, then where we have the Query_GetDayNumber
      click on the Ellipses button on the right
      hand side under
      Expression
    2. This will then open the Expression
      Builder
      Window
    3. Now as with our example, we know that our
      number will always start at 1 and the DayNumber will always be the same as the StartNumber.
      1. NOTE: There will be situations where you will need to run an actual SQL Query against your data to extract the required information.
    4. And we put in the following:
      1. NOTE: The reason for converting it to a string is because our StartNumber
        variable is defined as an Int32.
      2. You can then click on the Evaluate
        Expression to make sure that it is working.
    5. Then click Ok.
  6. Next we will need to configure our variable for the Query_ExtractData, so that we can then pass our above variable within our SQL Query by doing the following:
    1. Click on Variables, then where we have the Query_ ExtractData
      click on the Ellipses button on the right
      hand side under
      Expression
    2. Now we will put in our SQL Query from above, along with the variable as shown below:
    3. NOTE: If you have a look at the above
      syntax you will see that we have put our variable
      DayNumber into our Expression.
      1. You can then click on the Evaluate Expression to make sure that it is working.
    4. Then click
      Ok.
  7. Next create an Execute SQL Task and configure it with the following below so that it will be used to populate our DayNumber
    variable.
    1. Go into the Properties and configure the General
      page with the following below:
    2. NOTE: A few quick things to note on the above:
      1. We have set the Result Set to Single Row
        1. This is to allow our variable to be returned into a result set.
      2. Our SQLSourceType has been set to Variable.
        1. This is so that every time the For Loop Container runs and loops through it will then get the new value.
      3. SourceVariable
        1. This has been selected from the drop down and will be our query which we created earlier.
        2. Which on each execution of the For Loop Container will go and get the values we require.
    3. Then click on the Result Set and put in the following:
    4. Then click
      Ok.
    5. Now drag this into your For Loop Container
  8. Next drag in a Data Flow Task and rename it to Extract Data
  9. Then double
    click and go into your Data Flow Task
  10. Now Drag in an ADO NET Source.
    1. Then ensure that you have not selected the ADO NET Source and have clicked on the sheet within SSIS, and go into the properties.
    2. Next to Expressions
      click on the Ellipses
      Button
    3. Under Property click on the Drop Down and select the following as shown below
    4. Then click on the Ellipses next to Expression.
    5. Now in the Expression Window put in the following variable as shown below:
      1. NOTE: From the above you will see that this is our Variable query that we created and configured in step 6 above.
    6. Click on the Evaluate
      Expression and you will then see the actual query below in the Evaluated value:
      1. NOTE: In the above it has gotten the number 12 from the default
        value in our Variable for DayNumber.
    7. Then click Ok.
    8. Now go back into your ADO NET Source.
      1. Ensure that you have got the correct connection to your data source.
      2. Then under Data access mode
        change this to SQL Command
      3. Then you should see your query populated below as with our example:
      4. NOTE: It got this query
        information from our Expression, which in turn got the information from our variable.
    9. You can then click on Columns to see that the query
      runs and gets the required
      information.
    10. Then click Ok.
  11. Then the final step is to then link it to your destination table.
    1. NOTE: You can use either an OLE DB Destination or an ADO NET Destination.
  12. Next make sure that you drag your Data flow task into your For Loop Container
    1. Then ensure that you place the Success
      Precedence
      Constraint
      between your Execute SQL Task and your Data Flow Task as shown below.
  13. Now you can run your SSIS package and it should work
    successfully.

This post was published to myfriendjoobs at 1:57:59 PM 8/5/2015

SSIS – Using SSIS Variable within a SQL Script with Temp Tables – Error at Data Flow Task : No Column information was returned by the SQL Command