For an instructor lead, in-depth look at learning SQL click below.
For those who are interested in leveraging the power of data analytics, SQL Server Analysis Services (SSAS) is a worthwhile avenue to explore. One of the two modes available in SSAS is the Tabular mode, and it’s an effective tool for creating and deploying a business intelligence semantic model. This mode is used to create tabular models with a relational modelling construct, allowing users to interact with business data, gain insights via analytics reports, and create dashboards in Power BI, Excel and other reporting tools.
Getting Started with SSAS Tabular Mode
Before we deep dive into the creation of a Tabular model, it’s crucial to have SQL Server Data Tools (SSDT) installed along with a running SQL Server Analysis Services instance in Tabular Mode. You need to connect to the instance, and then it’s just a couple of simple steps to start your first project.
|
1 2 3 4 5 6 7 |
--- create a new SSAS tabular project CREATE DATABASE Sample_Tabular_DB AS TABULAR (EXTERNALPATH = 'C:/SampleProjects/Sample_Tabular_DB') |
Uploading Data to Your Tabular Mode
Data is the heart and soul of an SSAS Tabular model project. You can import data from various sources including relational databases, data feeds, Excel files, text files, and more. Let’s walk through a simple SQL code example of data import from a SQL Server database.
|
1 2 3 4 5 6 7 8 9 |
--- import data from SQL Server LOAD DATA INFILE 'C:/TestData/Customers.txt' INTO TABLE Customers FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' |
This code would load data from the customers.txt file into a Customers table.
Creating Measures in SSAS Tabular Mode
Measures are fundamental components of SSAS Tabular mode, offering actionable insights by performing calculations over data. DAX (Data Analysis Expressions) is commonly used to define these measures. Measurement creation can be as simple as the sum of sales or as complex as year-to-date calculations. Here’s a simple example of a measure that aggregates the sum of sales:
|
1 2 3 4 |
--- creating a measure for aggregating sales Sales := SUM([Amount]) |
Conclusion
This is a basic overview of getting started with SQL Server Analysis Services Tabular Mode. There’s plenty more to discover in the space of tabular modeling like hierarchies, perspectives, and partitions along with numerous DAX function possibilities. It is this combination of versatile data handling options and powerful analytics capabilities that make SSAS a remarkable tool for any data analyst’s arsenal.