BI-NSIGHT – Azure Analytics Services – Power BI Dynamics AX Connector – Auto refreshing of Excel Files in Power BI – Excel 2016 Updates

Once again this past week Microsoft as released even more products. So let’s get started.

Azure Analytics Services

On 29 April at the annual Microsoft Build conference they announced new services on the Azure platform. I really think that Microsoft is doing a great job of having an entire cloud platform available for customers.

Due to the digital age and a lot of data is coming from mobile devices, which in turn are using digital services, I can see looking forward that as Microsoft says in their video, there will be a major requirement going forward for businesses to be able to use digital platforms in order to gain insights into their data.

With the Azure Analytics Services, this enables businesses to easily use products, without having to actually build the infrastructure and knowledge on the platform.

I am going to highlight the two new services which relate to BI. The Azure SQL Data Warehouse and the Azure Data Lake.

Azure SQL Data Warehouse

I really think that this is a smart move from Microsoft. It is something that we have been looking for and asking for from Microsoft. From the video (Build 2015: Azure SQL Data Warehouse) it appears to me that not only will it be a data warehouse, but also enable you to move data in and out of the data warehouse using SSIS.

I do hope that my assumption is correct, as this would be a great feature to have SSIS on a cloud platform. And this has the potential to create a whole host of new possibilities.

Along with this, it also appears from what I read that they are using the APS (Microsoft Analytics Platform System) as a basis for the Azure SQL Data Warehouse. If this is indeed true, I think that this could be a game changer. Due to the fact that we already know how easily it is to scale out with the APS. Which gives amazing performance.

Another great advantage is it looks like you can use the Azure SQL Data Warehouse as a source in Power BI. Which then enables all the features of Power BI. This combination I think would make a great competitor to other BI cloud products on the market.

Also as per the video, they have made it really easy to change the scale of computing that you require. It is really easy to complete and it can be changed in seconds!

Credit: Microsoft

Azure Data Lake

This is another clever move, allowing businesses to store all their data in a Data Lake. Which then gives the business the flexibility to then use this with the various Azure Services.

Here is a link to the video (Build 2015: Data Lake)

What I think will be great is that you can then take data from your data lake, either put this into Azure SQL Data Warehouse, or then use a Machine Learning API to run over your data. After you have got the required results, you could then put this into your Azure SQL Data Warehouse, and put Power BI on top of this to visualize your results and see based on your specific Machine Learning API, what affect it could potentially have!

You also have the potential to use existing Machine Learning API developed by other people, which means that you do not have to spend countless hours trying to get the correct Machine Learning algorithm!

Here is an overview of the Azure Analytics Services from Microsoft

Credit: Microsoft

Here is the blog post from Microsoft: Microsoft Announces Azure SQL Database elastic database, Azure SQL Data Warehouse, Azure Data Lake

Power BI Dynamics AX Connector

Just a quick note that I received an email that the Power BI guys have started planning for the integration of Dynamics AX as a connector in Power BI.

I am sure that this is welcoming news for Microsoft Dynamics AX customers

Power BI Dynamics AX – Planned

Automatic Refreshing of Excel Workbooks in Power BI

Just a quick review of my blog post this week, which was really well received.

It just explains how using a Power Query and Power Pivot, in which you use to get data into Excel Workbook, you can then have a mechanism how to get your Power BI report updated automatically.

This means that you can have your data on premise which you can then ensure gets into Power BI and then can have the potential for real-time updates.

AUTOMATING REFRESHING OF POWER BI REPORTS AND DASHBOARDS WITH EXCEL WORKBOOKS AND POWER UPDATE

Excel 2016 Updates

Just another quick note if you want to read up about the updates for Excel 2016, head over to Chris Webb’s blog post which has all the relevant information.

Chris Webb – What’s New In The Excel 2016 Preview For BI?

That is it for this week, another busy week and a lot going on!

 

Power Pivot and DAX in Power BI

Introduction: Something got me thinking the other day when I used the Google Analytics Connector and saw what looked like to me was a Power Pivot Model, as well as an image. I know in the current Power BI Designer as well as when you import an Excel file into Power BI on the web front end, you do not get to see these options. So it got me thinking, how is Microsoft doing this?

After some playing around I found a simple and easy solution!

NOTE: This does not currently work with Excel 2016

What this enables you to do, is to have your entire Power Pivot Model, along with your DAX calculations, as well as the synonyms within your Power Pivot Model available in Power BI!

Below is a screenshot of my completed dashboard, after which I will explain how I did it, and then an example with the Power Pivot Model, DAX calculation, Image as well as a synonyms!

I have used data from the AdventureWorksDW2014 database to get data into my Power Pivot Model.

 

How to get Power Pivot, with DAX calculations and Synonyms into Power BI

You guy are going to love how simple this really is to get completed.

  1. Create your Power Pivot Model, DAX Calculations and if required your synonyms.
    1. NOTE: You can load your data from Power Query into your Data Model (which is Power Pivot)
  2. Then what I did was to first create a really simple
    Pivot Table with a chart.
  3. Next I then created a Power View report, by going into the Insert
    Ribbon and then clicking on Power View, after which I created my Power View Report as shown below
  4. Then I saved my Excel Workbook.
  5. Next I uploaded the Excel Workbook to Power BI.
    1. NOTE: I uploaded it directly from my PC, as well as from One Drive for Business and they both achieved the same results.
    2. I had the following while it was being imported
  6. Now before I go on, I wanted to show you what my Power Pivot Model looked like in Excel.
    1. From the above screenshot, you will see that I have created two DAX
      Calculations.
      1. Sales Amount
        1. This is just a sum of the SalesAmount
      2. PM Sales
        1. This is a DAX
          Calculation that I found in http://www.daxpatterns.com/time-patterns/
        2. I then used this and applied it to my data.
      3. NOTE: The two DAX Calculations above is to show that they can then be used within Power BI
  7. Now once I had uploaded my Excel
    Workbook I went into the report in Power BI and saw the following:
    1. As you can see above this is identical to my Power View report in Excel.
  8. Then if I click on Edit Report, I see the following in Power BI, as you will notice below, I added an additional Combo Report, using the Sales and PM Sales amount.
    1. Which as you can see above, is my original Power View report, but added an additional chart, using the DAX Calculations from my Power
      Pivot Model.
    2. NOTE: You can see that it has my DAX Calculations of Sales Amount and PM Sales available and ready to be used.
  9. As you can see from above, you can now have a fully functional and working Power Pivot Model, with most of the Power Pivot features such as DAX Calculations and Synonyms available to be used with Power BI!
  10. So in a nutshell all that you need to do is to add a Power View report within your Excel workbook. Which will then enable the Power Pivot model to be available in Power BI

For the Synonyms to work, I simply added them within my Power Pivot Model, under the Advanced tab.

Adding an Image for Power BI from Excel

  1. To get the image embedded, I simply added it to my Power View Report in Excel.
  2. Then once I uploaded my Excel Workbook, I could then Pin the image to my dashboard.

In future blog posts, I will explain how I got Dashboards working when they are not created automatically in Power BI.

Link to Excel File

Below is a link if you want to use my Excel File and then upload it to your Power BI site

Please note, the only reason for the image for my blog, is I had to put in some type of image and I could not think of anything else to put in there!

Adventure Works DW – Power BI Power Pivot Example.xlsx

The future for me

I was reading through the email from The Databse Weekly and I saw an article from Paul Randal with regards to Paul being your mentor. This really got me thinking and where is my future within Business Intelligence (BI).

I really love what I do. I get great satisfaction and enjoyment when I can tell people a story about their data. Or show them insights into their data for the first time.

I find it easy to grasp new concepts and get it all working. I work entirely within the Microsoft BI toolset.

With the new Office 365 and Power BI offerings there is a whole new world to get my head around. It really excites me.

I have also started working for a BI consulting company and it equally exciting times here. They are looking to expand and really get into the Microsoft BI space. That’s where I come into the picture.

I feel that I am at the right place at the right time. In terms of using my existing knowledge within the Microsoft space, as well as enabling the consultancy to grow from strength to strength going forward.

This is where I feel that the being mentored by Paul Randal could get me onto the next level, which is where I want to go and where I will eventually get to. Being mentored by someone as knowledgeable and experienced means I can get there a little quicker!

So where do I see myself in the future?

I see myself learning and going to that next level. Enabling businesses to gain insights to their data faster and quicker than ever. As well as driving the BI consultancy to become the go to consultancy for Microsoft BI.

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:

2.        

                       clip_image002[4]

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

Thanks

Gilbert