What is SSAS (SQL Server Analysis Services)?

• SSAS Definition

  SSAS Definition

  SQL Server Analysis Services (SSAS) is a multidimensional online analytical processing (OLAP) server and an analytics engine used for data mining. It allows IT professionals to break up large volumes of data into more easily analyzed parts. A component of Microsoft SQL Server, it helps enable analysis by organizing data into easily searchable cubes.

  SQL Server Analysis Services is a tool primarily used by organizations to analyze and make sense of information otherwise spread out, whether over multiple databases or in different tables or files.

  While there are many services included in Microsoft intended for business intelligence and data warehousing, Analysis Services focuses on OLAP and data mining capabilities. These capabilities come in two varieties: multidimensional and tabular.

  Multidimensional online analytical processing (MOLAP) is the classic form of OLAP. MOLAP uses optimized multi-dimensional array storage to store data, as opposed to a relational database. MOLAP is usually associated with fast query performance because of optimized storage, multidimensional indexing, and caching. It is also very compact, making it ideal for low-dimension data sets.

  For even faster execution of queries, the tabular storage mode is used to compress data and store the model in memory. Only tabular models are supported by Microsoft Azure, Microsoft’s cloud-based solution.

  While multidimensional cubes allow developers to write actions into cubes supporting hyperlinks, tabular is much simpler for users familiar with Excel databases. Additionally, the tabular storage engine, called VertiPaq, includes a columnar database structure. This structure makes retrieving requested column values incredibly fast. Tabular models can also support vast quantities of data. In fact, tabular models can support upwards of 10 billion rows, given a system has the right infrastructure, CPU, RAM, and storage solutions.
• How to create a cube in SSAS

  How to create a cube in SSAS

  An OLAP cube helps to optimize data. It is also used to analyze data quickly. Creating an OLAP cube allows for rapidly extracting data from multiple dimensions and tables.

  To create an OLAP cube using Microsoft SQL Server follow the below steps:

  1. Create a data warehouse in the Microsoft SQL Server studio.
  2. Create a new analysis service project in the Microsoft Business Intelligence Development Studio.
  3. Create a new data source by right-clicking on Data Sources in Solution Explorer. Then choose “Available Connections.” Alternatively, create a new connection and click the “Next” button. Choose the “Inherit” option and click the “Next” button.
  4. Click “Finish” to create a new data source.

  You will then create a new data source view by right-clicking on the “Data Source Views” tab in the Solution Explorer. Click the “Next” button to select a data source before clicking the “Next” button again. Move the Fact Table in the right pane, click on the “Add Related Tables” button, and then click “Next.” Enter the data source view name and click the “Finish” button. This will create a new data source.

  Finally, to create a new cube, right-click on the “Cubes” tab in Solution Explorer, click “Next,” select “Fact Table,” click “Next,” and then select the measure for the desired fact table. Click “Next” again to select dimension tables, click “Next,” name the cube, and click “Finish.”

  Now, modify the dimensions for queries by going to the Solution Explorer and double-clicking “Dimension.” When the “Dim Product” button appears, drag and drop “Product Name” from beneath and add it to the Attribute Pane on the left side.

  Deploy the project by right clicking the project in Solution Explorer and clicking “Properties,” followed by the “OK” button.

  Right-click the project in Solution Explorer, then click “Deploy.” This will deploy the project, and a message will appear stating that the project has been completed successfully.

  Right-click the project name in Solution Explorer and click “Process”.

  Click the “Run” button to complete the process.

  Right-click the cube and click “Browse.”

  Finally, add dimensions and fact fields to get quick results from the new cube.
• How does an SSAS cube store data?

  How does an SSAS cube store data?

  OLAP cubes, also called multidimensional cubes or hypercubes, are structures that exist to store data in SQL server reporting services. IT professionals can also query the stored data in order to examine systems and solve problems. These functionalities make cubes a crucial component of an effective data warehouse solution.

  Creating and using cubes allows for quick data analysis because they provide IT developers the ability to almost instantly examine both historical and trending data. Cubes also make it possible to slice and dice all the stored data in order to find solutions for a variety of questions relevant to many different areas of interest.

  OLAP cubes can offer access to critical data in SQL Server Analysis Services by automatically organizing data into management packs. Additionally, those cubes can be maintained without user intervention, automatically performing tasks including processing, partitioning, translations and localization, and schema changes. Users can also employ self-service Microsoft business intelligence tools, like Excel, to analyze cube data from different perspectives. The Excel reports can then be saved for future use.
• How to query SSAS cubes

  How to query SSAS cubes

  There are three main languages used to query SSAS cubes. The first is Multidimensional Expressions (MDX). MDX is a query language for OLAP cubes useing a database management system. MDX is a calculation language with syntax similar to the formulas used in querying spreadsheets.

  Data Mining Extensions (DMX) is a language used to create and work with data mining models in Microsoft SQL Server Analysis Services. DMX can be used to create the structure of new data mining models,train these models, and then browse, manage, and predict against them. DMX is composed of data definition language (DDL) statements, data manipulation language (DML) statements, functions, and operators.

  Data Analysis Expressions (DAX) is most often used in tabular mode. This language is like querying relational databases in addition to being the native query language and formula for both Microsoft PowerPivot and Power BI Desktop.
• How to monitor SSAS cube processing

  How to monitor SSAS cube processing

  Sometimes, SSAS can present performance problems difficult to troubleshoot, especially for IT professionals who are new to the platform. To monitor, identify, and resolve SSAS performance challenges quickly and correctly, it is essential to understand where potential bottlenecks might lie and what metrics can help identify the problem areas. Here are the most common performance problems for SSAS:

  • Restarting and crashing: Lack of visibility into concurrent, inefficient, high-impact events can make it challenging to get the complete picture of your SQL server issues.
  • Slow reporting: Slow reporting can cause delays in the delivery of critical data. If SSAS slows to a crawl when you need to deliver reports to stakeholders, your system might need to be optimized for high-concurrency workloads.
  • Wasted time: If your SSAS performance troubleshooting involves setting up traces or DMV queries, then you might be wasting time you could be spending on more strategic initiatives.

  The best way to mitigate these issues is to use an SSAS performance monitoring tool. An automated solution can help you monitor, diagnose, and optimize SSAS servers by providing unparalleled insight into performance issues, whether an organization uses multidimensional or tabular modes. By adopting Sentry, you can quickly identify bottlenecks related to memory and storage systems, aggregation usage, unoptimized queries, and query and processing tasks competing for the same resources.