Tag: Excel

Promoting Excel Tabular Model to SSAS

PowerPivot for Excel is a fantastic addition to Microsoft’s Business Intelligence offering.  It provides a self-service capability for users to mash-up huge amounts of data from multiple sources, create advanced calculations that get refreshed alongside their pivot tables and the ability to very easily publish workbooks as web applications.  Excel users are already comfortable with the interface.  And the PowerPivot add-in is free for Excel 2010 and 2013 users. 

There are pros and cons to using any one of PowerPivot for Excel, PowerPivot for SharePoint, SSAS Tabular or SSAS multidimensional.  Melissa Coates has written a great article on when to use which type of model. While PowerPivot for Excel is a fairly robust and flexible tool for individual users, there are many reasons why you might promote an Excel Tabular model to SQL Server Analysis Services. 

PowerPivot for Excel can be a great starting point for users to decide what information they want to include in a model. It allows them to play with the data and refine the model until it houses the main information that they need to see on a regular basis, in a format they can easily consume.  What can happen is that the workbooks get emailed around and get out of synch between users.  Or the data gets too large and the model takes forever to refresh.  Or the in-memory calculations take too long to load.

When this happens you might want to promote your Excel Tabular model to SSAS, if your infrastructure supports it.  It means the flexibility for users to mash-up additional data is lost, but the gains in stability, scalability, security and other features can be well worth the move.  Microsoft has made this promotion of a model very easy to do.

1. Create a new SSAS Project

Open up SQL Server Data Tools.  In the File menu click New and then Project.  In the New Project dialog box, under Installed Templates expand Business Intelligence and select Analysis Services.  On the right select Import from PowerPivot.

Browse for a location and enter a name for your project, and then click OK.

image_thumb1

In the Tabular model designer dialog you can enter the SSAS instance you will use for model.  It will need to be an SSAS instance running in Tabular mode.

image_thumb3

In the Open dialog box select the PowerPivot model for Excel that you will be converting to SSAS.

image_thumb5

It will take a minute or two to load the model into the project.

2. Check and refine your model

In the solution explorer, double click on the Model.bim top open up the imported model. 

image_thumb6

You will see the tabs similar to the PowerPivot model near the bottom of the screen, and any warnings, errors or messages. 

image_thumb8

Here you can do any edits to your model.  The interface is very similar to PowerPivot.  You can delete, hide or show data tabs from Client Tools by right clicking on the tab. 

You can manage the model from the Model menu.

image_thumb12

You can edit the table properties and manage the relationships from the Table menu.

image_thumb10

You can add, edit and manage calculations and columns within the table tab.

image_thumb14

3. Deploy the Model to Analysis Services

From the Build menu, you can build and deploy your model.  You can troubleshoot any deployment issues by following the error messages that pop up in the Deployment dialog in the Message column.

SNAGHTML103f7068_thumb1

image_thumb16

4.  Connect to your SSAS Tabular Model

You can connect to the model through Excel or SSMS or any other client tool that can consume an SSAS model.  In Excel, you won’t be using PowerPivot, rather you will be using the SSAS Tabular model as a direct data source.

In Excel, click on the Data tab, click on From Other Sources and then From Analysis Services.

image_thumb22

Enter the server name where you deployed your model, then select the database and model that you deployed.

image_thumb24

You can now use this as a classic Pivot Table data source.

 

I’ve noticed that the Tabular model is not as robust or responsive as the multi-dimensional model when you get to larger sets of data.  This is because it does everything in memory.  The converse of this is also true for smaller sets.  Because it is in-memory it can retrieve information and do calculations more quickly for smaller data sets.  Multi-dimensional models have additional features that Tabular does not have.  Richard Lees did an interesting comparison of Tabular vs Multidimensional.

As mentioned at the beginning of this article, the flexibility of PowerPivot can be exchanged in favour of the stability and scalability of a traditional SSAS deployed model.  Having users develop their own tabular models in PowerPivot and then have I.T. manage and deploy it to a wider audience allows for a great partnership and a quick development cycle.

SSIS Excel Refresh Task

CodePlex has a great new toolbox item for SSIS projects in Visual Studio 2012.  It’s called the SSIS Excel Refresh Task. It allows you to refresh a whole Excel file or single queries of your Excel sheet.  It works for PowerPivot models as well.

There isn’t a lot of instruction on how to install or use it, so I will blog it here. 

INSTALLATION
  1. Close Visual Studio 2012
  2. Install the SSDT Business Intelligence project templates for Visual Studio 2012 if you don’t already have them.  http://www.microsoft.com/en-us/download/details.aspx?id=36843  This will allow you to create an SSIS project.
  3. Download the SSIS Excel Refresh task from here https://ssisexcelrefresh.codeplex.com/
  4. Open Visual Studio 2012 and create an Integration Services project and create a package.
  5. You won’t see the new SSIS Excel Refresh Task in the toolbox the first time.  You have to right click inside the SSIS toolbox and select Refresh Toolbox.  The task will appear in the Common section of the toolbox.

image

PREPARING YOUR EXCEL WORKBOOK CONNECTION FOR AUTO REFRESH

If you are using Windows Authentication in your Excel Data Connection, then you need to ensure that the user driving the SSIS update will authenticate to your data source. If you are using SQL Authentication, then you need to set the password to be saved, so that SSIS won’t get a nasty message box looking for the password.  This means that anyone opening the Excel file will have access to the data and to the password, since the password is stored without encryption in the workbook.

  1. In the Data tab, select Connections.
  2. Open up the properties for each connection which will be refreshed automatically by the refresh task. 
  3. Click on the Defintion tab and check the Save Password check box

SNAGHTML77a40aa0

USING THE TASK
  1. Drag a sequence container onto the Control Flow.
  2. Drag the Refresh Microsoft Excel Task into the sequence container

image

3. MAKE SURE the Excel file you want to refresh is closed.  Check that it is not locked by another user, otherwise the next step will hang.  Test this by opening and closing the file before continuing.

4. Double click on the Refresh Microsoft Excel Task to edit the properties.  Click the NEW button to create a connection to your Excel file.

image

 

5. Once you create the connection, there is a short wait while the component finds the OLEDB Connections.  From here you can do one of two things: 

a)  You can refresh ALL of the connections in the workbook

image

or b) You can refresh specific connections in the workbook

image

6. The component isn’t notified when the update process is finished, so a timeout needs to be manually defined, or dynamically based on the file size.  To have it dynamically defined select the Dynamic Timeout Calculation. This is the best option for growing files.  Be aware that the calculation is not guaranteed, and refresh may still not be complete after the timeout. You’ll want to be aware of this and keep an eye on it. 

image

7. You need to set the IgnoreTimeoutWarning explicitly to turn off the warning on the task.  This is to remind you of the risk of incomplete updates due to the timeout issue described above.

image

8.  Selecting ‘Create a backup before refreshing will create a copy of your spreadsheet in the same folder where the spreadsheet is, and on successful update it will delete the copy.

image

The tricky bit is, you don’t get an error message if the update is not successful, due to a timeout or some other reason. 

That’s all there is to it.  Simple and effective.  As evidenced by an internet search on this task, it is much easier than writing custom VB scripts to do this for you.