BI-NSIGHT – Power BI (August Power BI Desktop Update, Future Download Report & Snap to Grid, IntelliSense coming to Power Query, Power BI Training, WebTuna Content Pack, Building a Real-Time IoT Dashboard, Chris Webb Loading Data from Multiple Excel Workbooks saved in OneDrive, SCCM Solution Template) – SQL Server (SSAS Integrated Workspace Mode, SSMS Update, Report Builder Update)

Last week there was not a lot on the go, but it seems to have picked up this week, so here are the latest insights.

Power BI – August Power BI Desktop Update

As I am sure some of you would have already seen and heard and I personally have to echo what other people have said, it is great to see how the Power BI team, as well as Microsoft take on board the issues and problems that their users face, and in this instance how quickly they have made the change, so that we can get not only the new functionality. But as requested also have the ability to use the older functionality also.

What I am referring to here is the Drill experience.

So you can use the double down arrow which will do the previous experience which will show the next level of the hierarchy.

And the new experience which is the split arrow which will perform the new inline hierarchy experience.

You can find the details here: August Power BI Desktop Update: Updated Drill experience

Power BI – Future state (Download Report) & Snap to Grid

As you can see with the image below on certain versions of the Power BI Service they have started provisioning the ability to download your Power BI report as a PBIX file. Which will be a great addition.

Next you can see that they have started working on the Snap to Grid functionality which is another great addition which will be available sometime in the near future.

Power BI – IntelliSense coming to Power Query

Just a quick update that IntelliSense is coming to Power Query, I have been working in the nuts and bolts of Power Query and this will be a very welcome addition.

Power BI – Training

There are some great free training available to get into Power BI, as you can see above there is a “Dashboard in an Hour” as well as “Dashboard in a Day”. Not only that but there is also a really great and valuable edX course (which I have already completed) which offers some great content and learning experiences.

You can find the details about the different options here: Power BI Dashboard in a Day and Dashboard in an Hour training near you

Power BI – WebTuna Content Pack

Here is another content pack, this time from WebTuna, which is an on-demand service for alerting and monitoring the performance of your internet or internet sits. And I am sure due to the nature of the business model as well as analytics in terms of usage and where it is coming from this will give some great insights into their customers data.

You can find all the details here: Explore your WebTuna Data with Power BI

Power BI – Building a Real-Time IoT Dashboard

In this blog post from the Microsoft Power BI team they show you how to setup, configure and create a real-time IoT dashboard.

Whilst the concept is very basic (which I think is a good thing to start learning) it also shows how powerful this can actually be.

You can find the details here: Building a Real-time IoT Dashboard with Power BI: A Step-by-Step Tutorial

Power BI – Tracking Changes in PBIX files

This is a blog post from Helen Gore, where they have created something that appears to be very simple, but actually is very powerful.

What she explains in her blog post is how they track changes in their Power BI Desktop (PBIX) files, so that when other people are working on the same file, they know what the previous changes were.

You can find all the details here: HOW TO TRACK CHANGES IN POWER BI DESKTOP

Power BI – Chris Webb – Loading Data from Multiple Excel Workbooks saved in OneDrive

This is an excellent blog post from Chris Webb where he shows how to not only load multiple Excel Workbooks, but ones that are saved on OneDrive Personal or OneDrive for Business, as well as ensuring that they get refreshed on your schedule.

I will not go into all the details, as you can read them on his blog post here: Loading Data From Multiple Excel Workbooks Into Power BI–And Making Sure Data Refresh Works After Publishing

Power BI – SCCM (System Center Configuration Manager) Solution Template

If my memory serves me correctly this is the second Power BI Solution Template from the Microsoft Power BI team.

And this time it is for SCCM, and having worked with this data in the past, it is really great to see how easily it can be used to show insights into your SCCM data. As well as it does appear that you can integrate 3rd party information in SCCM also.

You can find the details here: Announcing the Power BI solution template for System Center Configuration Manager

SQL Server – SSAS (SQL Server Analysis Services) Integrated Workspace Mode

This is a great update for SSDT and the modelling experience when working with SSAS Tabular models. You now can use an integrated workspace, which means you do not have to connect to an SSAS Tabular Server.

The thing to note is that you might need additional memory, as well as they highlight in the blog post drivers for both 32bit and 64bit depending on the version of SSDT.

You can find the details here: Introducing Integrated Workspace Mode for SQL Server Data Tools for Analysis Services Tabular Projects (SSDT Tabular)

SQL Server – SSMS (SQL Server Management Studio) Update

Here is the monthly update for SSMS, and there are quite a few updates in this release.

You can find all the details here: Download SQL Server Management Studio (SSMS)

SQL Server – Report Builder Update

It appears that for SQL Server 2016 the report builder will be following a similar release process as SSMS and SSDT where there will be more frequent updates to report builder.

I am sure that this will enable a lot of future updates and enhancements for report builder.

You can find the details here: SQL Server 2016 Report Builder update now available

BI-NSIGHT – Power BI (Publish in Web App, Enterprise Gateway GA, Desktop Update, Troux Content Pack, New Visuals) – SQL Server 2016 (CTP3.3, eBook, R)

So from last week to almost having nothing to talk about, to this week having a whole host of updates.

Just to quickly mention I attended the first local Power BI User Group meeting in Brisbane this evening ( QLD Power BI User Group ), and it had a really great turnout, along with some great content for the first user group. I have no doubt that it will grow from strength to strength.

So let’s get into the details there is a lot to cover here.

Power BI – Publish in Web App

This has been one of the most requested things that have been voted on. And it is great to see that they have listened again to what the people and users are asking for and have delivered it.

There are a few things to be aware of is that once you embed it into another application all your security is not valid.

Along with this currently due to it being in preview there might be an additional cost to have this capability. Which I can understand in a way because they are actually providing this outside of Power BI, and by making it available for anyone to interact, that means that there are things that are happening within the cloud that needs to be accounted for.

You can find out the details here: Announcing Power BI “publish to web” preview

Power BI – Enterprise Gateway General Availability

This is something that I have covered in the past, and it is great to see that they have incorporated all the features into one product.

This means it will make it easier to install, configure and get people using your on premise data. As you can see that this is something that I am already using and will be a great feature going forward.

Also the ability to handle failovers, as well as having the performance counter information will be great for viewing what is happening as well as the performance of the gateway.

You can find out the details here: Announcing General Availability of Power BI gateway for enterprise deployments

Power BI – January Desktop Update

This was announced last week, and there are a whole host of updates in the Power BI Desktop. I will go through a few ones here that I think are really important.

As you can see above, you can now add borders as well as Cartesian charts’ plot area.

Along with this, I really liked that you can now refresh data in individual tables, because sometimes in the past you did not want to refresh everything including your largest table.

And finally I see that they are still making performance improvements and cross rending is great. It has always been a fast visualization, but to make even that little bit faster means that everything will be that much quicker and who does not like speed?

You can find details here: Power BI Updates This Week: New Report Authoring Capabilities

Power BI – Troux Content Pack

This is yet another content pack, and if you are an existing Troux customer then I am sure that using Power BI will give you some great insights into your technology investments. Which will help you understand your data and how best you can leverage on your information.

You can find the details here: Explore your Troux data in Power BI

Power BI – New Custom Visuals

As you can see above there have been 3 new great visuals that have been released. And to me they are concentrated around mostly financials, which is great to see.

You can go here to see all the visuals: Power BI Custom Visuals

SQL Server 2016 – CTP 3.3

It was a surprise to see that they have released CTP 3.3 and most of the updates are around SSRS and SSAS Tabular.

It is great to see that now as per the visual above you can add your own favorite reports to your SSRS view of the world. Which I think is something that is so simple, but at the same time so valuable.

Along with this is the details and updates for SSAS Tabular.

It is really good to see that you can now create calculated columns in Direct Query mode. As well as applying Row Level security in Direct Query mode also. As I know personally in the past I did not implement Direct Query mode, due to the limitations, which now they have resolved.

It is good to see that they are adding a lot of updates and features for Business Intelligence to SQL Server 2016.

You can find details around SQL Server 2016 CTP3.3 here: Access your favorite KPIs and reports with SQL Server 2016 CTP 3.3

And then if you want to find out the details around SSAS Tabular you can find that here: What’s new for SQL Server 2016 Analysis Services in CTP3.3

SQL Server 2016 – eBook

This is an update from the original eBook, and there is a lot of great content in here, especially if you are not fully aware of what is coming in SQL Server 2016.

There are two versions for desktop and mobile.

You can find information about the eBook here: Free eBook: Introducing Microsoft SQL Server 2016: Mission-Critical Applications, Deeper Insights, Hyperscale Cloud, Preview 2

SQL Server 2016 – R

As you can see above R has come a long way, and is a great tool to use if you have the specific requirement.

The screenshot above was from a presentation by Jen Underwood. And there is some really valuable information in this slide deck.

If this is something of interest to you, you can view the slide deck here: Microsoft R Server and SQL Server R Services

BI-NSIGHT – Power BI (IOS Power BI App for SSRS, Enterprise Gateway, Printing, AT Internet Content Pack) – SQL Server 2016 (eBook, Mobile Publisher)

The year is finally coming to a close and it has been a really busy but amazing year.

I am looking forward to what will be happening in the Microsoft BI space next year, with the release of SQL Server 2016, as well as I have no doubt that there are a great things planned for Power BI.

Power BI – IOS Power BI App for SSRS

Whilst this is not the most amazing picture that there has been with regards to Power BI, I do think that this is a rather significant thing to mention.

The reason being is that this is the first time that we are starting to see the integration of Power BI and SQL Server (On-Premise) becoming one mobile application. Which is what was in the roadmap that was presented by Microsoft a few months ago.

In my opinion I do see that as next year continues we will see more features integrated. Which I personally think will be really great. What this will mean is that for people on the go, or who want to have a quick view of the data or information, it will always be at their fingertips. They will no longer have to have to log into multiple locations or have multiple mobile applications. They can open one mobile app, and then see the information that they need to see immediately.

I really am looking forward to see how this evolves next year.

If you have an iPhone you can find the updated app here: Microsoft Power BI – iTunes

Power BI – Enterprise Gateway

I am really pleased to see that they have moved so quickly with regards to getting the Enterprise Gateway for Power BI to support both Multi-Dimensional and Tabular models of SSAS. As well as already providing the support for SQL Server direct connectivity. As well as to SAP HANA.

I my opinion I do think that Microsoft is moving in the right direction and starting to provide some functionality for some of their Enterprise customers. And this is one tool that I know will be used to make the administration and access to in premise data sources that much easier.

As well as something that I will be using in the future.

You can find out the details here: Power BI Gateway Enterprise now supports live connections to Analysis Services and SAP HANA

Power BI – Printing

Just a quick note that they now have made it really easy to print your Power BI reports directly from Power BI.

Power BI – AT Internet Content Pack

Here is another great content pack from Microsoft. This time it is related to AT Internet, which help you get insights with your data. And this is yet another valuable content pack that can help its customers and potentially yourself quickly gain the insights to the information you have, to get the best return.

You can find more information here: Explore your AT Internet data in Power BI

SQL Server 2016 – eBook

Microsoft released this eBook, which I personally thought was the perfect timing. With a lot of people being on holidays it did mean that we have the time to read the book.

I downloaded it onto my mobile device, and while I did not read every single page, I did read most of the book. And I thought it was a great read. Not too technical, but it did give a lot of information on the sections that they put into the book. I was particularly interested in the Row Level Security (which I found in my mind has a similar implementation to SSAS), the Stretch Database (which I can foresee being used for large old data sets that are still required to be online, but not accessed frequently) and then Reporting Services.

I did read the entire Reporting Services section, and I enjoyed reading about the printing and how they have finally removed the Active X control. As well as the option to Export to Power Point, which I think a lot of people have been doing manually in the past. As well as other sections which I have read in separate blog posts in the past (Pining SSRS reports to Power BI, New Chart Types)

You can find out the information here: Free eBook: Introducing Microsoft SQL Server 2016: Mission-Critical Applications, Deeper Insights, Hyperscale Cloud, Preview Edition

And you can download it from here: Free eBook – Printable Copy

SQL Server 2016 – Mobile Report Publisher

It was great to see that Microsoft was able to meet their own deadline and have another release of SQL Server 2016 CTP 3.2 which included the new SSRS features.

And as we can see from the picture above they have incorporated the SQL Server Mobile Report Publisher, as well as the application to download and install. Along with this there is also a great blog post by Christopher Finlan which walks you through how to get this up and running.

You can find out all the details here: Mobile Report Publisher preview now available

BI-NSIGHT – SQL Server 2016 CTP 3.0 (SSAS, SSRS, SSIS) – Power BI (Chiclet Visual, SparkPost Content Pack, Weekly Service Update, Personal Gateway Update, Tiles in SharePoint)

I expected this week to be a really interesting week with SQL Pass happening. As I was sure to see some really good and interesting updates from Microsoft and it sure is living up to this.

There has been a lot of information on Twitter and on other blogs, so here is my take on the developments.

SQL Server 2016 CTP 3.0 (SQL Server Database Engine, SQL Server Analysis Services, SQL Server Reporting Services, SQL Server Integration Services)

There was a whole host up dates with SQL Server 2016 CTP 3.0, which is great to see, as well as some announcements of what we can expect in subsequent releases.

I am just going to highlight below what I think is relevant in the BI space. But there will be links below where you can find the related blog posts, which have more information from the Microsoft teams.


With regards to SSAS, it is good to see how much effort and work is going into the Tabular model. Which is what I thought would be the case.

I think that it is really great to see that they have changed the underlying structure from XMLA to JSON. The way that I see it, this is how they have implemented Power BI in terms of having the SSAS database sitting in memory in Azure. And without a doubt I am sure that they have learnt a lot, and from this they can then leverage this and bring it into the On Premise product. We all know how fast it is online!

The MDX Support for Direct Query is also a great update. I can see a lot of people leveraging this, and when you partner this with APS you can pretty much start to enable real-time analytics. Which can be a real game changer.

All the other updates that are coming into SSAS have mostly been completed either in Power BI Desktop or in Excel 2016. So it is great to see this in the Server product which will go a long way to ensure that it can scale and perform for enterprise workloads.


I have eagerly been waiting to see what was going to happen in the SSRS space. And whilst I had seen some of the now released information it is great to see it being released to the general public. As well as how well it has been received.

The pinning of SSRS reports into Power BI is a really smart move. And the ability to also refresh this report in Power BI is pure Genius. What this means now is you can leverage both of your On Premise and cloud investments. And to the users this will be seamless.

What I also really like is that you can often create really interesting SSRS reports, and the executives and high level managers do not need to see the details. They just want an overview. And now by leveraging this all into Power BI, it becomes their one stop shop!


There does not seem to have been a lot of love for SSIS, and to be honest it is a stable and really good product.

But what I did see is the Control Flow Template, and I am hoping that this is something similar to what you can currently do with BIML. What that is how I perceived it to be. And I am hoping that you can create different control flow templates for different control flows. So for example you could create a control flow template for a SCD Type 2. And then once you have it designed the way that you want, any other developers can then utilize it. This would go a long way in enterprises where you want to standardize the way of doing things.

You can read about all of the above here:

Power BI – Chiclet Visual Slicer

The one thing that I have been struggling with in Power BI was how to get a slicer to work, so that it looked good.

And low and behold there is a new visualization which can how do this. And to have it with images also is really smart. As people love to click on Images.

Another great announcement was from James Phillips that Microsoft would be releasing a new visualization every month, indefinitely. This is really great and I am sure that we will see some really interesting and useful visualizations in the future.

You can read all about it here: Visual Awesomeness Unlocked: The Chiclet Slicer

Power BI – SparkPost Content Pack

This week there is another interesting and great Content Pack. This time for SparkPost. Which you can now use to monitor your Email campaigns.

You can read about it here: Monitor Your SparkPost data with Power BI

Power BI – Weekly Service Update

Not only was there a host of announcements at SQL Pass, there was the weekly Power BI Service update.

Once again I am going to quickly highlight what there is in this week’s update.

They have made quite a few improvements with regards to the way we can share the dashboards in Power BI. All of these updates make it a lot easier to share the dashboard and to enable people to see how good Power BI is. The additions are (Sharing the Dashboards with AD Groups, People Picker and Sharing with a large number of Email addresses)

Along with this is the ability to start passing parameters into the URL. I have no doubt that passing URL parameters will keep on increasing and giving additional flexibility in the Power BI service.

You can read about it here: Power BI Weekly Service Update

Power BI – Personal Gateway Update

There was an update late last week for the Power BI Personal Gateway and it is mostly around bug fixes and performance improvements. Which is great to see because I do know that often we want it to run as smoothly and quickly as possible

You can find more information here: New version of Personal Gateway is now live!

Power BI – Tiles in SharePoint

And finally the guys from DevScope have now created a Power BI Tile for SharePoint.

I think that this will work really well, because it will give the ability to showcase all the work done in your Power BI reports, as well as not having to re-create reports over and over again.

If you want to find more details and pricing, you can find it here: Power BI Tiles for SharePoint

Power BI (Visual Contest, On the Go, Hyperlinks, Drill Up, Drill Down, tyGraph Content Pack) – SQL Server 2016 – CTP 2.3 (SSAS Tabular with a dose of speed, SQL Server Data Tools (SSDT), SQL Server Reporting Services (SSRS)) – Power BI (Personal Gateway for Power BI Update)

Right once again there is a lot to get into this week, and as with every week there are a whole host of Power BI updates.

Power BI – Visual Contest

I have to say that this is both a smart and fantastic move by Microsoft. This allows them to get or gain a whole host of new chart types that can be consumed within Power BI, without having to spend a lot of development time getting it all completed.

As well as there are a lot of smart people that have some great idea’s. And this gives them a great platform to showcase their idea’s. As well as get some recognition for their efforts.

As you can see in the above screenshot, this is a great KPI example. To me it does look similar to the Datazen KPI’s. But I do know that it would be welcome in Power BI.

You can read all about the contest details here: Announcing the Power BI Best Visual Contest!

Power BI – On the Go (Mobile Apps)

There has been another great update to the Mobile Apps for Power BI. And it is across all the current mobile platforms that are supported.

I like the idea that you can keep your favorites and have them on a dashboard within the Power BI Mobile application. This gives a very similar experience as with the Web based Power BI. Which is great when you potentially want to see data from different sources.

I also like the fact that you can enable Data Alert Rules, which means you can get alerts on your mobile device when the thresholds that you have set are exceeded. And just means that you do not have to go and keep on checking on reports or data. A much more proactive means of being notified.

There are some additional details which you can read about here: Power BI on the Go

Power BI – Hyperlinks, Drill Down and Drill Up

Once again this week the Power BI team has been very busy and has a whole host of updates to the Power BI Service.

Firstly, is the ability to put inline Hyperlinks, which I think is really great and will get the report consumer a much better and more streamlined experience.

The thing that I think is really fantastic is to have the ability to Drill Down as well as Drill up within the report. They have done another amazing job in making the process so simple to implement. As well as very easy to use when using the report. And I know that very often users ask if there is the ability to Drill Down. I also like the fact that you can then filter on your information once you have drill down. Which makes the entire report experience easy, quick as well as ensure that the users are really happy.

You can read all about it here: Power BI Weekly Service Update

Power BI – Content Pack tyGraph

Another week, another great Content Pack.

This time it is tyGraph, which is something that can be used or reported on if you use Yammer. Which I know more companies are looking to use, especially if it is part of your Office 365 Subscription.

You can read all about it here: Analyze and Monitor your tyGraph Data with Power BI

SQL Server 2016 – CTP 2.3 – SSAS Tabular with Direct Query

This was a very interesting blog post to read, due to the fact that in the past I never really thought of using the DirectQuery mode with SSAS OLAP or Tabular, due to the fact that in the past it did not perform well or fast.

From the blog post they have made some significant improvements in SQL Server 2016 CTP 2.3 And it is nice to see that SSAS is finally getting some attention. As well as looking at this example it would allow the report to be run and executed in real time. So if your source data is being updated on a regular interval, it means that going via SSAS Tabular to your Source SQL System you would be able to get up to date data. As well as it being so much quicker this really has the potential to be a game changer for a lot of organizations.

You can read all about it, as well as the details here: SQL Server Analysis Service 2016 CTP 2.3 DirectQuery in action

SQL Server 2016 – CTP 2.3 – SQL Server Data Tools (SSDT)

Just a quick note that they have updated how SSDT will be working going forward.

It appears that they have unified the setup for Database as well as BI (Business Intelligence). It makes perfect sense as the two work hand in hand.

You can read about it here: SQL Server Data Tools Preview update for August 2015

SQL Server 2016 – CTP 2.3 – SQL Server Reporting Services (SSRS)

It is nice to finally some actual changes and new things happening in SSRS. I am sure I am not the only one that agrees that it has almost been too long for SSRS to get an update. I personally was getting to the point where I was using SSRS as a last resort. But with all the upcoming changes I am sure that looking ahead this will become another alternative or an option for a reporting platform.

As you can see from above, it looks to me as if it will be along the similar lines of Power BI Desktop. Which I think is great so that report developers as well as people using Power BI will be used to a similar reporting experience.

It is also good to see, due to changing the way that they have created the new version of SSRS, that it is supported on pretty much any browser.

You can find out more about it here: What’s New in Reporting Services in SQL Server 2016 CTP 2.3

Power BI – Personal Gateway Update

It is great to see another update to the Power BI Personal Gateway. Along with the updates to SSAS OLAP (Multidimensional & Tabular) as well as the support for Custom ODBC drivers. Which means that you can connect to almost any database source.

This is a great alternative to ensure that your data in your Power BI reports are being updated and relevant. Which as I was writing about earlier, if your users are using the Power BI Mobile app, it then means that they can get notifications on the fly. Which can enable them to make the right decisions at the right time.

You can find out more information here: New version of Personal Gateway now available!

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


SSAS – KPI’s – Example of creating a KPI

Based on the previous BLOG post <font color="#ff000, we are going to explain how to create a KPI



·         Using our Internet Sales cube, we want to create a KPI in order to see if the guys are meeting their new target which is a 10% increase from their current sales.

·         So we will create a new KPI with the following:

o    The KPI Goal will be 10% greater than the current sales

o    The KPI Status will be if the KPI value is 100% greater than the KPI Value.

o    The KPI Trend will be based on the Previous Value selected from your Date Dimension using the Calendar Hierarchy


You can get a copy of this here:

·         I used the AdventureWorksDW2012 Data File and AdventureWorks Multidimensional Models SQL Server 2012


NOTE: We are using SQL Server 2014, and SSDT for Visual Studio 2013



1.       Go into the Adventure Works cube.

2.       Click on the KPIs and then select New KPI

a.        clip_image002

3.       Next we will give the KPI the following name and associate it to our Internet Sales Measure group

a.        clip_image004

4.       Now we will configure our Value Expression, which as per our example will be the Internet Sales amount

a.        clip_image006

5.       Next we will configure our Goal Expression which will be 10% greater than our Value Expression

a.        clip_image008

b.       NOTE: Due to using SSDT, the syntax is the following for the above:

Format([Measures].[Internet Sales Amount]*1.1,”$#,##0;($#,##0)”)

c.        NOTE: Because we are using a currency we formatted our Goal Expression so that when it displays it will display correctly.

6.       Next is the Status details.

a.        First what we did was to change the Status Indicator to a Shape as shown below:

b.       clip_image010

c.        NOTE: The reason that we did this, is so that when it is displayed in Excel you can see it easily based on the Shape Colour.

d.       Then for the Status expression we configured it with the following:

e.       clip_image012

f.         Here is the text below if this is not clear enough:


    When KpiValue( “Internet Sales Growth” ) / KpiGoal( “Internet Sales Growth” ) >  1

    Then 1

    When KpiValue( “Internet Sales Growth” ) / KpiGoal( “Internet Sales Growth” ) <= 1


         KpiValue( “Internet Sales Growth” ) / KpiGoal( “Internet Sales Growth” ) >= .85

    Then 0

    Else -1


g.        What we have done in the above Status expression is that if the growth is greater than 100% when comparing the Value to the Goal, then make the expression 1, which will make it green.

h.       What we have done in the above Status expression is that if the growth is less than 100%  but greater than 85% when comparing the Value to the Goal, then make the expression 0, which will make it yellow.

i.         Else it will be less than 85% in which case make it -1, which will make it red.

7.       Then for the Trend section we did the following:

a.        We changed the Trend indicator to the Status arrow.

b.       clip_image014

c.        NOTE: We did this so that when it is displayed in Excel it will display correctly.

d.       Then for the Trend Expression we configured it with the following:

e.       clip_image016

f.         Here is the text below if not clear enough


    When (KPIValue(“Internet Sales Growth”),[Date].[Calendar].CurrentMember) >

         (KPIValue(“Internet Sales Growth”),[Date].[Calendar].CurrentMember.PrevMember)

    Then 1

    When (KPIValue(“Internet Sales Growth”),[Date].[Calendar].CurrentMember) <

         (KPIValue(“Internet Sales Growth”),[Date].[Calendar].CurrentMember.PrevMember)

    Then -1


g.        NOTE: What we are doing above is we are taking the KPI Value and comparing it to the previous member in our Date.Calendar Hierarchy.

                                                                i.      The reason with using the Date.Calendar Hierarchy is that we can use any member or part of the hierarchy, it will then compare that to the previous value.

1.       EG: If you use quarter it will compare quarters. Or if you compare Month it will then compare months.

h.       If it is better than the Previous then make the trend 1 or Green Arrow.

i.         If it is worse or lower than the previous, then make the trend -1 or Red Arrow.

8.       Now if you process your cube and view the results in Excel it will look like the following for July 2005, where we are using the Date.

a.        clip_image018

SSAS – KPI’s – How Explanation of KPI Makeup

Below is an overall description of what is needed for the KPI’s when creating them in BIDS


1.       After you click on New KPI you will have a window shown below and underneath the picture are the details required for each section:



a.        Where it says Name:

                                                               i.      This will be the name for your KPI

                                                              ii.      NOTE: Make sure that it is descriptive and makes sense to the end user

b.       Associated Measure group:

                                                               i.      This is to which measure group you are associating the KPI.

c.        Value Expression:

                                                               i.      This is where you will use a measure to get the actual value of your KPI at Run time.

d.       Goal Expression:

                                                               i.      This is the goal or what you would like your KPI to get to

                                                              ii.      EG:

1.       You have people logging into your system and the more than can log in every minute the better.

2.       So it is decided that 500 logins per minute is the goal.

3.       Then in the Goal Expression you would put 500

e.       Status Expression:

                                                               i.      This is where you define that status is good, ok or bad for your gauge.

                                                              ii.      NOTE: You can change the status indicator to one that makes the most sense.

                                                            iii.      With the Status Expression it can also have three values

                                                            iv.      EG:

1.       1 is always good

2.       0 is always Ok

3.       -1 is always Bad

f.         Trend Expression

                                                               i.      This is where you define what the trend is for the KPI, meaning is it currently getting better or worse.

                                                              ii.      NOTE: You can change the Trend indicator to one that makes the most sense.

                                                            iii.      With the Trend Expression it can also have three values

                                                            iv.      EG:

1.       1 is always good

2.       0 is always Ok

3.       -1 is always Bad

Relocating to Australia – BI Job opportunities in Queensland

I thought I would let you guys know that I am about to relocate to Australia from South Africa. I have had an amazing time in South Africa, and learnt a whole lot whilst working at my past employer.

So this is a plug at anyone who has any lead or potential BI work in Queensland, Australia. I would really appreciate it, if you have anything to please email me.

I will be in Australia from 08 August 2014.

Below is a link to my CV and as well as my contact details.

CV-Gilbert Quevauvilliers-2014



SQL Server Analysis Services (SSAS) – Updating Project with Partition information

I am sure that this has happened to someone else before. You are making a change to your SSAS cube, within your SSAS cube you have created your initial partitions. But on your production server you have programmatically added additional partitions. Now by mistake or just not thinking you deploy your project, and when it prompts to overwrite your current database, you click YES.


Now your production SSAS cube has all the wrong partitions. SO then you have to go about creating them again and processing them again.


So below are the steps that I do, before I make changes to my SSAS project, so that if I happen to deploy it by mistake I will not have to recreate the partitions. You will still have to process them again, but it does save the hassle of having to re-create them all.



·         Our current Internet Sales Partition has the following partitions created on our Production Server

o    clip_image002

·         We are going to manually create a new Partition called:

o    Internet_Sales_2009

·         Then we are going to go through the manual steps to get this partition information into our existing SSAS Project.

o    So what when we are finished we will see our Internet_Sales_2009 Partition within our SSAS Project.

o    Currently the Project looks like this:

o    clip_image004


You can get a copy of this here:

·         I used the AdventureWorksDW2012 Data File and AdventureWorks Multidimensional Models SQL Server 2012


NOTE: We are using SQL Server 2014, and SSDT for Visual Studio 2013


Creating new Partition on Server

1.       What we did was to script out our current partition and then modify it to create one for the year 2009

2.       Below is a snippet of where we made the changes

a.        clip_image006

3.       Once we ran this we then could see our Partition for the year 2009

a.        clip_image008


Creating new SSAS Project and importing SSAS Database

In the steps below we are going to create a new SSAS Project and then import our SSAS database into our Project.


1.       Within SSDT we are going to create a new Project with the following:

a.        clip_image010

2.       Give your project a name.

a.        As with our example we gave it the name of Adventure Works – Production Import

3.       This will then start the Import Analysis Services Database Wizard

a.        Click Next on the first screen

4.       On the Source Database screen put in the details to your Server and select your database as with our example shown below:

a.        clip_image012

b.       Click Next

5.       This will then import everything from your server.

6.       And once complete it will look like the following below:

a.        clip_image014

b.       Click Finish

7.       If you now go to our Adventure Works Cube, click on Partitions you should see the following under the Internet Sales Measure Group

a.        clip_image016

8.       When it first loads it does not update the Adventure Works.partitions file

9.       You need to do the following to put the XML data into the Adventure Works.partitions file

a.        Click on Build and then Build Adventure Works – Production Import

b.       Once this is done you will then see that your Adventure Works.cube has an asterix and needs to be saved:

c.        clip_image018

d.       Click Save.

10.    Now you can verify that your Adventure Works.partition file has the information within the file by its file size:

a.        clip_image020

11.    Now you can close this project down.


Changing the Partition information on our current SSAS Project

What we are going to do below is to now take the information from our project we created above (Adventure Works – Production Import) and put swop out the partition file so that when we open up our current SSAS Project it will then reflect the additional partition, (Internet_Sales_2009)


1.       Go to the location where your current SSAS Project is.

2.       Then make sure you go into the details where you can actually see all your project files.

3.       IN our example it would be in the following location:

a.       C:\Users\DomainUser\My Documents\Projects\Adventure Works DW 2012\Adventure Works DW 2012

4.       And it will look like the following:

a.        clip_image022

b.       NOTE: You will see above the partition information stored in the Adventure Works.partitions

c.        NOTE II: Every cube that you create will always have a .partitions file, even if you have not created any partitions

5.       Now rename your Adventure Works.partitions file to Adventure Works.partitions.Backup_20140723

a.        NOTE: This is so that we know when we made the change.

b.       It will now look like the following:

c.        clip_image024

6.       Now go the location where you created your Import project (Adventure Works – Production Import)

7.       In our example it would be in the following location:

a.       C:\Users\DomainUser\My Documents\Projects Adventure Works – Production Import\Adventure Works – Production Import

b.       In this folder copy the Adventure Works.partitions file

c.        NOTE: You will see it should be larger than our screenshot in step 4 above:

d.       clip_image026

8.       Now go back to your folder location of your current SSAS Project. (which we have in step 3 above)

a.        Then paste the Adventure Works.partition file into the folder.

b.       NOTE: You should be able to paste it without any issues due to renaming the current partition file in step 5

9.       Now open your current SSAS Project and see when you go into the Adventure Works.Cube and go to Partitions if you can see the new partition.

a.        clip_image028


Now if by mistake you do deploy your project at least the cube information is up to date.