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.
Ref: microsoft white papers, microsoft documentation and solution
Bineesh 