Power Pivot Update and Issue with Relationships Between Tables

Yesterday the Power BI team released a fantastic new update that improves the usability of Power Pivot inside of Excel. I'm thrilled with what I've seen so far as it removes a lot of the delays when making changes to the data model.

However...I have noticed one issue since the update. This morning I created a simplified data model in order to create a Power View demo for a customer who's not yet familiar with Power View's interactivity behavior. When putting together a sample Power View report I noticed the dreaded "Relationships between tables may be needed" message. 

 

There are times this message is accurate and expected (such as when you're purposely using disconnected tables). However, in this case, I knew something was wrong. Upon checking the Diagram View for the model, sure enough, the arrow was pointed in the wrong direction.

Prior to the update, I believe Power Pivot would reverse the arrow of the direction for you if it detected that you dragged it the "wrong" way -- i.e., it wants to point to the dimension table / lookup table / table with distinct set of values. So, I deleted the relationship and dragged it the correct direction and all was well.

So, keep an eye out for that behavior on the direction of the relationships.

You'll know you have the December update with the performance improvements if you are on 15.0.4675.1002 (File > Account).

 

Update 12/18/2014: It seems the "Relationships" message is also displaying sometimes when it's not truly applicable - ex: I'm seeing it when one explicit calculated measure from a single table is the only thing that has been added to a pivot table & I'm absolutely certain the relationships are defined accurately. 

When To Choose Analysis Services vs. Power Pivot?

The conversation about when to use SSAS vs. Power Pivot comes up a lot. Power Pivot is the data modeling component within the Power BI set of tools, and is suitable for certain self-service BI situations. Here's my top reasons when you may consider selecting Analysis Services instead, or when to upsize a Power Pivot model to an SSAS Tabular model.

This information is as of October 2014 and will definitely change as Power BI evolves and matures.

1. Row-Level Security. If the data returned needs to be secured based on what the user is permitted to see, that requirement usually justifies Analysis Services. Power Pivot supports file level security (i.e., either you see the data in the file or you don't). If you have a very small number of user groups, you could possibly still use Power Pivot. For example, let's say you have two divisions. You could secure the two division files differently to accomplish the need, and create two data sources in the Power BI Admin Center which use different credentials for the refresh. The tradeoff here is you'll have multiple workbooks to refresh, and any changes to reports and calculations and such would have to be done in each workbook.

2. High Data Volumes. Currently Power BI supports files at 250MB after the xVelocity compression is applied. Analysis Services can support much larger data volumes.

3. Minimal Data Latency. Currently you can schedule a refresh for a Power Pivot model in Power BI as often as once per day. Analysis Services processing can be scheduled in order to meet near-real-time needs if necessary.

4. Workbooks as a Data Source / Centralization of Data Model. Reports in Power BI currently require an embedded Power Pivot model. Now, this intermediary model can get data from many, many sources -- but currently the Power View, Power Map, and Excel reports need to be sourced from an internal Power Pivot model. This means the reports and data model are confined to one single XLSX file and not available for reuse. Inherently this provides for a limiting situation if you wish to centralize a data model and/or separate the reports from the model.

There are a few additional differences and considerations, but these four are the biggies that I find myself talking about pretty frequently.

Finding More Information

Comparing Analysis Services and Power Pivot

You Might Also Like...

Decisions: Power Pivot, SSAS Tabular, or SSAS Multidimensional Model in SQL Server 2012

Signing Into SSMS 2014 as Domain User on Non-Domain Computer

Overview:  Just a quick tip about one way to run an application, such as SQL Server Management Studio, under the context of a domain user if you're not on a domain computer. 

Last week I started a new client engagement and we were tackling connectivity and setup.  For this engagement our team isn't working on VMs or client-provided equipment; we're using our own equipment owned by BlueGranite (my employer) rather than the problem. Ok.

However, my SQL Server credentials are Windows-based. Except that I'm not on the domain of the client. So I found myself trying to run SSMS as another user (the shift-right click thing) and it wasn't working.

The solution is the RunAs command with the /NetOnly switch.  RunAs executes a program under a different user account than the current login. When we add in the /NetOnly switch, it specifies the RunAs will pertain to remote access only -- meaning any connections made to other remote computers (like when we connect to a database in SSMS) will be made with the new user specified.

Here's the syntax for launching SSMS for SQL Server 2014 (with a default install location):

runas /netonly /user:domainname\username "C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\ssms.exe"

When you hit enter, you'll be prompted for the password associated with the domain user's account.

After you provide the password, it'll launch the application. Even though the SSMS login window doesn't indicate it, your remote connections will be made using the domain user identity rather than the local user.

 

If you'll be using this technique a lot, you probably want to create a custom shortcut. I went old school - typed the command into a text editor, saved it as a .bat file, and stuck it on my desktop. 

The nice thing is you can do this with a ton of programs. This technique can be used for testing or even performing a quick task with an elevated security account.

Third Annual SQL Saturday BI Edition in Charlotte

The third annual SQL Saturday, BI Edition, is back in Charlotte! It's coming up next weekend, on Saturday, October 4th.  

SQL Saturday is a free* training event for Database and Business Intelligence professionals. It's a conference condensed into a single day. The event includes training sessions led by local, regional, and national speakers, and lots of opportunities for networking and meeting up with peers. There's also exhibitor tables with useful products, information, and job opportunities.

The schedule has 40 technical sessions, at varying levels, on:

  • Data Analytics
  • Cloud
  • Big Data
  • Information Management
  • Development
  • Information Delivery
  • Environment Architecture
  • System Administration
  • Professional Development

More information: http://www.sqlsaturday.com/330/eventhome.aspx 

Registration: http://www.sqlsaturday.com/330/register.aspx

Schedule: http://www.sqlsaturday.com/330/schedule.aspx 

I hope to you see you there at SQL Saturday next weekend - be sure to say hi!

*If you would like to join us for lunch, we ask that you pay $10 to help us cover the cost of feeding a few hundred people. However, if you'd rather bring your own, that's ok too - just go through the registration process like normal. When it asks you to pay for lunch at PayPal, go ahead and close that window.