Microsoft in-memory avathar of BI–SSAS Tabular modelling


SQL 2012 comes with more feature for flexibility ,security and scalability,

  • tabular model or multidimensional model to choose from.
  • Queries and logics using DAX or MDX
  • Storage options – cached and pass-through
  • row or cell level security
  • Vertipaq for high performance and direct query for real time – all with new compression algorithms

Let us look on deeply(information perspective only) to the tabular modelling of SSAS 2012 in the below note.

Tabular model would be a more familiar model for architects and developers  which will enable them to build easily and a faster journey to the solution. Complex modelling concepts will not be available here directly. The primary use of this is to have an analytics wrapper for a simple relational database.

To start with a tabular project

In the BIDS , once we select SSAS there will have 2 projects, one is SSAS multidimensional and the other one is tabular project. There will come a Model.bim file in the solution explorer. Select the file and see the properties of the model. There is a DirectQuery Mode property there which will decide the model should go in memory or not. Direct Query mode if OFF means in-memory mode is ON.

Import from Data Source menu of the model is used to import data tables . Select tables and views by giving enough credential information . We dont have to take all the columns from the table instead we can choose columns by using filter the table data.

We need to mart the date table by selecting Mark as date table option in the date table tab of the model designer to enable time intelligence functions.

DAX

The Data Analysis Expressions (DAX) language is a new formula language that is considered an extension of the formula language in Excel. The DAX statements operate against an in-memory relational data store, comprised of tables and relationships in the PowerPivot workbook. DAX is used to create custom measures and calculated columns.

  • DAX expressions cannot be used to create new rows, only to create new values in columns or measures based on existing data.
  • DAX is not a query language; it is an expression language that is embedded within the MDX statements that are passed to an in-process instance of Analysis Services.
  • Use of DAX expressions is supported only within PowerPivot for Excel.We cannot use measures created by a DAX expression in an instance of Analysis Services that supports traditional OLAP.

Hardware considerations

All in-memory database technologies require large amounts of RAM, so it comes as no surprise that maximizing RAM should be your top priority. Whether plan is to run multiple small to medium size solutions, or just one very large solution, begin hardware search by looking at systems that offer the most RAM that can be afforded.

Because query performance is best when the tabular solution fits in memory, RAM must be sufficient to store the entire database. Depending on how you plan to manage and use the database, additional memory up to two or three times the size of your database might be needed for processing, disaster recovery, and for queries against very large datasets that require temporary tables to perform the calculation.

Disk I/O is not a primary factor in sizing hardware for a tabular solution, as the model is optimized for in-memory storage and data access.

image

Ref: microsoft white papers, microsoft documentation and solution

DAX- Data Analysis Expression


The Data Analysis Expressions (DAX) language is a formula language that allows users to define custom calculations, calculated columns and measures. DAX includes some of the functions that are used in Excel formulas, and additional functions that are designed to work with relational data and perform dynamic aggregation.

DAX formulas are very similar to Excel formulas. To create a formula,type an equal sign in the formula editing dialog box or measure grid, followed by a function name or expression, and any required values or arguments.

Like Excel, DAX provides a variety of functions that you can use perform calculations using dates and times, create conditional values, and work with strings. However, DAX formulas are different in the following important ways:

  • A DAX function always references a complete column or a table. If you want to use only particular values from a table or column, you can add filters to the formula.

  • If you need to customize calculations on a row-by-row basis, DAX provides functions that let you use the current row value or a related value as a kind of parameter, to perform calculations that vary by context.

  • DAX includes many functions that return a table, rather than a value. The table is not displayed in the workbook or model, but is used to provide input to other functions. For example, you can retrieve a table and then count the distinct values in it, or calculate dynamic sums across filtered tables or columns.

  • DAX functions include a variety of time intelligence functions. These functions let you define or select date ranges, and perform dynamic calculations based on these dates or range. For example, you can compare sums across parallel periods.

When creating a formula, it is important to understand the difference between calculated columns and measures. In a calculated column, the same formula is applied to every row in the column, and the formula checks the values in each row, as well as any constants or related values, to calculate the result. For example, the formula might be as simple as =[ThisRowAmount] * 0.5. Even if the table is filtered, the formula applies to all rows, and filtering does not affect the results.

In a measure, however, filters and other forms of context change the values that are used in calculation, and thus affect the results. In a PivotTable, context is determined by the choice of row and column headings, and relationships between tables. If you create a formula that calculates the sum of sales as a measure, the result of the formula when placed in a PivotTable would be dictated by the groupings that you add to the PivotTable. For example, the same formula would yield different sums if you grouped the values by [Product Category] and [Fiscal Year], vs. grouping them by [Calendar Month] and [Sales Territory].

Comparing DAX Functions and Excel Functions

The DAX function library is based on the Excel function library, but the libraries have many differences. This section summarizes the differences and similarities between Excel functions and DAX functions.

  • Many DAX functions have the same name and the same general behavior as Excel functions, but have been modified to take different types of inputs, and in some cases, might return a different data type. Generally, you cannot use DAX formulas in an Excel workbook without modification, and vice versa.

  • Formulas that you write in a PowerPivot workbook are generally portable to tabular models, and vice versa. The exception is models deployed in Direct Query mode, which are subject to some restrictions. For more information, see DirectQuery Mode (SSAS).

  • DAX functions never take a range of cells or a range as reference, but always take a column or table as reference.

  • DAX date and time functions return a datetime data type. In contrast, Excel date and time functions return an integer that represents a date as a serial number.

  • Many DAX functions either return a table of values or make calculations based on a table of values as input. In contrast, Excel has no functions that return a table, but some functions can work with arrays. The ability to easily reference complete tables and columns is a feature in business intelligence semantic models.

  • DAX provides lookup functions that are similar to the array and vector lookup functions in Excel. However, the DAX functions require that a relationship be established between the tables.

  • DAX formulas do not support the variant data type found in Excel. The data in a column is expected to always be of the same data type. When you import data into a model, if the data is not the same type, the VertiPaq engine implicitly converts the entire column to the data type that best accommodates all values

– MSDN

Introduction to BI Semantic Model in SQL Server CTP3 Denali


  • Analysis Services Denali has two enginges: the traditional OLAP one (the only one it had until 2008) and the new Vertipaq one (introduced by PowerPivot and now directly available without SharePoint and/or Excel).
  • In order to cover these two tecnologies under the same hat, a new acronym has been introduced: BISM (Business Intelligence Semantic Model)
  • When yuo create a project for BISM you can use either BISM Multidimensional (corresponding to a classic SSAS project based on the OLAP engine) or BISM Tabular (corresponding to the new project type based on the Vertipaq engine).
  • In documentation, books and article you will read Multidimensional (corresponding to BISM Multidimensional) and Tabular (corresponding to BISM Tabular).
  • Both Multidimensional and Tabular models can be queried by using MDX. However, internal calculation are based on DAX in Tabular and DAX can also be used to query a Tabular model. In a possibly near future, we should expect to use DAX also to query a Multidimensional project (I hope this will happen very very soon – months and not years).
  • When you install Analysis Services Denali you have to choose what type of engine you want to use for the instance you are installing. If you want to be able to use both engines, you need to install two instances of Analysis Services. During the setup you can choose which one you desire and Multidimensional is the default.
  • The UDM acronym (Unified Dimensional Model) is no longer used. An UDM project is now a BISM Multidimensional project. THere is a 100% compatibility for existing projects and from the point of view of the existing feature, BISM Multidimensional is 100% corresponding to UDM in 2008 R2. Any existing book, documentation and whitepaper is still valid in BISM Multidimensional. The Analysis Services release has improvements (there is no longer a 4GB limit for string stores, for example), fix and optimizations, but there aren’t new major features in Multidimensional for this release.

– courtesy Marco Russo