Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Analysis Services
3.
Storage Modes
3.1.
Native Mode
3.2.
Share Point Mode
3.3.
Multidimensional Mode
4.
Installation of Storage Modes
5.
Installation of Framework
6.
Frequently asked questions
6.1.
List some specialized editions of Microsoft SQL Server.
6.2.
Explain the Replication Services of Microsoft SQL Server.
6.3.
What do you know about the Integration Services of Microsoft SQL Server?
7.
Conclusion
Last Updated: Mar 27, 2024

Analysis Services in Microsoft SQL server

Author Rupal Saluja
0 upvote
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

You may have heard this several times that databases are the need for the future. Traditional File System usage has declined extensively. People have realized that managing and working with databases is much easier. SQL Servers become vital in this scenario of excessive database activity. Microsoft provides consumers with at least varied editions of SQL Servers that target different audiences. In this blog, we will try to understand the analysis services provided by Microsoft SQL Server.

Analysis Services

As the name suggests, Analysis Services are services that are used to analyze large amounts of data. A thesis is prepared on the basis of which business decisions are made. Analysis services are also used in the creation of two or more multidimensional business models and multidimensional storage modes, namely, Partition Storage Modes, Dimension Storage Modes, and Tabular Storage Modes. It is online analytical processing (OLAP) and data mining tool of Microsoft SQL Server. Microsoft began its journey into this business by acquiring OLAP technology from Panorama Sofware Company, based in Canada. In the short span of two years, Microsoft launched OLAP services as part of SQL Server 7.

Microsoft SQL Server Analysis Services, acroynmed as SSAS, is a tool used by organizations for analyzing and making sense of data that is present to be used for future calculated decision making. This has helped Microsoft to add up a number of services related to Business Intelligence, business analysis, and data warehousing.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

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 SQLSQL Server Interview Questions, and Azure SQL by clicking on the respective links.

To grasp more knowledge about MongoDBDatabasesOperational 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!

Previous article
Integration Services in Microsoft Services
Next article
PostgreSQL - Data types
Live masterclass