Analysis Services in Microsoft SQL server

Storage Modes

There are three modes available with Microsoft SQL Server. These modes are-

1. Native or Tabular Mode
2. Share Point Mode
3. Multidimensional and Data Mining Mode

Native Mode

Native Mode is also known as SQL Server Mode. As the name suggests, the native mode is the default server mode. If you leave the ASSERVERMODE parameter as null, it will be set to Tabular. It uses VertiPaq in-memory analytics engine as the default storage. It is a memory-bound storage option. You can configure tabular solutions to use DirectQuery disk storage as an alternative as per your requirement which has no bounds on Memory.

Share Point Mode

The Share Point Mode is optional. If you do not select the server mode for this option, the Power Pivot option is automatically set to 1 for Share Point installation. For better performance of this mode, you can adjust Memory Management and IO settings. This will help you enhance the capabilities of the non-default storage modes of Analysis Services. 

Multidimensional Mode

The Multidimensional Mode is also known as Data Mining Mode. This mode uses MOLAP as its default storage only for such models that have deployed Analysis Services with this mode. After deployment, you can configure the mode to use ROLAP instead of MOLAP. This will help you to run queries against the relational database. You will not have to store queries before running them which is a compulsory step when you use MOLAP.

Installation of Storage Modes

SSAS is multi-instance, which means, we can install multiple copies on a single system, or run new and old versions of SASS simultaneously. There are three modes available for SASS, and for every mode, a different instance can be made available. In this way, you can use all three modes at the same time.

To know how to install the Analysis Services using Wizard, follow the steps mentioned below.

1. Firstly, open the Feature Tree in Setup and select Analysis Services from it.
2. A Configuration page will be opened. On that page, you will find options to select a mode as per your requirement. Native or Tabular Mode is set by default.
3. Now, you have to determine the parameters for Command-Line Setup. The example below shows an illustration of a command-line setup that will install Analysis Services in Tabular Mode.

For Command-Line Setup, use the following command.

Setup.exe /q /IAcceptSQLServerLicenseTerms /ACTION=install /FEATURES=AS /ASSERVERMODE=TABULAR /INSTANCENAME=ASTabular /INDICATEPROGRESS /ASSVCACCOUNT=<Domain\User> /ASSVCPASSWORD=<Password> /ASSYSADMINACCOUNTS=<Domain\User>

You can refer to the table below to get a description of the parameters mentioned in the Command Line Setup below.

Installation of Framework

Till 2008, BIDS was used as an environment to work with SSAS. BIDS stands for Business Intelligence Studio. In this framework, there were limitations due to which an enhanced framework was much needed. Limitations such as the simultaneous running of different modes, no options for storage modes other than the default, etc.

Since 2012, we have been using an advanced version of the framework known as SSDT. SSDT stands for SQL Server Data Tools. SSDT not only resolved the drawbacks of previous frameworks but also provided quality security and better user-admin interaction facilities.

To learn how to install the framework, you need to follow the steps mentioned below.

1. From the Microsoft SQL Server Programs groups, you have to select the SSDT option.
2. Once you have opened SSDT, a screen will appear. Click the New option present on the top left corner of the screen.
3. Choose the project you want to open.
4. Select Analysis Services under the Business Intelligence section present in the left corner of the screen.
5. A screen pops up. Sect any one option out of the five listed as per the requirement of the project.

Frequently asked questions

List some specialized editions of Microsoft SQL Server.

The list below shows the specialized versions of Microsoft SQL Server available.

• Azure
• Azure MPP
• Compact (SQL CE)
• Developer
• Embedded (SSEE)
• Evaluation
• Analytics Platform System (APS)

Explain the Replication Services of Microsoft SQL Server.

For replication and synchronization of database objects, entirely or in subsets, using Replication Agents, we use Replication Services. Replication Services are of three types, namely:

• Transactional Replication
• Merging Replication
• Snapshot Replication

What do you know about the Integration Services of Microsoft SQL Server?

SQL Server Integration Services, acronymed as SSIS, provides Extract, Transform and Load (ETL) capabilities for SQL Server. These capabilities are used for data import and export, data integration, and data warehousing needs. SSIS utilizes GUI tools for building workflows which include extracting, querying, transforming, merging, and exporting data.

Conclusion

In a nutshell, we understood what are Analysis Services, and learned about the various modes of Analysis Services and the steps to install them. We also saw the steps to install the frameworks required for the functioning of these Analysis services.

We hope the above discussion helped you understand the Analysis Services of Microsoft SQL Server in clearer terms and can be used for future reference whenever needed. For a crystal understanding of Microsoft SQL Server, you can refer to our blogs on Microsoft SQL, SQL Server Interview Questions, and Azure SQL by clicking on the respective links.

To grasp more knowledge about MongoDB, Databases, Operational Databases, and Non-Relational Databases, you can pay attention to our blogs on those topics by clicking on the respective links. Visit our website to read more such blogs. Make sure that you enroll in the courses provided by us, take mock tests and solve problems available and interview puzzles. Also, you can pay attention to interview stuff- interview experiences and an interview bundle for placement preparations. Do upvote our blog to help fellow ninjas grow.

Happy Coding!