The SSAS Tabular model uses relational constructs, such as tables and relationships between tables, to provide rapid access to BI data. The model uses the xVelocity analytic engine and different compression algorithms to achieve high performance of analytic queries. Data managed by this engine is always stored in memory. (For the detailed description of in-memory data, see Chapter 21.)
Working with the Tabular model is significantly different from working with the Multidimensional model. As you already know, the Multidimensional model allows you to work offline during the first phases of your project. In other words, you need the SSAS server only when you want to deploy and process your cube.
The Tabular model requires a connection to the SSAS database from the beginning, so that “front end” will be in constant communication with the server during the whole process.
NOTE The Tabular model requires a separate instance of SQL Server Analysis Services. In other words, you need to install two instances of the SSAS server if you want to work with both the Multidimensional model and the Tabular model.
To learn how to create and use a Tabular model project, you first need to understand the notion of the workspace database.
1. Workspace Database
The complete information concerning any Tabular model project is kept in a corresponding workspace database. A workspace database is created on the Analysis Services instance, specified in the Workspace Server property, when you create a new Business Intelligence project by using one of the Tabular model project templates in Visual Studio 2019. Each Tabular model project has its own workspace database. You can use SQL Server Management Studio to view the data stored in your workspace database.
The workspace database resides in memory while the Tabular model project is open. When you close the project, the location in which the workspace database is stored is determined by the Workspace Retention property. There are three different possibilities regarding what happens with your workspace database after the corresponding project is closed:
- Kept in memory
- Unloaded from memory and stored on disk
- Removed
NOTE The location and behavior of the workspace database is controlled by two properties of the Tabular model: Workspace Server and Workspace Retention. The default values for these properties come from the settings. The workspace server is where the particular workspace database is created. This must be a Tabular instance of SSAS. The workspace retention can have one of the three options that were described.
2. Creating a Tabular Model Solution
Similarly to how you create a Multidimensional model solution, you create a Tabular model solution by using Visual Studio 2019. Choose Create a New Project in the initial window of VS 2019, select Analysis Services Tabular Project (refer back to Figure 23-3), and click Next.
The Configure Your New Project page appears, shown in Figure 23-11. In the Name text box, type the name of the project. (I named my project Tab_Project1.) The name you enter will be used as the database name. In the Location drop-down list box, type or select the folder in which to store the files for the project, or click the Browse button (…) to select a folder. In the Solution Name text box, type the name of your solution. The default name of the solution is identical to the name of the created project. Additionally, you can choose the version of .NET Framework that suits your project.
Click Create, and the Tabular Model Designer dialog box appears (see Figure 23-12). Select Integrated Workspace. By choosing this option, Visual Studio 2019 uses a built-in instance, eliminating the need to install a separate Analysis Services server instance just for model authoring.
To create a new project, you must select a version of SQL Server from the Compatibility Level drop-down list. Choose SQL Server 2017 and click Next.
After your project is created, it opens in Visual Studio. On the right side, in Tabular Model Explorer, you see a tree view of the objects in your model. (All folders are empty because you have not yet imported your data.) You can right-click an object folder to perform actions, similar to the menu bar.
Click the Solution Explorer tab. Here, you see your Model.bim file. If you don’t see the designer window to the left (the empty window with the Model.bim tab), in Solution Explorer, under your project, double-click the Model.bim file. The file contains the metadata for your model project.
Click Model.bim. In the Properties window, you see the model properties, most important of which is the DirectQuery Mode property. This property specifies if the model is deployed in In-Memory mode (Off) or DirectQuery mode (On). In our example we will deploy the model in In-Memory mode, so leave the DirectQuery Mode property set to the default, Off.
When you create a model project, certain model properties are set automatically according to the Data Modeling settings, which you can specify by choosing Tools | Options. Data Backup, Workspace Retention, and Workspace Server properties specify how and where the workspace database is backed up, retained in-memory, and built. You can change these settings later, if necessary.
After these introductory steps, you’re ready to look at a Tabular model example.
3. A Tabular Model Example
The process of creating a Tabular model solution comprises, among others, the following steps:
- Create a connection.
- Import data.
- Add measures.
- Deploy the model to the server.
These steps are discussed in the following subsections.
NOTE You can find the detailed description of other possible steps, such as creation of relationships and calculated columns, in Microsoft Docs.
3.1. Creating a Connection
To begin, click the Tabular Model Explorer tab. The source database for this example is the AdventureWorksDW database. To create a connection to the database, in Tabular Model Explorer, right-click Data Sources and choose Import from Data Source. In the Get Data dialog box (see Figure 23-13), click Database in the left pane, choose SQL Server Database in the right pane, and then click Connect.
NOTE The component underlying the Get Data dialog box is Power Query, which provides a vast array of tools for connecting to and reshaping data for modeling and analysis.
In the SQL Server Database dialog box, in the Server field, type the name of the server where you created the AdventureWorksDW database, and then click Connect. When prompted to enter credentials, you need to specify the credentials Analysis Services uses to connect to the data source when importing and processing data. In the Impersonation Mode drop-down list, select Impersonate Account and enter your credentials. (Type your user account in the form computer_name\user_name.) Click Connect to start the next step, importing data.
NOTE The Tab_Project1 project will be based on the same example that I used to create the Multidimensional cube. I intentionally chose to use the same example for both models so that you can compare the capabilities of both models.
3.2. Importing Data
After the successful connection, the Navigator dialog box appears (see Figure 23-14). First, select the AdventureWorksDW database and then click OK. This creates the connection to the database. (If the connection to the database already exists, the system omits this step.) After that, check the check boxes for the following tables:
- DimCustomer
- DimDate
- DimProduct
- DimProductSubcategory
- FactInternetSales
Next, click the Edit button. Data from the selected tables will be imported into the model. Figure 23-14 shows the part of this process, i.e., that the DimCustomer check box is checked, with the corresponding data shown in the right pane. Each imported table appears as a tab at the bottom of the design area.
Now, you can select all imported data or a part of it. To import the subset of data, you have to filter out unnecessary data. Suppose that you do not need import data for customers who have a Spanish or French occupation. To filter them out, in Query Editor, select the DimCustomer table. A view of the DimCustomer table at the data source appears. Right-click the FrenchOccupation column and then click Remove Columns. Repeat the process for the SpanishOccupation column.
After filtering out unnecessary data, you can import the rest of the data. To import your data for processing, click Import. The Data Processing dialog box shows the status of data being imported from your data source into your workspace database (see Figure 23-15). The wizard imports the table data and creates new tables in the model. To save your model project, choose File | Save All.
3.3. Adding Measures
Generally, to add a measure to a Tabular model project, you have to define a formula that combines a field with an aggregate function. In other words, measures are evaluated based on a filter. That way, you inform the model how to handle the quantity when groups are built.
NOTE In contrast to the Multidimensional model, the Tabular model cannot calculate aggregations in advance.
To create measures, you use the measure grid. From the beginning, each table has an empty measure grid. The measure grid appears below a table in the model designer (see Figure 23-16). To hide or show the measure grid for a table, choose Table | Show Measure Grid.
(The alternative way to find the Measure Grid menu is by expanding Tables in Tabular Model Explorer.)
You can create a measure by clicking an empty cell in the measure grid and then typing a formula in the formula bar. (The formula represents your filter for the corresponding table’s column.) When you press enter to complete the formula, the measure then appears in the cell. You can also create measures using a standard aggregation function by clicking a column and then clicking the AutoSum button (£) on the toolbar. Measures created using the AutoSum feature appear in the measure grid cell directly beneath the column, but can be moved.
As an example, add to the TotalProductCost column of the FactlnternetSales table a measure that calculates the sum of all values of the TotalProductCost column. To do this, click the FactInternetSalesTable tab to select the table with the same name. Click the TotalProductCost column title to select the entire column. Click the £ button in the toolbar. That way you create a measure in the measure grid at the bottom of the design area, as shown in Figure 23-16. On the right side of the figure you can see the Properties pane, where the formula for the calculation, SUM([TotalProductCost]), and the name of the measure are presented. (You can rename a measure by clicking the name of the measure and then, in the Properties window, replacing the old name with the new one in the Measure Name field.)
3.4. Deploying the Model to the Server
Deploying a Tabular model project creates a model database in a test, staging, or production environment. Users can then connect to the deployed model by using a data connection directly from reporting client applications.
Deploying is a simple process. However, it requires completing a set of tasks to ensure that your model is deployed to the correct Analysis Services instance and with the correct configuration options.
Tabular models are defined with several deployment-specific properties. When you deploy, a connection to the Analysis Services instance specified in the Server property is established.
A new model database with the name specified in the Database property is then created on that instance, if one does not already exist. Metadata from the model project’s Model.bim file is used to configure objects in the model database on the deployment server. With the Processing Option property, you can specify to deploy just the model metadata to create the model database (the Do Not Process option), or you can specify Default or Full to have the impersonation credentials used to connect to data sources passed in memory from the model workspace database to the deployed model database. (The list of all configuration properties with their detailed description can be found in Microsoft Docs.)
When a model is deployed from Visual Studio, it is automatically processed, unless the Processing Option property is set to Do Not Process. To keep the data in the model up to date, you have to process the data several times. This can be done with the Analysis Services Processing Task editor.
To deploy the project, right-click its name in Solution Explorer and select Deploy.
Before the system starts the deployment process, you are prompted to enter your credentials in the Impersonation Information page. Figure 23-17 shows the deployment process for the Tab_Project1 project.
4. Delivering Data from the Tabular Project
Now that you have seen how to create a Tabular model project and import data, let’s take a look at how data can be retrieved and delivered. The list of interfaces that you can use to access data is almost the same as the corresponding list concerning the Multidimensional model. The following interfaces, among others, can be used:
- Power Pivot for Excel
- Data Analysis Expressions (DAX)
- OLE DB for OLAP
- NET
As you can see from the list, the only significant difference to the corresponding list of interfaces for the Multidimensional model is the existence of DAX instead of MDX. For this reason, this section discusses the Power Pivot for Excel interface as well as the DAX interface.
4.1. Power Pivot for Excel
As introduced earlier in the chapter, Power Pivot for Excel allows you to analyze data using Excel and is a user-friendly way to perform data analysis using features such as Pivot Table, PivotChart views, and slices.
Before you learn how to use this tool, take a look at the advantages of PowerPivot:
- Familiar Excel features for delivering data are available.
- Very large datasets can be loaded from virtually any source.
As you will see in a moment, you can use the same sources that you use for SSAS in almost the same way for PowerPivot.
The integration of PowerPivot with Excel is straightforward. In Visual Studio 2019, choose Extensions | Model | Analyze in Excel. You are prompted for your credentials. (For details, see the description in the previous section.) The Excel initial window appears. In the PivotTable Fields pane, add the created measure from the FactlnternetSales table to Values, CalendarYear from the DimDate table to Columns, and EnglishOccupation to Rows. The PivotTable now gives an aggregated result from the measure by regions and year.
4.2. Data Analysis Expressions
Data Analysis Expressions (DAX) is a formula language used to create custom calculations in SSAS Tabular model projects. DAX formulas include functions, operators, and values to perform advanced calculations on data in tables and columns.
NOTE You can also use DAX with Power Pivot workbooks.
When using DAX to retrieve tabular data, your entire implementation is founded on the evaluate statement. Example 23.3 shows this statement in action.
NOTE All of the following examples can be implemented and executed using Management Studio similarly to the way in which Transact-SQL statements are implemented and executed. To execute these examples, open Management Studio and connect to the Tabular instance of Analysis Services. Expand the Database folder, right-click the database (the Tab_Project1 database in this example), and select New Query.
Example 23.3
evaluate (‘DimCustomer’)
order by ‘DimCustomer'[LastName]
The evaluate statement begins with the evaluate keyword, followed by a table expression enclosed in parentheses. As you can see from Example 23.3, the simplest table expression is one that specifies the name of the table enclosed in single quotes. When you specify only the table name, all rows and columns are returned. Therefore, Example 23.3 returns all rows of the DimCustomer table. (The order by clause is optional. It is used generally to sort rows.
Therefore, in Example 23.3 this clause sorts all rows according to the values of the LastName column of the DimCustomer table.) The result of Example 23.3 is displayed in Figure 23-18.
NOTE The statement in Example 23.3 is analogous to the following SQL statement:
SELECT * FROM DimCustomer ORDER BY LastName;
As you already know from Chapter 22, a typical query on a fact table usually uses some form of grouping numerical values to apply one of the aggregate functions (sum, maximum, minimum, or average). DAX supports the summarize function, which groups data based on specified columns in order to aggregate data on some other column(s). Example 23.4 shows the use of this function.
Example 23.4
evaluate (
summarize(‘FactInternetSales’,
‘FactInternetSales'[TotalProductCost]))
The summarize function is part of the evaluate statement. When you use it, you specify the function name and the arguments passed to this function. The first argument is the name of the table. All subsequent arguments are the columns that you want to include in the result set. For all specified column names, the system returns a summary table for the requested totals over a set of groups. In other words, the combination of values of all specified columns is built, and the identical values are put together in a group. Therefore, in Example 23.4, the summarize function retrieves only the TotalProductCost column from the FactInternetSales table and the values in that column are grouped together.
NOTE The summarize function corresponds to the GROUP BY clause of the SELECT statement.
One of the most important operations on relational tables is to specify a condition that is used to restrict the result set to a subset of tables’ rows. One of the ways to do this in DAX is to use the filter function. This function has two arguments: a table expression and a condition. The table expression can be the name of a table or an expression that returns a table. The condition is a Boolean expression that is evaluated for each row returned by the table expression. Any row for which the expression evaluates to TRUE is included in the result set. Example 23.5 shows how the filter function can be used.
Example 23.5
evaluate (filter
(‘FactInternetSales’,
‘FactInternetSales'[TotalProductCost] > 1000))
order by ‘FactInternetSales'[ProductKey]
Example 23.5 selects all rows from the FactInternetSales table where the values of the TotalProductCost column are greater than 1000. The result of Example 23.5 is shown in Figure 23-19.
NOTE The filter function corresponds to the WHERE clause of the SELECT statement. All Boolean operators, as well as almost all other operators of the WHERE clause, are implemented for the filter function, too.
Another very important relational operator is projection—the operation you know as “SELECT list” in Transact-SQL. Generally, there is not a direct way to retrieve just a subset of table columns in DAX. So, we have to use a workaround to achieve this goal.
One possible way is the use of the summarize function. As you already know, for all specified column names, this function builds the combination of values and displays each group separately. Therefore, we can specify the column(s) that build(s) the primary key of the corresponding table plus all other columns that should be displayed. That way, each combination of values belonging to the table’s primary key builds a separate group, and the values of the rest of the columns will be displayed, too. Example 23.6 shows the use of the summarize function to implement this operator.
Example 23.6
Display the first and last names of all rows in the DimCustomer table:
evaluate (summarize
(‘DimCustomer’,
‘DimCustomer'[CustomerKey],
‘DimCustomer'[FirstName],
‘DimCustomer'[LastName] ))
ORDER BY ‘DimCustomer'[CustomerKey]
The CustomerKey column is the primary key of the DimCustomer table. For this reason, CustomerKey must appear in the column list of the summarize function. The list is extended with the names of the columns (FirstName and LastName) whose values should be displayed. Figure 23-20 shows the output of Example 23.6.
NOTE This section describes only the most important DAX functions. Consult Microsoft Docs to learn more about this language.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.