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

Advertisements

Power View–SQL Server 2012(code named Denali)


Power View which was previously named as Project Crecent is an SQL Server 2012 reporting service add-in for sharepoint 2010 and excel power pivot for sql server 2012.

Power View (RDLX) and Power Pivot (XLSX)  samples can be downloaded at http://www.microsoft.com/download/en/details.aspx?id=26718#overview

ScreenShot_2012-04-13_13-35-00Power view is an interactive data exploration, visualization, and presentation experience. It provides intuitive ad-hoc reporting for business users such as data analysts, business decision makers, and information workers. They can easily create and interact with views of data from data models based on PowerPivot workbooks published in a PowerPivot Gallery, or tabular models deployed to SSAS instances. Power View is a browser-based Silverlight application launched from SharePoint Server 2010 that enables users to present and share insights with others in their organization through interactive presentations.

 

HIGHLIGHTS OF POWER VIEW

  • Presentation Ready :  A fine look and file for presentation. A WYSWYG , so it works with real data and no need top preview it to see how it looks. There are reading view and full screen view. Also an interactive power view can be exported to PPT slide. Power view can also be published to share point , so that user can view and interact with them.
  • Data Model based : It is a thin web client which is downloaded through the browser from a sharepoint 2010 data model( a power pivot model workbook or a tabular model running on an ssas.
  • Visual Design experiences : There is no separtion of design time and run time. we can switch between views and change the perspective as simple as working with an excel sheet with its ribbon controls
  • Creating Data Visualizations : A wide variety of visualizations available like tables, matrices, charts, graphs, bubble charts etc., and we can switch between those very quickly and easily.
  • Highlighting and filtering data:  Different types of filters are available. A filter can be applied to a visualization or globaly. We can also highlight the data using the filters in the visualisations
  • Sort : Sort is basically applied for anything
  • Reports with multiple views : A report can have multiple views with different visualisations and different filters.
  • Performance : it fetches data only that is needed for a particular visualisation. This is beneficial for visualisation based from millions of records.

Ref: Technet library

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