In this session we'll show you how to install and configure a new instance of Analysis Services in Tabular Mode. We will cover the following steps:
- Go through the setup steps in configuring the installation of Analysis Services in Tabular Mode.
- Validate the Analysis Services Tabular Instance.
This article assumes you have the following:
- Define what a Tabular Model is.
- Implemented SQL Server 2012 Standard/Enterprise.
- Experience/Exposure to PowerPivot tabular models.
- General know-how of report design and development.
What is a Tabular Mode?
Tabular mode used for hosting tabular model databases that use in-memory storage or DirectQuery storage.
Tabular models are in-memory databases in Analysis Services. Using state-of-the-art compression algorithms and multi-threaded query processor, the xVelocity in-memory analytics engine (VertiPaq) delivers fast access to tabular model objects and data by reporting client applications such as Microsoft Excel and Microsoft Power View.
Tabular models support data access through two modes: Cached mode and DirectQuery mode. In cached mode, you can integrate data from multiple sources including relational databases, data feeds, and flat text files. In DirectQuery mode, you can bypass the in-memory model, allowing client applications to query data directly at the (SQL Server relational) source.
Install and Configure Analysis Services Tabular Instance
1) Execute your SQL Server 2012 setup.exe to begin installation.
Note: Accept any update checks by clicking Next >.
2) Click Next until you get to the Installation Type page. Here you will select Perform a new installation of SQL Server 2012 option and click Next >.
4) Enter a Product Key if prompted and click Next >.
5) On the License Terms page, check the I accept the license terms and click Next >.
6) Within the Setup Role page, select the SQL Server Feature Installation option and click Next >.
7) Within the Feature Selection page, check Analysis Services and click Next >.
8) This will take you the Installation Rules page. If there are no errors, click Next > to proceed with the Instance Configuration page.
9) From within the Instance Configuration page, select the Named Instance option and provide a name. For example, MyASTablular.
Note: the Instance ID will automatically get populated with the name you provided, but in uppercase.
10) Set the Instance Root Directory to your desired location. Click Next > when you are done.
11) Next you'll review the Disk Space Requirements page. Make any configurations to storage location if needed and click Next >.
12) Within the Server Configuration page, you can accept the default accounts and collation values, or manually configure the properties. For this example, we will accept the default values and click Next >.
13) Within the Analysis Service Configuration page, select the Tabular Mode for the Server Mode value. Add the users you would like to grant Administrative Permissions to. You may optionally modify the Data Directories location.
14) Within the Error Reporting page, click Next >, or check the box to send Windows and SQL Server Error reports to Microsoft.
15) Click Next > on the Installation Configuration Rules page, if no issues are found.
16) Within the Ready to Install page, review the list of features and click the Install button. Kick back for a little bit and let SQL Server do its magic...
17) Upon completion of the install, you should see something similar. Click the Close button when you are done.
Validate Analysis Services Tabular Instance
1) Open SQL Server Management Studio.
2) The Connect to Server dialog renders. Change the Server Type to Analysis Services.
3) For the Server Name, click the dropdown and attempt to find your newly created instance. You may have to click <Browse for more..> to find your instance. For example, ServerName\MyASTablular.
4) Click Connect button to load the Tabular Model in the Object Explorer Pane. It will present your server name and the tabular instance name for the Root node. You Should see something similar.
You have completed this session of "Installing and Configuring a new instance of Analysis Services in Tabular Mode"!
Reference: Tabular Modeling (SSAS Tabular)