Category Archives: Self-Service BI

Online Analysis Services Course: Developing a Multidimensional Model

Check out the excellent, new online course by Peter Myers and Chris Randall for Microsoft Learning Experiences (LeX). Lean how to develop multidimensional data models with SQL Server 2016 Analysis Services. The complete course is available on edX at no cost to audit, or you can highlight your new knowledge and skills with a Verified Certificate for a small charge. Enrollment is available at edX.

Let’s block ads! (Why?)

Analysis Services Team Blog

Explore your Partner Center Analytics Data in Power BI

We’re pleased to announce the public preview of the new Partner Center Analytics App for Power BI for direct partners.

Get a visual representation of your Partner Center Analytics data with the Partner Center Analytics app for Power BI, including:

  • Growth of your customer base, subscriptions, and licenses.
  • Usage of Office 365, Microsoft Dynamics, and Microsoft Azure products.
  • Daily consumption units for each metered resource in each Azure subscription for the last 60 days.
  • Estimated cost based on latest rate card.
  • Ability to export datasets and create custom reports, including per customer.

Get started with the Partner Center Analytics preview now by following the installation steps here.

You can also watch the following video (starting at minute 39) to view a demo of the Power BI app that was presented at Inspire 2017 in “CSP02 – CSP Platform Updates and Roadmap” breakout session on Day 3.

54f9fefb aa4d 4e05 a8b6 685b809eac86 Explore your Partner Center Analytics Data in Power BI

Here is a sample of the dashboard that will be created for you when you use the App

2d4e7d50 6baf 49e1 8ec6 b812fc02d8de Explore your Partner Center Analytics Data in Power BI

And here is a sample of the report pages

91889fce 2167 462b a5a2 f6fc8a6ef8f1 Explore your Partner Center Analytics Data in Power BI

841f413f 7210 4a4a 98d4 0fdadcc394ea Explore your Partner Center Analytics Data in Power BI

We’re always interested in hearing your feedback – please contact us at http://support.powerbi.com to let the team know how your experience was and if there’s anything we can do better. We look forward to your feedback!

Let’s block ads! (Why?)

Microsoft Power BI Blog | Microsoft Power BI

PowerShell for Assigning and Querying Tags in Azure

Tags in Azure are useful pieces of metadata for documenting (annotating) things such as:

  • Billing or cost center categories (ex: general ledger code)
  • Environment names (ex: Dev, Test, Prod, Sandbox)
  • Project or system
  • Purpose or application
  • Team, group, department, or business unit
  • Who owns or supports the resource
  • Release or version numbers (ex: for testing infrastructure)
  • Archival date (ex: if infrastructure is only needed temporarily)
  • Who initially created the resource
  • Which customer a resource applies to (ex: for an ISV)
  • Service level agreement
  • Patching or maintenance window
  • etc…

Tags are free-form key/value pairs. So, they can be used for tracking anything you find to be helpful. Tags are particularly helpful for breaking down invoicing costs. For instance, rather than seeing the entire cost for certain resources (like VMs or storage) in a resource group, tags allow you to subdivide the resource costs further, or to group costs in another way across resource groups. Here’s what the tags look like when you download usage (the new V2 format) for your subscription:

 PowerShell for Assigning and Querying Tags in Azure

You can assign tags for resource groups, as well as individual resources which support Azure Resource Manager. The individual resources do not automatically inherit tags from the resource group parent. A maximum of 15 key/value pairs can be assigned (though you could store concatenated values or embedded JSON in a single tag value as a workaround). You may want to just assign tags at just the resource group level, and use custom queries to “inherit” at the resource level. Alternatively, you may want to assign tags to the individual resources directly particularly if you want to see them clearly on the standard “download usage” report of billing.

Since the key/value pairs are just free-form text, watch out for uniformity issues. To improve consistency, you can utilize policies to require tags and/or apply defaults if you’d like (for example, you might want to enforce a “Created By” tag). Tags can be set in the ARM template when you initially deploy a resource (which is best so that no billing occurs without proper tagging), or afterwards to existing resources via the portal, PowerShell, or CLI.

The three tags I’m currently using in an implementation are Billing Category, Environment Type, and Support Contact:

 PowerShell for Assigning and Querying Tags in Azure

The above screen shot shows setting tags within the portal. If you have more than a handful of resources, that won’t be efficient at all. Following are a few PowerShell scripts to help with that setting tags.

Assign Tags to a Resource

This script will *overwrite* any and all tags previously assigned to one resource.

$  resourceGroupName = 'InternalReportingRGDev'
$  resourceName = 'bisqlvm1datastdstrgdev'

$  azureResourceInfo = Find-AzureRmResource -ResourceGroupNameEquals $  resourceGroupName -ResourceNameEquals $  resourceName 

Set-AzureRmResource -Tag @{ billingCategory="Internal Analytics"; supportContact="Analytics Team"; environmentType="Dev" } -ResourceName $  resourceName -ResourceType $  azureResourceInfo.ResourceType -ResourceGroupName $  resourceGroupName -Force 

Assign Tags to a Resource Group

This script will *overwrite* any and all tags previously assigned to one resource group.

$  resourceGroupName = 'InternalReportingRGDev'

$  azureRGInfo = Get-AzureRmResourceGroup -Name $  resourceGroupName

Set-AzureRmResourceGroup -Id $  azureRGInfo.ResourceId -Tag @{ billingCategory="Internal Analytics"; supportContact="Analytics Team"; environmentType="Dev" } 

Assign Tags to All Resources Within a Resource Group (Inherited from the RG)

This script will *overwrite* any and all tags previously assigned to one resource.

$  resourceGroupName = 'InternalReportingRGDev'

$  azureRGInfo = Get-AzureRmResourceGroup -Name $  resourceGroupName
foreach ($  item in $  azureRGInfo) 
{
Find-AzureRmResource -ResourceGroupNameEquals $  item.ResourceGroupName | ForEach-Object {Set-AzureRmResource -ResourceId $  PSItem.ResourceId -Tag $  item.Tags -Force } 
}

Add an Additional Tag to a Resource Group

This script adds a new tag and preserves existing tags for one resource group. It only accepts new tags (i.e., it will error out if you repeat existing tags).

$  resourceGroupName = 'InternalReportingRGDev'

$  azureRGTags = (Get-AzureRmResourceGroup -Name $  resourceGroupName).Tags

$  azureRGTags+= @{ billingCategory345="Internal Analytics" }

Set-AzureRmResourceGroup -Tag $  azureRGTags-Name $  resourceGroupName 

Query to Get List of Tag Names & Values for a Specific Resource

This checks for the tags assigned to one resource.

$  resourceGroupName = 'InternalReportingRGDev'
$  resourceName = 'bisqlvm1datastdstrgdev'

(Find-AzureRmResource -ResourceGroupNameEquals $  resourceGroupName -ResourceNameEquals $  resourceName).Tags

Output is a list of each Name/Value pair which has been assigned:

 PowerShell for Assigning and Querying Tags in Azure

Query to Get List of Resource Groups With a Specific Tag Value Assigned

(Find-AzureRmResourceGroup -Tag @{ billingCategory="Internal Analytics" }).Name 

Output is a list of resource groups which have been assigned that tag name and value.

Query to Get List of Resources With a Specific Tag Value Assigned

(Find-AzureRmResource -Tag @{ environmentType="Dev" }).Name 

Output is a list of resources which have been assigned that tag name and value.

Query to Get List of Resources With a Tag Set Based on Tag Name

(Find-AzureRmResource -TagName 'billingCategory').Name 

Output is a list of which resources have a specific tag assigned (regardless of the tag’s value).

You Might Also Like…

Naming Conventions in Azure

Setting Up Disk Encryption for a Virtual Machine with PowerShell

Let’s block ads! (Why?)

Blog – SQL Chick

Model Comparison and Merging for Analysis Services

Relational-database schema comparison and merging is a well-established market. Leading products include SSDT Schema Compare and Redgate SQL Compare, which is partially integrated into Visual Studio. These tools are used by organizations seeking to adopt a DevOps culture to automate build-and-deployment processes and increase the reliability and repeatability of mission critical systems.

Comparison and merging of BI models also introduces opportunities to bridge the gap between self-service and IT-owned “corporate BI”. This helps organizations seeking to adopt a “bi-modal BI” strategy to mitigate the risk of competing IT-owned and business-owned models offering redundant solutions with conflicting definitions.

Such functionality is available for Analysis Services tabular models. Please see the Model Comparison and Merging for Analysis Services whitepaper for detailed usage scenarios, instructions and workflows.

This is made possible with BISM Normalizer, which we are pleased to announce now resides on the Analysis Services Git repo. BISM Normalizer is a popular open-source tool that works with Azure Analysis Services and SQL Server Analysis Services. All tabular model objects and compatibility levels, including the new 1400 compatibility level, are supported. As a Visual Studio extension, it is tightly integrated with source control systems, build and deployment processes, and model management workflows.

Schema Diff2 Model Comparison and Merging for Analysis Services

Thanks to Javier Guillen (Blue Granite), Chris Webb (Crossjoin Consulting), Marco Russo (SQLBI), Chris Woolderink (Tabular) and Bill Anton (Opifex Solutions) for their contributions to the whitepaper.

Let’s block ads! (Why?)

Analysis Services Team Blog

Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)

Greetings P3 Nation! I’m here today to talk about maps, maps, globes, charts, topographical representations, and…well frankly I’m out of map synonyms. However there’s at least twice as many ways to visualize data with maps in Power BI as there are ways for me say the word map! I wonder how many times I can get the word map in this post…I’ll basically be an certified cartographer by the time I’m done writing this. wlEmoticon smile 5 Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)

Phone Map Power BI thumb 1 Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)

I’ll eventually give you an updated workbook that helps showcase some the ever expanding map features in Power BI. But before we get to that, let’s first get a quick refresher course on all the awesome ways we can visualize data with maps in Power BI.

Power BI currently offers MANY ways to visualize data with maps

Including the awesome Synoptic Panel custom map generator made by the folks over at SQLBI, but that’ll be a post for another day. My focus today will be on my four favorite map types built into Power BI Desktop natively, and one that is available for download as a custom visual. Each one offers it’s own pros and cons, but choice between them is largely left to personal taste. However I’ll do my best to explain each one in outline before letting you dive into the embedded report at the end of this post.

Bubble Map

Bubble Map thumb 1 Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)The grandfather of map visualizations in Power BI (literally)! It was the first map visual provided in Power BI Desktop back in its infancy. What’s really great is that this visual uses Bing maps data, so depending on the scale of your data (E.g. Country, State, City, Zip, etc…), when you zoom in the map will continually show more detail. The one caveat is it does require an internet connection to work (if you’re opening the Power BI Desktop file locally).

The size of the bubble is determined by a selected data field, and the color can either be a gradient based on a data field or colored based on a category. What’s fantastic is that a recent update now allows you to adjust the SIZE of the bubbles on the chart, using a slider to adjust the bubbles in the formatting settings. I was very happy when they added this!

Power BI Bubble Map thumb Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)

Pictured Left – Data bubbles by State and colored based on value by State:

Pictured Right – Data bubbles by State and colored based on Category:

Filled Map

Filled Map thumb Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)The second map offering made available in Power BI. It’s basically a kissing cousin to the Bubble Map, and I say this because they both use Bing maps data as their way of visually representing geography. It functions very similarly to the Bubble Map as well, with the key difference being that it displays the data by filling in the associated geographical area rather than using bubbles. Other than that difference, they’re basically identical.

Filled Map thumb 1 Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)

Pictured Left – Data filled in by State and colored based on value by State:

Pictured Right – Data filled in by State and colored based on Category:

Shape Map

Power BI Shape Visual thumb 3 Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)A semi-recent addition to the PBI map family, and in some ways an improvement over the Filled Map! The Shape Map let’s you visualize data with maps the same way the Filled Map does. However, it has a default list of shapes to select from (U.S. in our example), and also has the option to import additional shapes as well. It essentially forces a perspective (limited to the defined areas of the shape) rather than using Bing Maps. I’m using this one as the primary map in my report as I find it’s the cleanest one for representing my data by state. More details about this shape can be found on the Power BI Shape Map page.

Default Shape Map thumb 3 Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)

Pictured Left – Data filled by State and colored based on value by State:

Pictured Right – Data filled by State and colored based on Category:

ArcGIS Map

ArcGIS Visual thumb 1 Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)The most recent addition to the Power BI Map family. It’s supported by a company called Esri, and is a very feature rich map visual! What makes this visual stand out is that you can overlay whatever data you have with public geographical data such as demographics, weather, and even historical data. It’s highly customizable and offers multiple ways to visualize data with maps, and that’s even before you start adding the public data sets! Can you tell that I like this visual a lot? Because I do! wlEmoticon smile 5 Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)

Now I could easily spend an entire blog post JUST outlining all the ways to use this visual, but I’ll stick to the highlight reel. It can visualize data with maps using the bubble or fill method similar to the other map visuals, albiet with a few more customizations and tweaks. However, one of the unique features of this visual is the heat map option! Any of you familiar with Power Maps in Excel has probably seen this before…well now we have it in Power BI. I find this data visualization super useful in identifying data clustering based on location.

The icing on the cake is the fact that there’s a marketplace for the reference layer. So you’re not only given public demographics data, but also a plethora of custom layers to add to your hearts content. Pretty. Damn. Useful. More info can be found at Arcis Maps For Power BI or Take a tour of ArcGIS maps in Power BI.

The first image below showcases the map in three forms: Filled, Heat Map, and Bubbles. The second image shows all the important customizations you can apply to the map. I will say it’s a little tricky to find the settings menu, you have to click on the elipsis (…) in the upper right corner of the visual to find the edit option. It’s important to note that you need to enable ArcGIS Map in the options menu under Preview to see it as a visualization in Power BI Desktop.

ArcGIS Map thumb Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)

Pictured Left – Data filled by State and colored based on Category:

Pictured Center – Data breakouts by State and using a Heat Map based on value:

Pictured Right – Data breakouts by State and colored based on Category:

ArcGIS Map Settings thumb Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)

#1Base map: Let’s you pick the look of the base layer map:

#2  – Location type: You can pick between points (bubbles) or boundaries (filled):

#3Map theme: When location type is set to points this lets you pick between bubble types (including heat map):

#4 Reference layer: Lets you layer on various demographics data or access the ArcGIS marketplace for other datasets:

#5 Infographics: Lets you add in various public data cards to the upper right corner of the map:

Globe Map

Globe Map Icon thumb Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)Last on this list…and one I’d honestly rather skip, is the Globe Map. This visual is the only true 3D representation of map data available right now. It’s cool in concept but lacks customizations, so I don’t find it particularly useful compared to the other map visuals. It works by displaying data either as a column by location or by heat map, Neither of which is very customizable. This isn’t included in Power BI Desktop and needs to be downloaded from the Visuals Marketplace on the Power BI Website. More info about the visual can also be found on the Power BI Blog.

Globe Map thumb Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)

Now The Main Course (Power BI Embedded!)

Had enough maps yet? No? Well good because now I’m going to give you full access to the report with all these maps in it. As of the time of writing this, the ArcGIS Maps doesn’t support embedded (YET) so those report tabs don’t work on this page. However if you download the workbook those report tabs are fully functional.

The dataset I used came from a post written we wrote a while back, titled Red Nose Special: 24 Distinct Ways to Die. It focuses on US Census data from the last 10 years and provides some pretty fascinating “Aha!” moments pertaining to deaths by state. I won’t go into too much detail here about that post, rather let you read more using the link above. I also just counted…and I’ve managed to use the word maps 54 times in this post, impressive. With that we’ve come full circle in this article, so until next time readers. wlEmoticon smile 5 Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)

Download the Power BI Desktop (.pbix) Report Here

X

Get Your Files

Let’s block ads! (Why?)

PowerPivotPro

Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)

Greetings P3 Nation! I’m here today to talk about maps, maps, globes, charts, topographical representations, and…well frankly I’m out of map synonyms. However there’s at least twice as many ways to visualize data with maps in Power BI as there are ways for me say the word map! I wonder how many times I can get the word map in this post…I’ll basically be an certified cartographer by the time I’m done writing this. wlEmoticon smile 5 Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)

Phone Map Power BI thumb 1 Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)

I’ll eventually give you an updated workbook that helps showcase some the ever expanding map features in Power BI. But before we get to that, let’s first get a quick refresher course on all the awesome ways we can visualize data with maps in Power BI.

Power BI currently offers MANY ways to visualize data with maps

Including the awesome Synoptic Panel custom map generator made by the folks over at SQLBI, but that’ll be a post for another day. My focus today will be on my four favorite map types built into Power BI Desktop natively, and one that is available for download as a custom visual. Each one offers it’s own pros and cons, but choice between them is largely left to personal taste. However I’ll do my best to explain each one in outline before letting you dive into the embedded report at the end of this post.

Bubble Map

Bubble Map thumb 1 Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)The grandfather of map visualizations in Power BI (literally)! It was the first map visual provided in Power BI Desktop back in its infancy. What’s really great is that this visual uses Bing maps data, so depending on the scale of your data (E.g. Country, State, City, Zip, etc…), when you zoom in the map will continually show more detail. The one caveat is it does require an internet connection to work (if you’re opening the Power BI Desktop file locally).

The size of the bubble is determined by a selected data field, and the color can either be a gradient based on a data field or colored based on a category. What’s fantastic is that a recent update now allows you to adjust the SIZE of the bubbles on the chart, using a slider to adjust the bubbles in the formatting settings. I was very happy when they added this!

Power BI Bubble Map thumb Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)

Pictured Left – Data bubbles by State and colored based on value by State:

Pictured Right – Data bubbles by State and colored based on Category:

Filled Map

Filled Map thumb Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)The second map offering made available in Power BI. It’s basically a kissing cousin to the Bubble Map, and I say this because they both use Bing maps data as their way of visually representing geography. It functions very similarly to the Bubble Map as well, with the key difference being that it displays the data by filling in the associated geographical area rather than using bubbles. Other than that difference, they’re basically identical.

Filled Map thumb 1 Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)

Pictured Left – Data filled in by State and colored based on value by State:

Pictured Right – Data filled in by State and colored based on Category:

Shape Map

Power BI Shape Visual thumb 3 Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)A semi-recent addition to the PBI map family, and in some ways an improvement over the Filled Map! The Shape Map let’s you visualize data with maps the same way the Filled Map does. However, it has a default list of shapes to select from (U.S. in our example), and also has the option to import additional shapes as well. It essentially forces a perspective (limited to the defined areas of the shape) rather than using Bing Maps. I’m using this one as the primary map in my report as I find it’s the cleanest one for representing my data by state. More details about this shape can be found on the Power BI Shape Map page.

Default Shape Map thumb 3 Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)

Pictured Left – Data filled by State and colored based on value by State:

Pictured Right – Data filled by State and colored based on Category:

ArcGIS Map

ArcGIS Visual thumb 1 Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)The most recent addition to the Power BI Map family. It’s supported by a company called Esri, and is a very feature rich map visual! What makes this visual stand out is that you can overlay whatever data you have with public geographical data such as demographics, weather, and even historical data. It’s highly customizable and offers multiple ways to visualize data with maps, and that’s even before you start adding the public data sets! Can you tell that I like this visual a lot? Because I do! wlEmoticon smile 5 Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)

Now I could easily spend an entire blog post JUST outlining all the ways to use this visual, but I’ll stick to the highlight reel. It can visualize data with maps using the bubble or fill method similar to the other map visuals, albiet with a few more customizations and tweaks. However, one of the unique features of this visual is the heat map option! Any of you familiar with Power Maps in Excel has probably seen this before…well now we have it in Power BI. I find this data visualization super useful in identifying data clustering based on location.

The icing on the cake is the fact that there’s a marketplace for the reference layer. So you’re not only given public demographics data, but also a plethora of custom layers to add to your hearts content. Pretty. Damn. Useful. More info can be found at Arcis Maps For Power BI or Take a tour of ArcGIS maps in Power BI.

The first image below showcases the map in three forms: Filled, Heat Map, and Bubbles. The second image shows all the important customizations you can apply to the map. I will say it’s a little tricky to find the settings menu, you have to click on the elipsis (…) in the upper right corner of the visual to find the edit option. It’s important to note that you need to enable ArcGIS Map in the options menu under Preview to see it as a visualization in Power BI Desktop.

ArcGIS Map thumb Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)

Pictured Left – Data filled by State and colored based on Category:

Pictured Center – Data breakouts by State and using a Heat Map based on value:

Pictured Right – Data breakouts by State and colored based on Category:

ArcGIS Map Settings thumb Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)

#1Base map: Let’s you pick the look of the base layer map:

#2  – Location type: You can pick between points (bubbles) or boundaries (filled):

#3Map theme: When location type is set to points this lets you pick between bubble types (including heat map):

#4 Reference layer: Lets you layer on various demographics data or access the ArcGIS marketplace for other datasets:

#5 Infographics: Lets you add in various public data cards to the upper right corner of the map:

Globe Map

Globe Map Icon thumb Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)Last on this list…and one I’d honestly rather skip, is the Globe Map. This visual is the only true 3D representation of map data available right now. It’s cool in concept but lacks customizations, so I don’t find it particularly useful compared to the other map visuals. It works by displaying data either as a column by location or by heat map, Neither of which is very customizable. This isn’t included in Power BI Desktop and needs to be downloaded from the Visuals Marketplace on the Power BI Website. More info about the visual can also be found on the Power BI Blog.

Globe Map thumb Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)

Now The Main Course (Power BI Embedded!)

Had enough maps yet? No? Well good because now I’m going to give you full access to the report with all these maps in it. As of the time of writing this, the ArcGIS Maps doesn’t support embedded (YET) so those report tabs don’t work on this page. However if you download the workbook those report tabs are fully functional.

The dataset I used came from a post written we wrote a while back, titled Red Nose Special: 24 Distinct Ways to Die. It focuses on US Census data from the last 10 years and provides some pretty fascinating “Aha!” moments pertaining to deaths by state. I won’t go into too much detail here about that post, rather let you read more using the link above. I also just counted…and I’ve managed to use the word maps 54 times in this post, impressive. With that we’ve come full circle in this article, so until next time readers. wlEmoticon smile 5 Stick a Drop Pin in It: Visualize Data With Maps (DAX Reanimator Series: Part 2)

Download the Power BI Desktop (.pbix) Report Here

X

Get Your Files

Let’s block ads! (Why?)

PowerPivotPro

What’s new in SQL Server 2017 RC1 for Analysis Services

The RC1 public preview of SQL Server 2017 is available here! It includes Dynamic Management View improvements for tabular models with compatibility level 1200 and 1400.

DMVs are useful in numerous scenarios including the following.

  • Exposing information about server operations and health.
  • Documentation of tabular models.
  • Numerous client tools use DMVs for a variety of reasons. For example, BISM Normalizer uses them to perform impact analysis for incremental metadata deployment and merging.

RC1 rounds off the DMV improvements introduced in CTP 2.0 and CTP 2.1.

DISCOVER_CALC_DEPENDENCY now works with 1200 and 1400 models. 1400 models show dependencies between M partitions, M expressions and structured data sources.

Further enhancements in RC1 include the following for 1200 (where applicable) and 1400 models.

  • Named dependencies result from DAX or M expressions that explicitly reference other objects. RC1 introduces named dependencies for DAX in addition to DAX data dependencies. Previous versions of this DMV returned only data dependencies. In many cases a dependency is both named and data. RC1 returns the superset.
  • In addition to dependencies between M partitions, M expressions and structured data sources, dependencies between provider data sources and non-M partitions (these are the traditional partition and data source types for tabular models) are returned in RC1.
  • The following new schema restrictions have been introduced to allow focused querying of the DMV. The table below shows the intersection of the schema restrictions with the type of objects covered.
    • KIND with values of ‘DATA_DEPENDENCY’ or ‘NAMED_DEPENDENCY’.
    • OBJECT_CATEGORY with values of ‘DATA_ACCESS’ or ‘ANALYSIS’.
  KIND OBJECT_CATEGORY
  DATA_DEPENDENCY NAMED_DEPENDENCY DATA_ACCESS ANALYSIS
Mashup
Provider data source & non-M partitions
DAX named dependencies
Other data dependencies
  • Mashup dependencies are dependencies between M partitions, M expressions and structured data sources. They are named, M-expression based, and only apply to 1400 models.
  • Provider data source & non-M partitions are dependencies between traditional partitions and provider data sources. They are based on properties in tabular metadata rather than expression based, so are not considered “named”. They are available for 1200 and 1400 models.
  • DAX named dependencies are explicit named references in DAX expressions. They are available for 1200 and 1400 models.
  • Other data dependencies are data dependencies for DAX expressions and other types of data dependencies such as hierarchies and relationships. To avoid potential performance issues, data dependencies from DAX measures are only returned when using a QUERY schema restriction. They are available for 1100, 1103, 1200 and 1400 models.

1100 and 1103 models only return other data dependencies, and they ignore the new schema restrictions.

DAX data dependencies

DAX data dependencies and DAX named dependencies are not necessarily the same thing. For example, a calculated table called ShipDate with a DAX formula of “=DimDate” clearly has a named dependency (and data dependency) on the DimDate table. It also has data dependencies on the columns within DimDate, but these are not considered named dependencies.

Example: [KIND]=’NAMED_DEPENDENCY’

The following query returns the output shown below. All DAX and M expression named references in the model are included. These can originate from calculated tables/columns, measures, M partitions, row-level security filters, detail rows expressions, etc.

SELECT * FROM SYSTEMRESTRICTSCHEMA
    ($  SYSTEM.DISCOVER_CALC_DEPENDENCY, [KIND] = 'NAMED_DEPENDENCY')

calc dep 1 What’s new in SQL Server 2017 RC1 for Analysis Services

Example: [KIND]=’DATA_DEPENDENCY’

The following query returns the output shown below. Some data dependencies happen to also be named dependencies, in which case they are returned by this query and the one above with a NAMED_DEPENDENCY schema restriction.

SELECT * FROM SYSTEMRESTRICTSCHEMA
    ($  SYSTEM.DISCOVER_CALC_DEPENDENCY, [KIND] = 'DATA_DEPENDENCY')

calc dep 2 data dep What’s new in SQL Server 2017 RC1 for Analysis Services

Example: [OBJECT_CATEGORY]=’DATA_ACCESS’

The following query returns the output shown below. Partitions, M expressions and data source dependencies are included.

SELECT * FROM SYSTEMRESTRICTSCHEMA
    ($  SYSTEM.DISCOVER_CALC_DEPENDENCY, [OBJECT_CATEGORY] = 'DATA_ACCESS')

calc dep 3 data access What’s new in SQL Server 2017 RC1 for Analysis Services

Example: [OBJECT_CATEGORY]=’ANALYSIS’

The following query returns the output shown below. The results of this query are mutually exclusive with the results above with a DATA_ACCESS schema restriction.

SELECT * FROM SYSTEMRESTRICTSCHEMA
    ($  SYSTEM.DISCOVER_CALC_DEPENDENCY, [OBJECT_CATEGORY] = 'ANALYSIS')

calc dep 4 analysis1 What’s new in SQL Server 2017 RC1 for Analysis Services

RC1 provides improvements for this DMV, which is used by various client tools to show measure dimensionality. For example, the Explore feature in Excel Pivot Tables allows the user to cross-drill to dimensions related to the selected measures.

RC1 corrects the cardinality columns, which were previously showing incorrect values.

SELECT * FROM $  System.MDSCHEMA_MEASUREGROUP_DIMENSIONS;

Mdschema MeasureGroup Dimensions What’s new in SQL Server 2017 RC1 for Analysis Services

Download now!

To get started, download SQL Server 2017 RC1. The latest release of the Analysis Services VSIX for SSDT is available here. VSIX deployment for Visual Studio 2017 is discussed in this blog post.

Be sure to keep an eye on this blog to stay up to date on Analysis Services!

Let’s block ads! (Why?)

Analysis Services Team Blog

Two days of Power BI Content at the User Group World Tour

Emerge a Power BI super star.

Powered by your local Power BI User Group (PUG), the Power BI World Tour will be unprecedented access to premium Power BI content designed by your local industry experts. This two-day event will cover technical content designed for the Power BI Analyst, Developer/IT Admin, and new professional. Grow your world of experience by attending our training, expand your network of connections, and get ready to become the Power BI super star you were meant to be.

Starting in August, the tour will visit seven cities throughout the World. Register by August 07, 2017 to take advantage of the early bird discount. Cities and dates include: 

  • London- August 23-24
  • Copenhagen- August 28-29
  • New York- September 19-20
  • Toronto – October 04-05
  • San Francisco – October 04-05
  • Chicago – October 11-12
  • Sydney – November 06-07

Interested in speaking? Call for proposals for New York, Toronto, San Francisco, Chicago and Sydney will be accepted through July 28. See the details at: https://www.pbiusergroup.com/participate/pbi-world-tour

Let’s block ads! (Why?)

Microsoft Power BI Blog | Microsoft Power BI

Congratulations to the Featured Data Stories for May and June!

We put out the call to you a couple of months ago to create fantastic reports for the Data Stories Gallery, and we were amazed by your submissions! Congratulations to the grand winner and runners-up from May and June.

The theme for May was data storytelling. These entries were judged by the Microsoft Data Journalism Team. Microsoft believes that data enables reporters, bloggers, broadcasters and analysts alike to tell unique and interesting stories. Data visualization is a powerful tool that can help form a narrative and present valuable insights in a compelling way, and with Microsoft Power BI, writers can emphasize messages and engage audiences through interactive and data rich stories.

The way we tell stories has changed, in part, due to the ever-growing volume and availability of data. Media outlets are tasked with providing credible, data-backed stories that enable readers to explore the personal angle of the story. This demand for interactive data stories is pervasive across beats, but a lot of journalists and media outlets don’t have the time, resources, or tools to create compelling interactive data visualizations. We hear from a lot of journalists that data stories are often a high-risk, expensive investment for newsrooms because of limited bandwidth, the lack of tools or technical expertise to create interactive graphics, and the challenge of drilling down into local or relevant stories from national or global datasets.

Microsoft has been pioneering a new, simple and scalable solution to overcome these barriers with the Microsoft Data Journalism Program and Microsoft Power BI. With Power BI publish to web, writers can publish interactive Power BI data visualizations with their stories and reach an unlimited number of readers. Since this capability was released, Microsoft has been working with journalists and media organizations to understand how to realize this opportunity. To learn more about the Microsoft Data Journalism Program, visit the website or email them with questions.

May Grand Winner:

Susie’s report displays a very clear, simple story. There are good insights, with the ability to see national, regional, and local drill down.

 Congratulations to the Featured Data Stories for May and June!

June Grand Winner:

Alex’s report not only shows good design, but it’s also a very helpful way for Power BI users to find the right custom visual for their project.

 Congratulations to the Featured Data Stories for May and June!

Nine other reports will also be marked as Featured. Congratulations to the following reports (in no particular order):

May Featured Data Storytelling Reports

June Featured Reports


Submit your story

Want to see your work become a Featured story? Post it to the Data Stories Gallery, and then tweet a link with the hashtags #powerbi #datastory. At the end of each month we’ll review all tweeted stories, and choose several to become featured. Featured stories will be selected for telling a compelling story, being original and creative, and effectively using Power BI.

The author of the story that we pick as the grand winner for each month will win a Power BI t-shirt and a badge of recognition for their Community profile.

Let’s block ads! (Why?)

Microsoft Power BI Blog | Microsoft Power BI

Power BI On-premises data gateway – July Update is now available!

We’re glad to announce that the July Update for the Power BI On-premises data gateway is now available.

This update includes the following enhancements:

  • Support for Snowflake connector
  • Support for SAP BusinessWarehouse DirectQuery connector
  • Updated version of the Mashup Engine

You can download this new version following this link and continue reading below for more details about each enhancement.

d9d3c66c bc52 41c6 828b ddafa2e1277e Power BI On premises data gateway – July Update is now available!

Support for Snowflake connector

With this month’s Power BI Desktop update the Snowflake connector has been declared Generally Available. This means that the connector is recommended for use in Production scenarios. This support includes the ability to refresh content for reports published to the Power BI service, including Import and DirectQuery connectivity modes. After you install this month’s Gateway update, you will be able to register your Snowflake instances in the “Add Data Source” page within the Power BI Service.

Support for SAP BusinessWarehouse DirectQuery connector

DirectQuery connectivity mode was added to the SAP BusinessWarehouse connector as a Beta feature in the Power BI Desktop June update. This month we’re enhancing this Beta connector by also allowing DirectQuery reports to be enabled in the Power BI Service. We’re very excited about this capability being rolled out to customers and encourage everyone using the SAP BusinessWarehouse connector to try it out and share feedback with us.

Updated version of the Mashup Engine

Last but not least, this Gateway update includes the same version of the Mashup Engine as the Power BI Desktop update released last week. This will ensure that the reports that you publish to the Power BI Service and refresh via the Gateway will go through the same query execution logic/code as in the latest Power BI Desktop version. Note that there are some Beta connectors (such as IBM Netezza or Spark) still not supported outside of Power BI Desktop. Please refer to specific connector documentation for more details or contact us if you have any questions.

That’s all for this month’s Gateway update. We hope that you find these enhancements useful and continue sending us feedback for what new capabilities you’d like to see in the future.

7c920e73 6a93 4268 939b 9e4bb83f17f6 Power BI On premises data gateway – July Update is now available!

Additional resources:

· Try Power BI

· Follow @MSPowerBI on Twitter

· Join the conversation at the Power BI Community

Let’s block ads! (Why?)

Microsoft Power BI Blog | Microsoft Power BI