PowerPivot has a 2 GB limit for the size of the Excel file. If you are running a solution in Excel on your local machine, the resources available may further impact your maximum size. Tabular SSAS models do not have an upper size limit.

Tabular models have partitions, which can be used to manage processing of large data volumes by breaking them into smaller chunks of work. PowerPivot does not have partitions.

Tabular SSAS models support DirectQuery against your data sources. You can consider DirectQuery almost as a scaling feature as it allows you to avoid processing large data volumes altogether. PowerPivot only queries the in-memory xVelocity cache.

PowerPivot allows for a daily scheduled data refresh in SharePoint and checks on usage data via central administration. Managing tabular SSAS models is similar to multidimensional models with the full power of SQL Server tooling and APIs.

SQL Server management has all the backup, restore, attach, detach, and delete features that you would expect. It also allows for scripting out from SSMS, browsing your cube, and querying your cube using MDX. These features are identical across multidimensional and tabular models. There are also unique features for tabular models, such as DAX queries and restoring from a PowerPivot workbook.

You can create or modify Analysis Services objects using Analysis Management Objects (AMO) or XML for Analysis (XMLA) commands containing Analysis Services Scripting Language (ASSL) elements. These work consistently across tabular and multidimensional models. The deployment wizard also works just fine for both tabular and multidimensional models. PowerShell cmdlets work for both multidimensional and tabular, except Invoke-ProcessCube is not supported for tabular models. Integration Services tasks work for tabular models. Finally, msbuild integration is new for tabular models, allowing you to automate nightly builds.

PowerPivot is designed to provide a rapid development solution that is contained within an Excel workbook. As such, the security model of PowerPivot is constrained to workbook-level security. Either you have access to the workbook or you do not.

In tabular models, roles define permissions for a model. Each role contains members, by Windows username or by Windows group, and permissions (read, process, administrator). Members of the role can perform actions on the model as defined by the role permission.

Row filters define which rows in a table can be queried by members of that role. Row filters are defined for each table in a model by using DAX formulas. Row filters can be defined only for roles with “Read” and “Read and Process” permissions. If a row filter is not defined, members of a role that have “Read” or “Read and Process” permission can query all rows in the table.

Once a row filter is defined for a particular table, a DAX formula that must evaluate to a TRUE/FALSE value defines the rows that can be queried by members of that particular role. Rows not included in the DAX formula cannot be queried. These filters apply to related rows as well. When a table has multiple relationships, filters apply security based upon the active relationship.

Dynamic security provides a way to define row-level security based on the username of the user currently logged on or the CustomData property returned from a connection string. This is simply implemented by filtering your data using the userID of the logged-in user to filter a given dimension.

To implement dynamic security, you can use the following functions as part of a DAX formula to return the user name of the user currently logged on, or the CustomData property in a connection string. The USERNAME function returns the Active Directory domainusername of the user currently logged on. Alternately, you can use the CUSTOMDATA function to return the CustomData property in a connection string. A best practice is to use the LOOKUPVALUE function to return values for a column in which the Windows username is the same as the username returned by the USERNAME function or a string returned by the CustomData function. Queries can then be restricted where the values returned by LOOKUPVALUE match values in the same or related table.

One of my favorite things about PowerPivot is how easy and fast it is to whip something together in Excel and publish it out to SharePoint. There is no server to think about or big heavy development tool suite to load up. I probably had Excel open to look at some data anyway so it’s pretty quick and easy. If I’m building something really important and want to make sure I don’t mess it up, sometimes I’ll back up my Excel workbook before making changes. As developers, we are all used to a more robust integration application life cycle management solution.

Tabular SSAS models live in the Visual Studio and benefit from services such as integrated source control, Team Foundation Server work item and bug tracking, and Team Build integration. The editing environment can be extended using Visual Studio extensions, such as the DAX Editor, which adds language services for DAX into Visual Studio.

The other life cycle benefit is that because tabular models live in Visual Studio, there is a natural separation between development, test, and production deployment. A workspace server is specified for the hosting of the development tabular model and a deployment server is specified for deployment of the finished cube. Visual Studio support also includes the ability to preview the cube you’re developing in Excel. Finally, the deployment wizard can alter connection strings, retain role members, and deploy your changes to a model to production.

As stated at the beginning of this chapter, the power of Analysis Services allows you to deliver scalability and the IT governance required for business-critical applications. The method for scaling up is to upgrade from a regular PowerPivot workbook to a full tabular cube.

Figure 18-1. Creating a new project in Visual Studio

The wizard to import a PowerPivot workbook is not very complicated, but let’s walk through it. First, you are prompted for a default Analysis Services server that is running in tabular mode. Remember that an Analysis Services server is configured to support either tabular mode or multidimensional (OLAP) mode. If you have both types of cubes, then you need two instances of Analysis Services running.

Simply provide the name of your SSAS tabular mode server, as shown in Figure 18-2.

Figure 18-2. Providing the name of your SSAS tabular mode server

Next you’ll browse for the PowerPivot workbook you’d like to import. This workbook can either be on your local file system or you can paste the URL of your SharePoint site into the browse path to import your model from SharePoint. Both techniques work equally well (see Figure 18-3).

Figure 18-3. Specifying the path to the workbook

You may get the message shown in Figure 18-4 informing you that the data will not be imported due to permissions between the workspace server and your local PowerPivot model. Don’t worry about it. The metadata that defines the cube is really all you want to import. You will process the partitions in Visual Studio and at deployment time, load in the data from your data sources. So go ahead and click Yes.

Figure 18-4. BISM message regarding importing metadata, but not data

When the import completes, you’ll see your model reflected in Visual Studio, as shown in Figure 18-5. Note that your tables are each reflected as tabs. The main window has a split layout with the columns of the table on top and the imported measures shown below.

Figure 18-5. BISM model in Visual Studio

When you process your cube from the model drop-down menu, you will be prompted for credentials for each of your data sources. If you are using Windows authentication, this may just be a pass through, but for accounts where a username and password are required, you’ll need to specify them as shown in Figure 18-6.

Figure 18-6. Providing the Azure DataMarket credentials

It’s really that easy to upgrade your solution from PowerPivot to a SSAS tabular model. The cube will process as shown in Figure 18-7 and will then show you the confirmation of success as shown in Figure 18-8.

Figure 18-7. Importing data

Figure 18-8. Data imported successfully

At this point, you’ll see all your data loaded in the Visual Studio–based designers, as shown in Figure 18-9. You can further refine your model as needed using the same techniques you learned in the previous chapter.

Figure 18-9. Data in Visual Studio

To get this project deployed to our production server, we’ll need to configure a deployment server. Open the property pages for the project by right-clicking on the project name in Solution Explorer and clicking Properties.

Figure 18-10 shows the Property Pages dialog where we can configure the name of the server to which we will deploy, the edition of SQL Server we are running, and the name of the database and cube we want our users to connect to. Set up your preferred options here and click OK.

Figure 18-10. Property Pages dialog

The Build drop-down menu gives us the ability to deploy our new tabular model out to production, as shown in Figure 18-11. Of course, this does assume that you have rights to your production server.

Figure 18-11. Deploy option in Visual Studio

After that process completes, we will validate by connecting to and browsing the model.

You will see your new database shown in Object Explorer on the left.

You are now connected to your cube running on your production server and you can browse it simply by dragging measures and dimensions onto the query design surface. Remember that you will need both measures and dimensions on the surface before starting to see data.

Figure 18-12. Cube Browser

The tabular model is an in-memory cache of your data. This makes querying very fast, but it also means that as your source data changes, you will be looking at stale data. One approach we’ve seen people take is making the junior person on the team log in at 6 a.m. and manually refresh the data. We don’t recommend that approach, as they won’t stick around very long.

Every SQL Server database administrator and even most developers will be familiar with using SQL Server Agent to schedule tasks that you would like to run automatically. It’s reliable, quick, and easy to configure and can even send you an email if something goes awry—seems like a better solution then the junior guy already!

Setting this up isn’t too hard either. Management Studio has the ability to generate scripts after walking through a wizard and we’ll use that to help us automate this. Let’s get started.

Figure 18-13. Select Process Database

This launches the Process Database wizard. Only a few options present themselves here, but we’ll walk through them. The mode you choose determines what steps Analysis Services goes through when processing your cube. If you want to reach out to your data sources and pull in new data, you’ll want to do a full process as seen in Figure 18-14. If we had a solution with multiple partitions, they would each be displayed at the bottom. Furthermore, you can uncheck partitions that you don’t want to update. This is great if you have some large reference data that doesn’t change with the same frequency as your transactional data.

Figure 18-14. Choosing to process the full cube

If you don’t know it’s there, then you probably didn’t notice that in the top of our Process Database wizard, there was a drop-down menu labeled Script. They say that knowing is half the battle but in this case it’s more like 90%. After you’ve set your options just select Script Action to Clipboard as shown in Figure 18-15.

Figure 18-15. Script Action to Clipboard

What’s produced by this is really not all that complicated. In fact, before realizing that you could script the output of the wizard, most Analysis Services developers used to write this XML by hand. Check out the XML produced by the Process Database wizard.

<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Type>ProcessFull</Type>
  <Object>
    <DatabaseID>TabularHelpDesk</DatabaseID>
  </Object>
</Process>

Finally, all we need to do is get this scheduled by setting up a job in SQL Server Agent. SQL Server Agent is managed via the database engine, so we’ll need to connect to our database engine with SQL Management Studio to set this up.

Figure 18-16. Select New Job in SQL Management Studio

Once again, we get a wizard to walk us through this configuration process. On the General tab, simply provide a name for the new job and add any description information to identify it to your administrators (see Figure 18-17).

Figure 18-17. Identify Administrators

SQL Server Agent jobs are composed of a series of steps that are executed in order. Select the Steps table and click the New button to add a step, as shown in Figure 18-18.

Figure 18-18. Adding a step

Figure 18-19. Specifying the SSAS Command

You will see that the step was added successfully and now, can move to the Schedule tab to set up the times you’d like the job to run. The dialog shown in Figure 18-20 is self-explanatory. We can run hourly, daily, weekly, or whatever schedule you need. Remember that processing your cube does take server resources on both the database servers that hold your source data and on your Analysis Services server where the cube will be processed. For large cubes, it’s a best practice to update outside of business hours in order to not impact performance if that schedule is compatible with your requirements.

Figure 18-20. New Job Schedule dialog

Once you’ve completed configuration of your new job, it’s a really good idea to give it a run. This helps you validate that the account you are running as has the appropriate rights on your server and that everything goes as planned. Just right-click on the job in Object Explorer and select Start Job at Step... (see Figure 18-21).

Figure 18-21. Start Job at Step... Option

When this job finishes running, you’re done.

In this chapter, we took a PowerPivot model from either Excel or SharePoint and upgraded it to a full-featured SSAS tabular model. By developing in Visual Studio, you get the full professional developer solution with integration into TFS and development, test, and production release cycles.

This completes the development of your BI Semantic Model. In the next part, we will explore the various clients that you can use to explore and visualize your data including Excel, Power View, and Reporting Services and how they link into SharePoint.