For an instructor lead, in-depth look at learning SQL click below.
SQL Server Analysis Services (SSAS) Tabular Mode provides a robust platform for in-memory analysis, allowing you to retrieve business intelligence rapidly from large volumes of data. It is a storage mode in SSAS that employs column-based data source storage, thus enhancing the speed of data retrieval and processing.
Introduction to SQL Server Analysis Service (SSAS) Tabular Mode
The SSAS Tabular Mode utilizes an in-memory database, leveraging the xVelocity in-memory analytics engine’s power to deliver quick performance when analyzing massive amounts of data. In-memory analytics primarily loads the data into memory, thereby reducing the need to retrieve data from the disc repeatedly. In simple terms, ‘In-Memory’ makes retrieval lightening quick!
Setting Up
Observe the basic example of SQL code used to create a database in the SQL Server below:
|
1 2 3 4 5 6 7 |
CREATE DATABASE TabularDB GO USE TabularDB GO |
In this SQL code, we first create a new database called ‘TabularDB’ and then select this created database for use.
Creating a Tabular Model Project in SQL Server 2017
Now, we are about to create a new Tabular Model Project. For this, we need to direct select File > New > Project, then choose Analysis Services Tabular Project.
Loading Data into the Model
Load data into the model using the SQL code to create and fill a table:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE Sales ( ProductID int, OrderQty int, SalesYTD money ) INSERT INTO Sales VALUES (1, 100, 23000.00), (2, 200, 35000.00), (3, 300, 40000.00) |
You have created a ‘Sales’ table and inserted three rows of data into it.
Conclusion
By going through this, you have taken your first step into In-Memory Analytics with SQL Server Analysis Services Tabular Mode. Using this mode offers faster performance for retrieving data, and together with SQL Server’s features, provides powerful tools for data analysis.