Microsoft SQL Server Analysis Services - SSAS (Tabular DAX)

Download SQL Server Developer Edition 2022 from the free specialized editions. Choose a custom install to access SSAS, SSIS, and SSRS, with the developer edition free but not for production.

Learn how to install SQL Server Developer 2022, configure database engine and Analysis Services in tabular mode, choose the developer edition, set authentication, and prepare the instance for front-end setup.

Install the GA version of SQL Server Management Studio and connect to the default instance with dot or localhost. SSMS 20 may require trusting the certificate or enabling optional encryption.

Install visual studio 2022 to build SQL Server Analysis Services projects, meeting 64-bit Windows and hardware requirements, and explore older 2019 and free community editions for data solutions.

Install Visual Studio 2022 and the Microsoft Analysis Services projects 2022 extension, then create mdx analysis and analysis services multidimensional or tabular projects.

Discover how to convert Visual Studio 2019 Community Edition from a trial dialog to a full license by updating the license and signing in with a Microsoft account.

Download and attach the AdventureWorks DW data, restore the database in SQL Server Management Studio, and explore fact internet sales with dim product tables to start an SSAS tabular model.

Create your first SSAS tabular model in Visual Studio by setting up a tabular project, connecting to SQL Server, and importing tables like Fact Internet Sales.

Explore creating a pivot table analysis from our SSAS model by importing the fact internet sales table, configuring pivot fields in Excel, and creating an explicit measure for sales amount.

Add a second table from the existing data source, link fact internet sales to dim product via product key, and analyze sales by product description and name.

Import data from databases, mdx sources, and excel or text files into the tabular model, then configure server names, credentials, database names, and file paths, and test the connection.

Explore how to manage and configure columns in SSAS tabular models: create relationships, copy data, insert, rename, freeze or hide columns from client tools, adjust width, and use perspectives.

Demonstrates how updated source data requires processing the SSAS tabular model to refresh partitions and update the snapshot, then refreshing the Excel pivot table to reflect new product keys.

Create implicit and explicit measures in SSAS tabular, switch calculations from sum to max, and name and format measures for sales analysis using the max of sales amount.

Explore how perspectives in extensions model hide or reveal columns and tables for different teams, switching between English and French views.

Create an SSAS tabular model by importing data from SQL Server Adventureworks DW, define relationships, add a sum of sales amount measure, and build a pivot table in Excel.

Construct a dim sales territory hierarchy with country and region, test in Excel, hide key columns, format currency, and create measures for tax and freight, plus an English perspective.

Discover pivot table design: configure rows, columns, and values; switch layouts (compact, outline, tabular); adjust subtotals, grand totals, repeat labels, blanks, and include filtered items in totals.

Explore pivot table options in SSAS tabular models and learn to show items with no data on rows and columns, enabling analysis of unsold products across tables.

Learn to query a SSAS tabular data model by converting pivot tables into formulas, using cube value and cube member functions to sum sales by due date and promotion key.

Practice activity four shows manipulating pivot tables in SAS tabular: switch to outline layout, add subtitles, and create running total of sales by region with a slicer for English education.

Learn to create basic calculated columns in SSAS tabular with Visual Studio, perform arithmetic and string operations, reference other columns, and use colon equals for formulas.

Explore DAX functions for SSAS tabular, focusing on the IF function to create calculated columns from a logical test. Test results in pivot tables and manage slicers and refresh.

Use the blank and is blank functions in Tabular DAX to produce blanks instead of zeros, ensuring accurate counts and averages in sales analysis.

Explore combining conditions in dax with and, or, not and comparison operators, including less than, greater than, and their inclusive forms, and simplifying nested ifs in tabular ssas.

Learn the logical functions in ssas tabular dax, including coalesce, if, and if error, with a practical example of handling division by zero by returning blanks.

Create calculated columns to build full names, handle null middle names, and flag customers with both house and car, then classify US versus outside US in sales territory.

Explore measures as aggregations over a column in SSAS (Tabular DAX) and distinguish them from calculated columns, then review standard functions like sum, average, count, count A, max, and min.

Discover how to aggregate calculations on order quantity using sumx, avoiding helper columns by evaluating expressions row by row for exact totals.

Explains the distinct count function in SSAS tabular DAX, showing how it counts unique values (excluding blanks) using a measure on sales order line numbers across product categories.

Explore rounding functions in ssas tabular dax, including int, trunk, ceiling, floor, iso dot ceiling, and em round, to eliminate rounding errors in pivot table totals.

Explore the abs and sign functions, using absolute values and sign to build a switch-based logic that classifies sales amounts as less, same, or more.

Explore trigonometric and statistical functions, including degrees and radians conversion, and learn how to use rank.eq to rank values in a dataset, with practical examples like sales amount.

Round income per person to the thousand with round or mround. Then rank with rank.eq. Use sign, switch, and abs to show above, in line, or below target of 10,000.

Explore text extraction and substitution in ssas tabular dax using left, mid, right, and len to retrieve characters, and replace or substitute to modify text and build labels.

Explore other text functions in SSAS tabular DAX, including concatenate and concatenatex, combined values with delimiters, distinct, exact, lower and upper, repeat, Unicode, and trim.

Explore information functions in SSAS tabular DAX, including contains, contains rows, contains string, isblank, mod, and is error for handling errors like division by zero.

Parse addresses and birth dates by building dax calculated columns with functions like find, search, left, mid, substitute, and trim; handle errors and format dates.

Discover how to use lookupvalue and related to fetch values from related tables in SSAS tabular DAX, with practical examples linking sales, product, and category data.

Explore the difference between related and related table in SSAS, navigate from many-to-one to one-to-many, and apply sumx with related table to compute context-driven totals.

Learn to create a measure in the fact internet sales table that calculates the percentage of sales amount by dividing current context sum by the total using the all function.

Learn how to use sumx and calculate to compute sales amounts within different contexts. Apply filter to drill down the data, such as restricting fact internet sales by promotion key.

Learn to remove filters from the context with all and all except, using category, promotion key, and currency key, and compute the grand total and percentage of sales amount.

Learn how allselected removes individual context filters from rows and columns while retaining others, yielding accurate visual totals in SSAS tabular models.

Practice activity demonstrates creating a calculated country column with lookup value, using related and relatedtable for context, and building a sales amount measure. It shows filtering with calculate and filter, using all to compute grand totals and ignoring country context, then visualizing results in a pivot table by education and sales territory.