AnalysisServiceTabularModel # 002Analysisservices Structure

Analysisservice2012ubuntureoneproduct, TwoModels in the previous SQLServer2008R2 analytics service only has one version, which is the familiar multi-dimensional data model MultidimensionalModel. However, in SQLServer2012, there are more than this multi-dimensional data model.

Analysis Service 2012 Architecture One Product, Two Models in the previous SQL Server 2008 R2 version of the Analysis Service, in fact, only One version is our familiar multi-dimensional data Model multidimen1_model. however, in SQL Server 2012, there are more than this multi-dimensional data model.

Analysis Service 2012 Architecture-One Product, Two Models

In SQL Server 2008 R2, the analytics service has only one version-multidimensional data that we are familiar.ModelMultidimen1_model. However, in SQL Server 2012ModelThere is anotherModel-TableModelTabular Model.

Multi-dimensional dataModelAnd tablesModelEssentially two completely differentProductTherefore, when installing the Analysis Service, You must select the multidimen1_model or Tabular Model method for the installed instance.ModelThe underlying layer is the same in many aspects, but it is still two differentProductSo you can only select either of them during installation.ModelProject under cannot be converted to each other, if you need to install anotherModelYou need to reinstall the new instance, and then select the appropriateModel.

TableModelTabular Model

In the tableModelMedium, tableModelThe database is the highest level. The concept of this database is very similar to the relational database we are familiar. the analytic service instance can contain many databases, and each database is associated with a separate business solution or project. Therefore, it does not exist. For example, cross-database query is required when a report is created.

TableModelThe design is completed in SSDT (SQL Server Data Tools), so each project in SSDT corresponds to a database of the Analysis Service.

TableModelThe database is also composed of many tables. The tables here are similar to those in relational databases, but tablesModelThe table in has requirements on the column type. For example, the column type is intelligent Integer, text, or Decimal type.

What's different from relational databases is that tablesModelYou can define the relationship between tables in the query phase. Unlike normal SQL statements, the relationship between all query tables must be pre-defined before they can be used.ModelThe relationship between tables can be identified as Active or inactive. In the query stage, the relationship between the table and the table can be determined during actual calls.

TableModelThe relationship between tables can only be one-to-many, and only single-column Association is allowed. There is no one-to-one or multiple-to-many relationship, and the self-referenced association relationship is not supported.

TableModelThe memory analysis engine is used, which is equivalent to copying a copy of data from the disk to the memory, so that even if the analysis service restarts the disk, the data will not be lost.

TableModelThe xVelocity in-memory analytics engine is used in-memory columnar storage technology, which greatly improves query performance. Relational databases use traditional row-based storage formats.

For more information about xVelocity, visit the http://msdn.microsoft.com/zh-cn/library/hh922900.aspx

In the tableModelThe query and expression defined in is a DAX Statement (Data Analysis eXpressions), which is a tableModelAnd the local query language used by the workbook. Use the client tool Power View to generate a DAX query from the tableModelBut based on the tableModelThe database also supports MDX queries, so it is compatible with client query tools that support MDX.

To increase user experience, you can directly go to the tableModelTable Design LevelStructureModel.

The multidimen1_model multi-dimensional dataModel

Multi-dimensional data at the top layerModelAnd tablesModelAlmost the same. Data is managed by the analytics Service database. The database is designed in SSDT or earlier versions of BIDS (BIDS only support multi-dimensional data ).Model.

Data is organized in a series of cubes and Dimension, unlike tables.ModelTable is used directly. in addition, each Cube contains one or more Measure groups, and each Measure Group corresponds to a Fact Table in the data warehouse. each Cube contains two types of Dimension: one is a common Dimension and the other is Measure Dimension. there are many other concepts. Multi-dimensional data setsModelNow it is very mature, and the functions and features are perfect, while the tableModelA little lacking.

For example, in terms of data storageModelThere are three storage methods:

• Multidimen1_olap (MOLAP) stores all data in the Analysis Service.
• Relational OLAP (ROLAP) does not store data. When a Cube is requested, the data is queried from a Relational data warehouse.
• Hybrid OLAP (HOLAP) is similar to ROLAP, but some pre-aggregated values can be stored in MOLAP.

Another difference is that multidimen1_model only supports MDX when defining queries and expressions, and many third-party client tools currently support MDX, which has formed an industry standard. however, after SQL Server 2012, Power View supports simultaneous query of multidimen1_model and Tabular Model.

Why Have Two Models?

The multidimenstmmodel is designed based on the 32-bit system with one or two processors and a small memory. However, the development of hardware is aimed at higher efficiency, which has a bottleneck.

The cost of learning the multidimen1_model is relatively high, including some concepts and processing processes like the MDX language, which require a lot of effort to learn, microsoft hopes to make the learning process easier to attract more users.

The growth of the Self-Service BI market requires simpler tools and concepts to help users complete custom BI development.

In the tableModelData types are more streamlined and accurate.ModelIt also gives developers more choices.

How do I select the two? Microsoft also makes it clear that it will not give up the multidimen1_model, And the Tabular Model will not be its replacement product. Therefore, developers can choose the appropriate one based on their knowledge.ModelIn the future, the true trend of analysis services will be towards cloud services. At present, Microsoft is building a cloud-based BIProductSuch as cloud-based analysis Services, SQL Azure, SQL Azure Reporting Services, and Office 365.

Access otherNotesSQL Server 2012 Analysis Services Tabular ModelReadingNotes