Chapter 4. Analysis Services
In this chapter we will be focusing on the new features that catapult both SQL Server and Analysis Services straight into the 21st century. Some of these features we have not touched upon, particularly surrounding the advances in PowerPivot and SharePoint. These are specialist topics, and are big enough to demand a book of their own.
When we first read about the new Analysis Services features, one of the first things that immediately came to our attention was the new tabular architecture model. There are now three models to choose from when configuring SQL Sever Analysis Services, so we will explore all three to help you understand which one you need. We will examine how to set up the three models, how to find out which one is running and how to change the model.
You will learn how to override the string store limit of 4 GB and upgrade projects from earlier versions of SQL Server to take advantage of this new storage capability.
We found this chapter particularly...
Business Intelligence Semantic Model
What does Microsoft imply when talking about the Business Intelligence Semantic Model (BISM)? Fundamentally, it is the Analysis Services storage engine in which you choose to store your analytical data. The solution may also include SharePoint, Reporting Services, Excel or a custom BI application.
SQL Server 2012 Analysis Services introduces a new Business Intelligence Semantic Model called tabular modeling. We will take a look at what this new model offers, as well as exploring the two existing models and how xVelocity fits in.
While we refer to these as "models", as does Microsoft in their documentation, it can be helpful to think of them as different types of Analysis Services storage engines. We recommend reading Analysis Services expert Chris Webb's excellent article "So, what is the BI Semantic Model?" at http://cwebbbi.wordpress.com/2012/02/14/so-what-is-the-bi-semantic-model.
Whether you are a BI developer or the DBA charged with keeping Analysis Services up and running, you need to know about the three different server models. These models determine the types of solutions that can be delivered in your organization.
The three models are Multidimensional and data mining (the default mol), Tabular, and PowerPivot for Shareoint.
You can only run one model per instance. If you need more than one, you can run all three models as separate instances on the same server, if you so wish. If you later find that you have installed the wrong model, you can uninstall it and re-install another. The three models function in different ways and we will explore each model in the upcoming section.
Tip
Check with your business users and developers before starting your Analysis Services installation to see if there are particular features they need, and ensure they are included in the model you plan to provide. For a side-by-side comparison across the three models...
We are big fans of performance monitoring and always happy when Microsoft add new features to enhance the utilities that already exist. SQL Server 2012 introduces the Resource Usage event class. This new event class allows you to gather statistics on how your resources are used when processing queries.
You can monitor these events using SQL Server Profiler, just as you would with your OLTP systems. SQL Server Profiler monitors the engine process events such as logins, transactions starting and stopping, and what T-SQL code is executed on the server. It captures data about these events, such as how long it took to run, so you can track both server and database events.
Using SQL Server Profiler, you can monitor the overall performance of Analysis...
Extended Events for Analysis Services
This is a geeky topic, even for this book. Nevertheless, if you develop event tracing for Analysis Services or, develop system monitoring tools, it is worth knowing that you can now trace Analysis Services using SQL Server's Extended Events framework. This event driven framework places little demand on resources, and is easily configured and scaled, handling and routing events from the database engine to consumer applications.
An entire book could be written on this subject as it is very specialized, so rather than wasting valuable space here by giving it perfunctory treatment or just repeating what has been said, we would recommend the following resources:
If you have ever encountered the error message File system error: a FileStore error from WriteFile occurred, then you will probably be all too familiar with the reason for this message. In a multidimensional database, strings are stored using a different architecture to numeric data so that performance optimizations can be made based on data characteistics.
A dimension attribute such as a name or location could be considered as string data. Also a string data is used for distinct count measures and keys. SQL Server's default behavior is to store the string data in a file (.string) that has a 4GB size limit. This still exists as the default in SQL Server 2012, but this edition introduces a new storage feature called scalable string storage that allows you to overcome this limit. This allows the string store to grow as the data stored increases in size.
Using scalable string storage
This new storage feature increases your upper storage limit to either four billion unique...
In this chapter, you have learned about the new tabular model and which of the three Analysis Services architectures is the best fit for your environment. You saw how to install the three models, determine which model is currently running on your server and how to bypass the default string store limit.
You will probably agree that there is so much to explore in SQL Server 2012 Analysis Services that it deserves a book to itself. If you have not already delved in deep, we hope you have learned enough to start your journey into the world of BI. You are now in a firm position to start exploring the topics we have not had the scope to cover here. One thing is for sure, it is incredibly exciting to see Analysis Services evolve.
In the next chapter, we will explore the new features in Reporting Services, including deprecated features, Data Alerts and Report Builder 2012.
