Seeing that the reports developed by John Kirkland are greatly helping the company understand its performance, the management decided to explore the use of SQL Server Analysis Services (SSAS). The company has accumulated huge amount of data from various sources, and SSAS is the perfect tool to implement the company's analytics initiative.
The company's senior BI Developer designed an Online Analytical Processing (OLAP) database and it is ready for deployment on the Analysis Services server.
John was tasked to develop reports based on the cubes in the OLAP.
There is a big advantage in using an analytic engine in handling Big Data. The SQL Server database engine is of great use in storing and retrieving relational data. But once you demand complicated calculation and aggregation, the database engine can only do so much.
Now that the company has embraced analytics, John can slice and dice their data and, thus, can have a deeper understanding of what those data mean. John is now more capable of delivering meaningful data to report consumers.
Please take a note that we are not going to step-by-step cover anything on creating dimensions or cubes on Analysis Services, aside from setting up the deployment of Analysis Services project to the server. In this chapter, you'll learn the following:
- Checking port that the Analysis Services listens to
- Setting up a new inbound rule in the firewall for SSAS deployment
- Deploying model for Analysis Services
- Adding SSAS data source
- Working with the query designer
- Building and using a dataset based on a cube
- Adding a filter on the dataset
- Adding a parameter on the dataset
- Creating a calculated member
We need the following files from CodePlex for this chapter to build the data source for our report:
AdventureWorksDW2012 Data FileAdventureWorks Multidimensional Models SQL Server 2012
Here is the link to the download page: http://msftdbprodsamples.codeplex.com/releases/view/55330
If this is the first time you deploy an Analysis Services model, chances are you need to set up the firewall of the server where the Analysis Services is installed. You may also need these settings if you cannot log in from a remote SQL Server Management Studio session but can log in without any problem locally.
You may not need this firewall setting if you are deploying the model from the same server your Analysis Services is installed.
- Get the Process ID (PID) of the Analysis Services running in your server, as shown in the following screenshot:
- To view the TCP Port of PID
1208, runnetstat –ao –pTCP from the command line: