Filtering in Power Query

Last week I was teaching a Power BI Workshop. On the final day while attendees were building their own solutions, one participant named Linda says to me:  "Melissa, Power Pivot is missing some data."  We proceeded to trace back through her Power Query operations to determine where the data got eliminated.

She shows me the final Power Query and points to the column header and says "See, there's no filters here. So why didn't it end up in Power Pivot?"  Since this was a very interesting observation from a very bright gal, I thought I'd share it.

To illustrate, first let's set up a quick little list of Dept Numbers and Names in the first step of our Query Editor:

In the second step, let's add a filter to remove one Dept Number. 

In our third step after the filter, let's do something. Anything. For grins I merged two columns to produce a concatenated column of Dept Name - Number. Really though, this third step only exists to prove a point that there is no longer an indication in the column header that a filter occurred on this column in a previous step:

I often say that each step in the Query Editor displays the state of the data at that point in time. Since the filter occurred on a previous step, the rows filtered out are now really gone in the next step - the state of the data changed. This is why there's no longer an indication a filter occurred in a previous step. Power Query behavior isn't like Excel where the rows are still there just hidden; rather, the rows are truly filtered out and won't progress further along in the steps or into Power Pivot.

So, having said all that, the two main takeaways are:

  • Filters in Power Query aren't controlling what you see on the screen; they truly exclude the data from progressing any farther.
  • There's not a visual indication in Power Query on steps after a filter operation has occurred, so keep an eye out for that if you are doing some data exploration as you build your steps.

Keeping Up with New Information with RSS, Twitter, and Read Later

I wanted a solution to view the RSS feeds of my favorite bloggers in the same place as the items I've tagged in my 'read later' app. This single place needs to be available on my Windows 8 device (a Surface Pro 3 which I dearly love), preferably in an app rather than "just" a web browser.  I've found a solution that's been working well, so I thought I'd pass it along.

Feedly:  RSS feeds to follow my favorite bloggers

Pocket:  Read Later app to compile links - these most often come from links I see on Twitter

Readiy:  Windows 8.1 app to display Feedly and Pocket items in one place

My "Pocket Reading List" is just another RSS feed for Feedly to pick up which is how they get compiled together. Here's a sample of what things look like within Readiy:

As you can see, the Pocket items don't display the same preview as the RSS items do, nor the author, but that's ok. At least they're in one place which gives me a much better chance of actually reading the content.

Once in a while I'll also send a YouTube video link to Pocket. Those don't show up inside of Readiy but they do in the Pocket interface. That's not a big deal to me since I save a lot, lot more articles than videos.

I'm sure there's many different ways to accomplish this same thing. This method was very simple to set up and since the components integrate with my iPhone and Windows 8.1 tablet/laptop, it's been working well for me. 

Monitoring the SSAS Connector in the Power BI Preview

Welcome to part 3 of a 4-part series on using the SSAS Connector with the new Power BI Preview. This information is as of early March 2015 and is subject to change as the Power BI Preview evolves.

Part I:   Overview of Using Analysis Services Data in the new Power BI Preview

Part II:  What You Need to Set Up the SSAS Connector in the new Power BI Preview

Part III: Monitoring the SSAS Connector in the Power BI Preview (you are here)

Part IV:  Connecting to Analysis Services From Power BI Designer and Excel  {Coming Soon}

What's Going On When the SSAS Connector Is Running?

As we discussed in Part 1, the SSAS Connector allows Power BI to display data that is stored in an Analysis Services Tabular model.  What's happening:

  • Reports:  Refreshed on-demand from user interaction
  • Dashboards:  Refreshed automatically by the system

A Power BI report issues queries back to SSAS Tabular model when a user interacts with the report, field list, or filter.

A Power BI dashboard issues queries back to SSAS Tabular model every 10 minutes to keep the dashboard current. It does this without a refresh schedule being enabled (which isn't necessary in Power BI for an SSAS Tabular model anyway). 

 

If you have a lot of users and a lot of dashboards, that's a lot of queries happening automatically. At this time, the 10-minute refresh interval is not configurable -- that's one of my wish list items. Most commonly I see an SSAS tabular model being refreshed daily by a job which makes Power BI refreshes every 10 minutes a slight bit of overkill. So the ability to scale this back would be very helpful to manage the SSAS server load.

Monitoring SSAS Queries Being Sent to Power BI with SQL Server Profiler

First, a quick word about Profiler. It is deprecated for the SQL Server Engine, but not officially for Analysis Services yet according to this MSDN documentation. However, many database professionals are starting to rely more on Extended Events instead of Profiler for both the DB Engine and SSAS because Extended Events are less taxing on the system and have far more events that can be captured. Since Profiler is familiar let's start with that though. In the next section we'll show Extended Events. For purposes of this first example, we'll keep it simple and use the Profiler GUI for our trace (with a Production server, you obviously want to do that cautiously so as not to significantly impact performance).

If you fire up Profiler and start a trace on the Analysis Services instance, look for three things: the NTUserName, the EffectiveUserName associated to the Query Begin event, and the ApplicationName which is Power BI.

In the above screen shot:

  • NTUserName:  This is the account with SSAS administrator permissions used when the SSAS Connector was configured. Preferably this is a domain service account, not associated to a user, with a password that doesn't expire.
  • EffectiveUserName: When the active user is also the original author, this is the active user. When the active user is viewing a dashboard that's been shared with them, the EffectiveUserName is still the original author -- not the active user.
 

I talked about the issue with EffectiveUserName and shared dashboards in Part I of this series. Am holding out hope that this behavior changes very soon so security is honored in all cases.

Monitoring SSAS Queries Being Sent to Power BI with Extended Events

Since there's not yet a GUI for setting up Extended Events in SSAS, the starting place is an XMLA query window in Management Studio. Below is an example of a small trace I let run on the SSAS server for a couple of minutes.  I did borrow Bill Anton's script as a starting point for this example. Both Mark Vallincourt and Bill Anton have much more thorough discussions about this topic if you want to read more.

The only thing I cared about this time was tracing was the QueryBegin event.  

<Create
xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"
xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"
xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"
xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"
xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
<ObjectDefinition>
 <Trace>
<ID>9</ID>
<Name>QueryBeginTracing</Name>
<ddl300_300:XEvent>
 <event_session 
  name="SSAS_9_10"
dispatchLatency="1"
maxEventSize="4"
maxMemory="4"
memoryPartitionMode="none"
eventRetentionMode="allowSingleEventLoss"
trackCausality="true">
<event package="AS" name="QueryBegin" />
<target package="Package0" name="event_file">
<parameter name="filename" value="SSASTrace_QueryBegin.xel" />
</target>
 </event_session>
</ddl300_300:XEvent>
 </Trace>
</ObjectDefinition>
</Create>

After the trace starts, it creates an .xel file in your SSAS log folder.

If you double-click the .xel file, it'll open for viewing in Management Studio.  Here's what it looks like when you first open it:

You can start right-clicking detail items to display as columns in the table in the top half of the screen.

SSASExtendedEvent4.png

If you expand RequestProperties, you'll see the EffectiveUserName there. So here in the Extended Events trace we have all the same information we saw in Profiler, plus more, just in a different format. You can also trace more events if you'd like of course. Info on SSAS trace events can be found here.

Oh, and don't forget to stop your traces from running.

<Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<TraceID>9</TraceID>
</Object>
</Delete>

If you need to double check what's running against your SSAS Tabular instance, check the Discover_Traces DMV. In the following screen shot, you can see I've got two traces running: one is my Profiler session, and the other is my Extended Events session. The Flight Recorder will be there too if you have it enabled in your SSAS server properties.

SELECT * FROM $system.discover_traces

Monitoring SSAS Server Resources Associated with the SSAS Connector

Here's where you use the same tools and techniques you use to monitor your server health, whether it be PerfMon, Activity Monitor, a third party tool, and so forth. A few things I've observed from the default installation of the SSAS Connector:

Windows Events

Events related to the SSAS Connector are classified as the "Data Management Gateway" log.

Reminder: The SSAS Connector cannot currently co-exist on the same machine as a Data Management Gateway (from Power BI for O365).  So even though the log naming of services and events are the Data Management Gateway, it's really the SSAS Connector. I guess this means the SSAS Connector is a special type of Data Management Gateway, but different than the one associated with Power BI for O365.

Windows Service

The Windows Service associated with the SSAS Connector is run by: NT SERVICE\DIAHostService.

 

Local Group

When the SSAS Connector is installed, a local group called "Data Management Gateway Users" is created. The person who installed and configured the SSAS Connector is who is initially added to the group. So in my case, I configured the SSAS Connector so I'm the person initially added to the group (even though I specified the PowerBISvcAccount credentials as the administrator). 

SSASConnector_c.png

Firewall Ports

The SSAS Connector installation sets up the following inbound rules associated to DIAHost.exe and DIAWP.exe:

Managing the Password for the SSAS Connector

Lots of organizations have security policies which dictate user passwords change on a regular interval. If the SSAS Connector were set up with a user's credentials, and the password expires, then the SSAS Connector stops functioning. That's because the ID and PW are stored by the SSAS Connector. 

 

Therefore, unless your policy dictates otherwise you may want to set up the SSAS Connector with a domain service account with a password that doesn't expire frequently. 

Part I:   Overview of Using Analysis Services Data in the new Power BI Preview

Part II:  What You Need to Set Up the SSAS Connector in the new Power BI Preview

Part III: Monitoring the SSAS Connector in the Power BI Preview (you are here)

Part IV:  Connecting to Analysis Services From Power BI Designer and Excel  {Coming Soon}

How to Deploy Master Data Services Models Between Environments

If you plan to promote a SQL Server MDS (Master Data Services) model from Dev and/or QA to Production, there's a few particulars you need to know to get it right. In this entry I'm focusing on using the "MDSModelDeploy" utility since it offers the most flexibility. Below I will discuss a two-tier MDS environment, Development and Production, just to keep things simple.

Three Options for MDSModelDeploy

DeployClone: Appropriate for the first time you promote to a new environment, such as from Dev to Prod. You can use DeployClone to deploy structure only, or structure + data. Keeps the unique identifier (MUID) consistent between environments, which is necessary if you want to keep models in sync between Dev and Prod.

DeployUpdate: Appropriate for when you want to push an update to a new environment, such as a new column in an entity. This can deploy structure changes only (ex: a new column), or structure + data (ex: refreshing Dev from Prod occasionally). Will only work when the unique identifier (MUID) is the same between source and destination.

DeployNew: Not used that frequently; only appropriate for creating a brand new model using another model as a starting point. Creates a new unique identifier (MUID) for the new model. This is *not* the choice you want to use for promoting to Production from Dev.

Checking the Service Name

Before you do any deployments, you need to know the Service Name(s), especially if you have more than one MDS instance running on the same server. In most cases this is MDS1 if you only have one MDS web app & one MDS database running on the SQL Server instance.

MDSModelDeploy listservices

In my local environment, I use several MDS databases for testing.  From "listservices" we see that my MDS_Development is referred to as MDS3, and MDS_Production is MDS4.

Basic Steps for Using MDSModelDeploy

These steps will be standard for each of the examples in each section below. If MDS is installed locally (i.e., if the MDSModelDeploy utility exists on your local development box), you can omit some of the RDP steps. Also, if any shares to the MDS Configuration paths exist, you can simplify and omit RDP steps that way too. I went ahead and listed the most basic way to get it done, knowing you can simplify once you're familiar with where things are located.

1. Remote Desktop into source server where MDS is installed (ex: Dev). 

2. Open an elevated command prompt (CMD > Run as Administrator).

3. In the command prompt, change the directory to the path where the MDSModelDeploy utility resides.  

CD C:\Program Files\Microsoft SQL Server\120\Master Data Services\Configuration

4. Run MDSModelDeploy to generate a package from the source MDS server. Locate the deployment package on a file share (this saves you from copying and pasting it from one server's configuration folder to the other). See examples below for generating a package.

5. Remote Desktop into target server where MDS is installed (ex: Prod).

6. Open an elevated command prompt (CMD > Run as Administrator).

7. In the command prompt, change the directory to the path where the MDSModelDeploy utility resides.  

CD C:\Program Files\Microsoft SQL Server\120\Master Data Services\Configuration

8. Run MDSModelDeploy to deploy the package on the destination MDS server. This will point to the share used in step 4. See examples below for deploying a package.

9. Run validation for the model just deployed.

DECLARE @ModelName NVARCHAR(50) = 'ModelName'
DECLARE @Model_Id INT
DECLARE @UserName NVARCHAR(50) = 'Domain\User'
DECLARE @User_Id INT
DECLARE @Version_ID INT

SET @User_Id = (SELECT ID FROM mdm.tbluser u WHERE u.UserName = @UserName)
SET @Model_Id = (SELECT TOP 1 model_id FROM mdm.viw_system_schema_version WHERE Model_Name = @modelname)
SET @Version_ID = (SELECT MAX(ID) FROM mdm.viw_system_schema_version WHERE Model_ID = @Model_Id)

EXEC mdm.udpValidateModel @user_id, @Model_ID, @Version_ID, 1

10. Reinstate security for users (if a clone was performed) in the Users and Group Permissions area.

11. Double check all subscription view names are correct in the Integration Management area. I've seen the subscription views come through after a DeployClone, but with new prefixes with the model name in front which needs the prefix removed so that ETL processes can continue to use the subscription views.

Example: Initial Deployment of MDS Model

This technique uses DeployClone to ensure that Dev and Prod can be kept in sync with the same unique identifier (MUID) for the model.

Source MDS server (structure only):

MDSModelDeploy createpackage -model ModelName -service MDS1 -package "X:\Path\FileName"

Source MDS server (including data):

MDSModelDeploy createpackage -model ModelName -service MDS1 -version VERSION_1 -package "X:\Path\FileName" -includedata

Target MDS server:

MDSModelDeploy deployclone -service MDS1 -package "X:\Path\FileName.pkg"

Following the DeployClone, you'll also need to validate the model. Run the validation script from SSMS (see script in step 9 above).

You also need to reinstate permissions for users to see the model. This is done from the User and Group Permissions area in Master Data Manager.

I've noticed sometimes it takes a couple of minutes for everything to show up in the Master Data Manager web interface after the deployment.

Note that after this initial DeployClone, you will always do a DeployUpdate from this point forward. If you try to do another DeployClone you'll get an error that says "A clone of the model cannot be created. There is already a model with the same name or MUID." So in this case, do a DeployUpdate instead.

Example: Deploying MDS Model Changes

This technique uses DeployUpdate which requires the same unique identifier (MUID) for each model. This is often used for promoting a structural model change from Dev to Prod some time after an initial DeployClone was done. Or, the "includedata" option can be used for refreshing Dev from Prod occasionally (i.e., assuming data stewards have been keeping the data in Prod up to date).

Source MDS server (structure only):

MDSModelDeploy createpackage -model ModelName -service MDS1 -package "X:\Path\FileName"

Source MDS server (including data):

MDSModelDeploy createpackage -model ModelName -service MDS1 -version VERSION_1 -package "X:\Path\FileName" -includedata

Target MDS server:

MDSModelDeploy deployupdate -service MDS1 -package "X:\Path\FileName.pkg"

Following the DeployUpdate, you'll also need to validate the model. Run the validation script from SSMS (see script in step 9 above).

Note that if you did a DeployNew initially, then try to do a DeployUpdate, you'll get an error "The model cannot be updated. There is no match for a model with the same name and MUID." If this happens, you'll need to do a DeployClone from the best environment, delete the model from the other environments, deploy the clone, the re-implement any changes and retry the DeployUpdate. 

Why It's Important to Validate After MDS Deployment

After deployment with data, the members in the model are deployed but not yet validated. This can be seen in the Master Data Manager web interface:

 

After you run a validation on the model, then the members will show validated in the interface:

 

Validation like this should also occur after any staging ETL processes are run. Here's the script to run the MDS stored procedure to validate a model. Note that the two variables at the top need a value input.

DECLARE @ModelName NVARCHAR(50) = 'Product'
DECLARE @Model_Id INT
DECLARE @UserName NVARCHAR(50) = 'COATES_HPENVY\Melissa'
DECLARE @User_Id INT
DECLARE @Version_ID INT

SET @User_Id = (SELECT ID FROM mdm.tbluser u WHERE u.UserName = @UserName)
SET @Model_Id = (SELECT TOP 1 model_id FROM mdm.viw_system_schema_version WHERE Model_Name = @modelname)
SET @Version_ID = (SELECT MAX(ID) FROM mdm.viw_system_schema_version WHERE Model_ID = @Model_Id)

EXEC mdm.udpValidateModel @user_id, @Model_ID, @Version_ID, 1

Deploying Only a Subset of a Model

The MDSModelDeploy utility deploys an entire model. What if you really only want to push one entity and be extra cautious not to touch anything else? That's where the MDSPackageEditor.exe comes in handy.

You launch the MDSPackageEditor after you have created a package from the source MDS server. Then you de-select the individual elements you want to exclude, and it saves the package as a different file name. Then you'd continue on with the deployment to the destination server like normal. Using the MDSPackageEditor.exe is much easier than editing the .pkg XML file manually.

Logging MDS Deployments

By default, trace file logging of MDS deployments is not enabled. Instructions for how to enable it can be found here: Enabling Logging to Troubleshoot MDSModelDeploy.exe. (Don't forget to back up the original config file before editing it, just to be safe.)

Note that in SQL Server 2014, Cumulative Update 6 is required in order to use the trace file. Prior to CU6, a trace file doesn't get generated even if it's been enabled in the config file. This is documented here:  No MDSTrace.log file is generated after you enable tracing in the MDS 2014 web.config file. Installing CU6 did indeed solve the problem for me.

Brief example of enabling logging in the MDSModelDeploy.exe.config file:

Where to locate the MDSModelDeployTrace.log file: