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

SSIS Excel Source error -“Text was truncated or one or more characters had no match in the target code page.”


When the driver determines that an Excel column contains text data, the driver selects the data type (string or memo) based on the longest value that it samples. If the driver does not discover any values longer than 255 characters in the rows that it samples, it treats the column as a 255-character string column instead of a memo column. Therefore, values longer than 255 characters may be truncated. To import data from a memo column without truncation, you must make sure that the memo column in at least one of the sampled rows contains a value longer than 255 characters, or you must increase the number of rows sampled by the driver to include such a row. You can increase the number of rows sampled by increasing the value of TypeGuessRows under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel registry key. For more information, see PRB: Transfer of Data from Jet 4.0 OLEDB Source Fails w/ Error. –MSDN

The default sample row count is 8 !!!

Easy workaround is to paste 300 characters to the first row of the excel sheet, and then recreate the package. Then it will detect the row data type as Unicode Text. This is a one time process and once it is deployed it will not demand anything like this. Happy coding !!