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.

Resolving Errors from Deployment of Database to Azure SQL DB

This week our team at BlueGranite decided that the sample relational database called ContosoRetailDW would be best located for our demo & training purposes in Azure as a SQL Database. Below are the steps taken to get this sample database in a state where it could be deployed as an Azure SQL DB. I will list my process from beginning to end, although I won't bother detailing out all of the early steps.

Download the Sample Database

First, I downloaded the Contoso database backup file, extracted it, and restored the backup to a SQL Server 2014 instance. Then I verified it can be queried in SQL Server Management Studio.

Azure Management Portal

In the Azure management portal, I created a small Azure SQL DB just for testing purposes. Since this was the first Azure SQL DB created in this Azure subscription, I wanted to do a small test to obtain a server name, set the server firewall, and check connectivity.

You can find the server name on the Servers page. To set the server firewall, click the Manage button, and respond Yes to the first prompt.


Connect to the Azure server from SSMS for a quick verification of connectivity to the test database created as an Azure SQL DB. 

Good. Now that I know I can connect ok to Azure, I'm ready to move onto the task at hand with migrating the Contoso sample database.

First Attempt to Deploy to Azure SQL DB from SSMS

From within SSMS, connect to the instance where the database to be migrated exists. Right-click the database > Tasks > Deploy Database to Windows Azure SQL Database. 

Well, if the story ended here it wouldn't have any drama, right?  With my first attempt I received a list of errors due to unsupported features. I initially tried to rectify them in SSMS but it's just not the best tool for the job so I'll skip that party of the story.  Enter Visual Studio.

Removing Unsupported Features in Visual Studio

Open Visual Studio (SQL Server Data Tools). Connect to your SQL Server instance in SQL Server Object Explorer. We will need a project in order to alter the schema to remove unsupported features.  So, right-click the Contoso database and choose Create New Project.

I named mine as shown below since it's purpose was to convert the original database schema to where it's supported in Azure SQL DB.

Now that we have a project that is based on the Contoso Retail DW database, the first thing to do is change the target platform to Microsoft Azure SQL Database. This is done in the project properties.

Now that the project understands its destination will be an Azure SQL DB, let's do a build and see what happens.

Same list of errors that I got previously in SSMS. However, now we're in a lot better position to address them quickly.

There's an MSDN blog entry which discusses a quicker way to address the issues. I went ahead and handled them individually.  It didn't take very long at all.  

Double-click on each error in the Error List and it'll take you to the DDL where the unsupported syntax exists. 

One of our two main issues is data compression.  That required the "WITH (DATA_COMPRESSION = PAGE) syntax to be removed from some of the tables.

Another one of the issues was extended properties which aren't supported in Azure SQL DB at this time. So, even though they are nice for documenting a database, I went ahead and removed the extended properties from each object.

Let's try another build (a rebuild actually, to make sure everything gets evaluated). And now that all of the errors are rectified we are happy and ready to move on.

Schema Comparison in Visual Studio

Now it's time to get the real database sync'd up with the changes made to the project schema. So let's create a schema comparison under Tools > SQL Server > New Schema Comparison.

For the left side I used the project (i.e., the "fixed" one) and for the right side I used the database (i.e., the "wrong" one that needs to be "fixed"). 

Looking down the list, I see some deletes and some changes. They all look reasonable. Click on the Script button on the SqlSchemaCompare toolbar.

Now I have a script that I can run against the database to get its DDL updated.  Scrolling down through the script, everything looks reasonable.  Next step is to Connect to the database server using the icon on the toolbar.

Once connected, we can execute the script.

Mine took 18 minutes to run on a laptop.

Deploy from SSMS to Azure SQL Database

Now that the unsupported features have been addressed, let's try the deployment again. In SSMS, right-click the database > Tasks > Deploy Database to Windows Azure SQL Database.

We are presented with a wizard.

Connect to the server in Azure.  The server name from Azure for a SQL DB is appended with ".database.windows.net".  Be sure to use SQL Authentication. 

I changed the maximum database size from the default of 1 GB to 5 GB, since my version of the Contoso Retail DW is about 3.9 GB. 

You're presented with a summary page before choosing Finish. Double check your Source Database is less than the Maximum Database Size selected for the target with some room to spare. If the source is larger, it'll still deploy what it can until it runs out of space.

As it's working you'll see status messages.

Mine took a couple of hours to deploy the ~4GB database. Now our sample Azure SQL DB is ready to be used.

Verify Connectivity to Azure SQL DB

In SSMS, connect to the instance & do a quick test to verify connectivity is working.