BI-RoundUp – Power BI (Developer Update – Balanced Scorecard – Deep dive at Data & BI Summit Ireland – Difinity Conference)

Here is the weekly BI-RoundUp, lots of great things happened in the past week!

Power BI – Developer Update

There is now a developer update for Power BI Developer, and there are a whole host of new updates for the Power BI Developers. I will give a brief overview of each section and if you want more details please click on the link further below.

Embed Capabilities

With the single visual embedding there now is the functionality to be able to embed a single visual into a page as shown below. Or it is possible to select multiple visuals and decide their size and position of each one

Now there is also the option to define the Custom layout sizing with regards to the Page Layout as well as the Visual layout.

There is now also a phased loading of embedded content where there is the option now to call it in the following order

  • Preload
  • Load metadata
  • Render Object

There is now more Tile Embedding options as shown below

We added support for more tile types that can be embedded. Here are the additional tile types:

  • Live page tiles
  • Custom visual tiles
  • R visual tiles
  • Live stream tiles
  • Image tiles
  • Video tiles
  • Web content tiles
  • Q&A tiles
  • Visio tiles

Automation and Life Cycle Management

There is the option now to Update a report, as with their example if you have deployed a report to 20 different organizations and you want to make a change, you can now leverage the Update report API. But this is something that I would test and read through the documentation as it does say that “an update operation changes the entire content of the report”

There is now also Row Level Security (RLS) for Tiles and Dashboards that have been embedded

New Capabilities in Azure

There is now the capability to use the Azure Resource Manager (ARM) with PowerShell to automatically manage Power BI Embedded Azure SKU’s (A)

As well as an updated version of the Custom Visuals API in which there is now Authentication with Azure Active Directory (AAD) and localize anything with the localization manager.

You can find all the details for the Power BI Developer Update here: Power BI Developer community January update

Power BI – Balanced Scorecard


This blog post goes into details on how people within Microsoft are utilizing Balanced Scorecards and KPIs to measure their success. It goes into details on how they measure their success and use Power BI in order to visually see how they are doing.

This is a great blog post if you are looking to implement scorecards and KPIs and is well worth the read.

You can find more details here: Balanced Scorecards in Power BI

Power BI – Deep Dive at Data & BI Summit in Ireland

If you are looking to go to the Data & BI summit in Ireland from 24 – 26 April 2018, be sure to attend the Deep Dive, which will be presented by Will Thompson, Amanda Cofsky and Chuck Sterling from Microsoft.

You can find more details about the Deep Dive here: Microsoft Deep Drive Track at the Data and BI Summit April 22-26

Power BI – Difinity Conference

I am honoured to be presenting my session at the Difinity conference on Power BI Row Level Security Made Easy

It is not too late to get a ticket, there are also some great pre-con sessions and I am almost spoilt for choice in terms of which one to select.

I am also looking forward to meeting up with fellow Power BI guru’s, as well as others from Microsoft.

If you want to find more information please find it here: Difinity 2018

Power BI – Losing Disk space when working with Power BI Desktop

I recently had an issue on a customer’s server, where I was developing Power BI Desktop reports. And the C: Drive was running out of disk space, which was attributed to my user profile.

Upon inspection I found that the TempSaves directory had a whole stack of temporary files saved in there. Even though I had previously selected to change the option in Power BI Desktop to delete files

I am pretty confident it has to do with the Auto recovery feature in Power BI Desktop.

If you are ever having any issues here is where you can go and delete the unwanted files that are no longer relevant.

Click on Start, then Run and put the following into the Run Command below.

%UserProfile%\AppData\Local\Microsoft\Power BI Desktop\TempSaves

This should then open the location of the Temp Saves for Power BI Desktop Files.

I then selected all the files that I knew I no longer needed.

And then deleted them.

NOTE: I did keep the most recent file, which had todays date, which I did currently have open.

I then could get back roughly 10GB of disk space.

BI-RoundUp – Power BI (January 2018 Update – Power Apps Custom Visual)

Welcome to 2018, and I am sure that everyone is keen and ready to hit the ground running. I am really excited to see what this year will bring, both personally as well as with how things progress specifically in the Microsoft BI (Data and Analytics space). So here is my first BI Roundup for 2018

Power BI – January 2018 Update

It almost caught me by surprise that it was already time for another Power BI update, and here we are.

Reporting

The Show/Hide pages does not appear to be anything significant, but I have to admit that for me this is AWESOME. As I have been developing Power BI reports, I have often had to save multiple PBIX files, because I would be busy working on a new report, and someone would ask for a quick change. In order to get the change published I had to make a copy of the PBIX, then rename, change and upload.

Now I simply can hide the page in my existing PBIX file, and when read (or if not required) I can unhide the page.

Next is the ability to control the background colours for Cartesian and map controls. This has been available in other chart types, but not it is available in more charts.

Also in the past when using charts with labels that are rather long, they have often been truncated. Now with the latest update I can modify the maximum size in order to get more of the labels visible. As shown I can change the sizing and I can see more details on the label.


Along with this, under the same section in the charts is now the ability to be able to set the Inner Padding for Bar and Column charts. As shown below by changing the Inner Padding, the bar chart can start to almost look like a histogram. Likewise, I can also shrink the Inner padding to make the bars smaller than the defaults.

Showing the dates as a hierarchy is another update that will help when you do not have a date table in your model. I personally would suggest creating a date table, because this unlocks all the Time Intelligence measures within Power BI. But with that being said now with a Date Hierarchy, I can now select a single value from the hierarchy and it will only give me those values. If I select Month, it will then only show me the months.

When using the Relative Date Slicer, there now is the option to select an Anchor date. This is an awesome feature especially if you want to ensure that your starting date is from a specific period. As with the example in the video from Amanda where she references if you are doing quarterly reviews and you only want to see the previous quarter. You can use the anchor date to achieve this.

And finally, there is already an update for Q&A in the desktop, for the TOP N selection. I did blog about it this week, in terms of the hidden gems when using Q&A (Ask a Question (Q&A) Hidden Treasures in Power BI Desktop)

Analytics

In this section there was only one update with a new quick measure from Daniil Maslyuk, where he has created a quick measure for the Pearson correlation coefficient between two measures within a certain category.

Custom Visuals

Once again in this month’s updates there are a whole host of Custom Visuals, and I am going to give screenshots and the names of what they are. Especially the PowerApps Custom visual because it has its own entire blog post from the Power BI Team

Next is the TreeViz visual which is similar to a decomposition tree.

Funnel with Source by MAQ Software

Box and Whisker by MAQ Software

Agility Planning Matrix Light which reminds a bit of a pivot table

Image Grid

Gantt Chart by MAQ Software

Data Connectivity

There is now support for Azure Active Directory authentication for Azure SQL database and Data Warehouse connectors. Which is really great because some customers these days only have all their data in the cloud. As well as others being federated from their local active directory.

And finally, there is the option to change the display language not only for the application (which is the front end or where you create the reports), but also for the Model (Which is the query editor where you can mash the data up)

You can find all the blog post details here: Power BI Desktop January Feature Summary

Power BI – Power Apps Custom Visual

This is something that I know a lot of people have been waiting for. And now it is finally here. The capability to include Power Apps into my Power BI report.

This opens endless possibilities, as an example I now could enable write back from within my Power BI report, back to my source data. Which would then update the report. I could also potentially use Microsoft flow via Power Apps to distribute content or send emails directly from my Power BI report.

I will not go into too much detail, but rather wanted to highlight that the capability is now there.

You can read the fascinating blog post here: From Insights to Action with the Power Apps custom visual

Ask a Question (Q&A) Hidden Treasures in Power BI Desktop

The new Ask a Question (Q&A) in Power BI Desktop really does make it quick and easy to create new visuals.

Here is a link to the blog post from the Power BI Team: Q&A for report creation (preview)

I have been using Q&A in the Power BI Service for some time, and there are a few hidden treasures that you can now also use in Power BI Desktop. I will demonstrate a few of them that I know below.

I think the hidden gem is that the amazing people in the Power BI team have enabled Q&A to understand context so that you can not only ask questions as we would ask, but also it understands things like dates (Today, This Month), as well as for example highest, top, bottom etc.

So this can then be leveraged to easily create visuals that will always be in the context that they were created in.

Q&A for today

The first example, I created a card showing me the sessions for today 04 Jan 2018. The reason I wanted this, is because I am always interested in how many sessions I have for today. So as time moves, so will the card below, to always show me today, which is the context it was created in.

HOW COOL is that!

In order to confirm this, I validated it with the following screenshot below, showing what my sessions were for today 01/04/2018. As well as the DateTime where I have my PBIX file open.

And if I look on closer inspection at the Visual level filters, I can see where it has applied the filter automatically for me.

Q&A for current (this) month

A second example is where I want the total sessions for the current (this) month, so that as time moves on it will always show me the current month that I am in, again the context in which I created it. I did not script this at all, that it would add up to 1,000 exactly!

One thing to take note if, is that I chose “this month (Date > Date), which is from my Date table, and the Date column. This is because I have created a relationship from my Analytics table to the date table. This ensures that it is used within the right context.

Once again if I look at the Visual level filters it shows me how it created the measure in the card, as well as it shows that it will move with time.

Other hidden gems

As I found out from the documentation there is a whole host of hidden gems that you can use in context when asking a question.

Tips for asking questions in Power BI Q&A

At the end of this blog post are the current words and terminology that Q&A recognizes.

Differences between Q&A in the Power BI Service and Power BI Desktop

EDIT: 10 Jan 2018

Once again the Power BI team is incredibly quick at updating Power BI Desktop, and where I had previously said there were some differences between Q&A in the Power BI Service and Power BI Desktop, with the January 2018 release, it now appears to be identical.

So as shown below, if I was now searching for the date of my Max Sessions, this can easily be achieved with the following shown below.

 

 

 

 

 

 

 

 

 

 

Once again I am really impressed with Power BI, and I love working with the product!

It would be nice to have the ability to edit the Q&A as you can do on the Service. So I have created the following idea on the link below.

Vote here: Edit Q&A questions in Power BI Desktop

I hope that you have found this blog post useful and can start leveraging Q&A in Power BI Desktop, it really is a fantastic feature.

Power BI – Q&A Words and Terminology

If you are the owner of a dataset, add phrasings and synonyms to improve the Q&A results for your customers.

Aggregates: total, sum, amount, number, quantity, count, average, most, least, fewest, largest, smallest, highest, biggest, maximum, max, greatest, lowest, littlest, minimum, min

Articles: a, an, the

Blank and Boolean: blank, empty, null, prefixed with “non” or “non-“, empty string, empty text, true, t, false, f

Comparisons: vs, versus, compared to, compared with

Conjunctions: and, or, each of, with, versus, &, and, but, nor, along with, in addition to

Contractions: Q&A recognizes almost all contractions, try it out. Here are a few examples: didn’t, haven’t, he’d, he’s, isn’t, it’s, she’ll, they’d, weren’t, where’ll, who’s, won’t, wouldn’t.

Dates: Power BI recognizes most date terms (day, week, month, year, quarter, decade, etc…) and dates written in many different formats (see below). Power BI also recognizes the following keywords: MonthName, Days 1-31, decade.

Examples: January 3rd of 1995, January 3rd 1995, jan 03 1995, 3 Jan 1995, the 3rd of January, January 1995, 1995 January, 1995-01, 01/1995, names of months.

Relative dates: today, right now, current time, yesterday, tomorrow, the current, next, the coming, last, previous, ago, before now, sooner than, after, later than, from, at, on, from now, after now, in the future, past, last, previous, within, in, over, N days ago, N days from now, next, once, twice.

Example: count of orders in the past 6 days.

Equality (Range): in, equal to, =, after, is more than, in, between, before

Examples: Order year is before 2012? Price equals between 10 and 20? Is the age of John greater than 40? Total sales in 200-300?

Equality (Value): is, equal, equal to, in, of, for, within, is in, is on

Examples: Which products are green? Order date equals 2012. Is the age of John 40? Total sales that is not equal to 200? Order date of 1/1/2016. 10 in price? Green for color? 10 in price?

Names: If a column in the dataset contains the phrase “name” (e.g., EmployeeName), Q&A understands the values in that column are names and you can ask questions like “which employees are named robert.”

Pronouns: he, him, himself, his, she, herself, her, hers, it, itself, its, they, their, them, themselves, theirs, this, these, that, those

Query commands: sorted, sort by, direction, group, group by, by, show, list, display, give me, name, just, only, arrange, rank, compare, to, with, against, alphabetically, ascending, descending, order

Range: greater, more, larger, above, over, >, less, smaller, fewer, below, under, <, at least, no less than, >=, at most, no more than, <=, in, between, in the range of, from, later, earlier, sooner, after, on, at, later than, after, since, starting with, starting from, ending with

Times: am, pm, o’clock, noon, midnight, hour, minute, second, hh:mm:ss

Examples: 10 pm, 10:35 pm, 10:35:15 pm, 10 oclock, noon, midnight, hour, minute, second.

Top N (order, ranking): top, bottom, highest, lowest, first, last, next, earliest, newest, oldest, latest, most recent, next

Visual types: all visual types native to Power BI. If it’s an option in the Visualizations pane, then you can include it in your question. The exception to this is custom visuals that you’ve manually added to the Visualization pane.

Example: show districts by month and sales total as bar chart

Wh (relationship, qualified): when, where, which, who, whom, how many, how much, how many times, how often, how frequently, amount, number, quantity, how long, what

 

Power BI – Turning off (Disabling) Native Database Queries

In this quick blog post I am going to show how to turn off or disable having the dialog
box which pops
up when using Native Database Queries.

This can sometimes be an extra step on something that you have to action when refreshing your data either in the Query Editor or in Power BI Desktop.

NOTE: I was often prompted with the following below for EACH native database query.

As you can see below this is typically what would happen if I ran a native database query

  • I would first get the yellow car asking for permissions to run the native database query.
  • Then once I clicked Edit Permission I then got the following Window, showing me the query that is going to be run.
  • Once I click Run it would then run the query.
  • So this can be time consuming especially if I had multiple queries.

How to turn off or disable the Native Database Queries

In the steps below I will show how to easily turn (Disable) the native database queries

NOTE: I could complete the steps below from either being in the Query Editor or within Power BI Desktop.

  • Click on File and then Options and settings and then Options
  • Then under GLOBAL I clicked on Security
  • Then in the Security settings at the top it has got a section which says Native Database Queries you will see an option.
    • And there is a tick
      next to “Require user approval for new native database queries
    • I removed the tick.
  • Then click
    Ok.

Now I went back and ran my native database query and it simply
ran with no requests to Edit Permissions or to Run the query.

Conclusion

I have shown how to disable the native database queries which not only helps when I am developing but also assists me when my Power BI Desktop file is refreshing.

BI-RoundUp – Power BI (December Desktop Update – Larger Power BI Premium PBIX – Power BI Bookmarking Contest – Premium Capacity WhitePaper)

Even though it is almost the holiday season, the wonderful people from the Power BI team have given myself (and hopefully you also) some early birthday presents. So please find all the updates below.

Power BI – December Desktop Update

There was an incredible number of updates in the December edition of Power BI Desktop, so please find below all the details.

Reporting

The first and biggest thing in the latest update was now the capability to be able to Ask a Question (Q&A) within Power BI Desktop, which relates to asking a question on a report page. Previously this could only be done in the Power BI Service on a dashboard.

I have used Ask a question (Q&A) in the Power BI Service previously and it works really well. But with this now being in Power BI Desktop, it enables business users to quickly create the visualizations that they are looking for. And then if need be they can customize them afterwards.

I also think that this will go a long way to facilitate self-service report creation, because it now means they can just ask a question and get a visual response.

Next is updates to Bookmarking:

  • The bookmark will now save your cross-highlighted state when you create your bookmark.
  • INSERT GIF HERE
  • Along with this there is more flexibility with regards to what is bookmarked.
    • Data – This will apply properties relating to your data such as filters and slicers
    • Display – This lets you decide if you want to include Visual Properties of the display item
    • Current Page – This lets you apply the bookmark to the current page only, if deselected it will not apply to the current page you are in.
    • All Visuals – This means that the bookmark will bookmark all the visuals on the entire page.
    • Selected Visuals – This will only apply the bookmark to the selected visuals, which I would have selected before applying the bookmark.

Next is the Field Properties Pane and Field Properties Description, which now allows you to add in a description for your Field Names. This is great to document or make a note within your Power BI Desktop model. Especially if someone else is going to come along and edit the Power BI Desktop PBIX, they can now find additional details within the file.

There is now the capability to be able to create a scatter visual from the x & y-axis groupings. What this means is that you no longer need to add in a category to see the details. The only currently caveat is, is that you have to use a Numeric column it currently does not work on measures. But I am certain that this will change in the near future.

If you use maps, and have had a lot of data points in the past, the new algorithm has also been applied to maps. This is great because where they are dense, the human eye will not be able to see any difference. But more importantly it will show the outliers, which are often very important.

(I know it is really wide, but that is how far I had to drag it across in Power BI Desktop in order to get the entire name!)

Along with this, there are now responsive slicers, which is an update to the existing responsive visuals. This makes it easier to use especially when on touch screen’s or mobile phones.

There are a whole host of Custom Visuals, some I have seen before, but some really cool new ones.

  • Card Visual
  • Table Heatmap
  • Data Image
  • Power Matrix KPI
  • Text Filter
  • ChartAccent – Line Chart
  • ChartAccent – Bar Chart

Analytics

In the analytics section there is now is the ability to drill filters to other visuals within your Power BI Desktop report.

What this means if you drill down in a visual it will apply the same filter to all the other visuals in your report. This is really fantastic, because I know personally that I often want to see the context of what I am drilling down to and how it relates with my other visuals.

Data Connectivity

Please find below new data connectors, as well as some updates to existing data connectors.

  • Adobe Analytics Connector
  • HDInsight Interactive Query connector
  • Data.World connector
  • SAP BW connector improvements – Ability to be able to connect to either the SAP Business Warehouse Application or Message Server.
    • As well as currently this improvement is only available in Power BI Desktop, and will be coming to the On-Premise Data Gateway soon.
  • IBM Netezza connector is now generally available

There is a lot of content to go through, so if you would like to watch the video or find more details here is the link to the blog post: Power BI Desktop December Feature Summary

Power BI – Larger PBIX sizes for Power BI Premium

As you can see above, available now in Power BI Premium, you can now upload PBIX files that are up to 10GB in size. I am sure that this is a great welcome for customers who have had larger PBIX files.

You can find the blog post details here: Announcing Power BI Premium support for larger datasets

Power BI – Bookmarking Contest

There is a Power BI Bookmarking contest where you could not only win some awesome Power BI Swag, but also be seen by the Power BI community as to how awesome your Bookmarking capabilities are.

More details can be found for the competition here: Bookmarking Contest: Dec. 12th – Dec. 27th

Power BI – Premium Capacity White Paper

Please find the link below to the newly released Power BI Premium White Paper

Power BI Premium White Paper

How to enable Excel Pro’s to use Power BI Datasets

In the steps below, I am going to demonstrate how to access your Power BI Dataset through Excel.

This is a great example for people who love Excel and use it as their tool of choice, but the company or organization still wants to have a single version of the data. By leveraging the steps below the data is available in the Power BI Service, as well as from Excel.

NOTE: This currently only works on a PC.

Installation Power BI Publisher for Excel

There are 2 ways to access data in the Power BI Service via Excel. I prefer the method below, because it allows for easier connectivity as well as not having to go and download ODC files and then store and open them each time.

By using the Power BI Publisher for Excel, people who use Excel will have another item in the ribbon in order to access Power BI data.

  • The first thing I needed to do was to install Power BI Publisher for Excel.
  • I chose the Download for Office 64-bit, because that is the installation that I have gotten installed.
  • Once the download was completed, I ensured that Excel was closed and completed the installation.
    • I simply accepted all the defaults until the installation was complete.
  • I then opened Excel and verified that I could now see Power BI in the ribbon

How to connect to a dataset

Next, I will demonstrate how to connect to a dataset.

  • I opened Excel and went to the Power BI Ribbon.
  • I then clicked on Profile and Sign In
  • I then signed in with my Organizational account
    • I verified that I was signed in by clicking on Profile again
  • Next, I clicked on Connect to Data
  • This then brought up all the reports and datasets in my own workspace, as well as other reports and datasets that had been shared with me.
  • As you can see below these are datasets that were my own
  • And if you had a dashboard shared with you, that also means that you have got access to the underlying reports and datasets.
  • In order to access datasets shared with you, make sure where it says Select a workspace: it says My Workspace
  • This is shown below with the Share Icon
  • Then click Connect
  • This will then connect and create the Pivot Tables to the dataset, as you can see with my example below.

Enabling Access to Dataset via Sharing or App Experience

There are two ways that I am currently aware of where you can grant access to the dataset.

  • The first is via Sharing a dashboard to a particular user.
  • The second is create an App from an App Workspace, and then the user gains access once they have got the App.

Completing either of the following above will ensure that when the user clicks on Connect to Data in the Power BI Ribbon they will then be able to see the dataset. As shown below where my user “Pro” can see the dataset that he has got access to via getting an App.

And now when in Excel and the user “Pro” clicks on the Connect to Data, ensures that it is set to “My Workspace”, as well as the data that user “Pro” is connecting to is the Dataset, user “Pro” can connect to the dataset called “Fourmoo Google Analytics”

Conclusion

As I have demonstrated how to connect to a Power BI Dataset using Excel, as well as how to grant access so that your users can gain access.

If there are any questions, or advice please leave it in the comments below.

BI-RoundUp – Power BI (Nov On-Premise Data Gateway Update – Weekly Hot Topics – Project Online Content Pack)

After very quiet in the previous week, here is this week’s round up details.

Power BI – November On-Premise Data Gateway Update

Whilst it might not appear as though there is a lot with regards to the November update to the On-Premise data gateway there IS!

The first thing is that they have now implemented High Availability. This is really great news, because it means you now can have the On-Premise data gateways in a cluster. So that if one of the servers fail, it will automatically move over to another one.

It appears to be really simple to setup. And then when you are in the Power BI Service, all the gateways in a cluster, appear as one. Which makes it again really easy for people who are Gateway Administrators to just use the gateways as they previously did.

There is also now additional logging for Mashup Engine Traces, so that you can see what the Mashup Engine is doing.

Now if the On-Premise Data Gateway cannot connect via TCP, it will automatically fall back to HTTPS, making the gateway that much more redundant to ensure that your data gets refreshed.

And finally, the Mashup Engine for the On-Premise Data Gateway has been updated with the November version.

You can find more details here: On-premises data gateway November update is now available

Power BI – Weekly Hot Topics

There is now a weekly hot topics blog post from the Power BI team, which will highlight members from the Power BI community who have helped out the most.

Also, it highlights the Top 10 Viewed Threads and the Most Active Threads.

Go and have a look here: Weekly Hot Topics

Project Online Content Pack

As you can see from the screenshot above there now is the capability to use a Project Online Content Pack if your organization is currently using Project Online.

The content pack has got a lot of default reports and great information out of the box.

There have been quite a few additions to the previous version of the Content pack, so it would be a great idea to get this newer content pack.

You can find all the details here: A new Project Online Content Pack for Power BI

Power BI Embedded InfoGraphic

There has recently been a lot of talk around Power BI Embedded for various reasons.

The first being that Power BI Embedded can be used to for Power BI Reporting requirements, where the users are viewing it from an internal application. This can work for existing applications where a business has data. As well as potentially as another avenue to limit costs of using Power BI.

The second is that there are now 3 different Power BI Embedded SKUs which can also create confusion with regards to which SKU is applicable to me in my organization.

So below is the Power BI Embedded Infographic that I have created. I do hope that this infographic will simplify the different versions for Power BI Embedded.

As well as give a clear indication, to show based on what you are trying to achieve, which option is best suited.

I did source this information from the following blog post from the Power BI Team: Power BI Developer community October update

You can also find the image in the following location: Power BI Embedded

As always, if there are any questions or there is anything that I have left out, or incorrect please let me know and I will happily assist or resolve any issues.