In DW/BI systems, relational databases are the best place to store and manage data. But the relational database itself is not intelligent enough. The relational database lacks the following features:
- Rich metadata to help users navigate the data and create queries.
- Powerful analytic calculations and functions, defined in context-sensitive query statements.
- Outstanding, consistent query performance in a variety of ad hoc queries.
On the Microsoft platform, the preferred architecture is to use SSAS as the primary presentation database, when the Analsis services database is defined on the relational data warehouse, creating this rich metadata layer, as well as creating a physical storage layer to contain aggregations and indexes. Get outstanding query performance.
Reasons for using Analysis Services
1. User-oriented meta-data
The definition of an OLAP cube highlights elements in the dimension model that improve the user experience, especially ad hoc queries, which contain the differences between facts and dimensions, hierarchies and approaches, grouping of attributes and facts, and the ability to merge facts from multiple business processes through a consistent dimension. These elements are defined once on the SSAS server and can be applied to any customer tool without further configuration.
2. Calculation
You can also define business calculations, such as profits, sales up to date, and sales for the same period last year. These calculations are generally done by the development team, and users can share them with them.
Complex security rules: One challenge for ad hoc access to relational data warehouses is to protect detailed data, but allow access to aggregated data, especially for ad hoc use. SSAS supports complex security rules to better protect your data.
3. Query performance
Analysis Services can provide efficient dimension query performance.
4. Aggregation Management
In Dw/bi systems, the most important way to improve performance is to define aggregations. Aggregation is the sum of the pre-computed and stored details of the fact table. Aggregations are not special data: they are simply summary tables of different granularity, such as summaries by month and geographic regions. In relational databases, they are called aggregation tables and become aggregations in SSAS. For query performance, it is more valuable to define the appropriate aggregate peso citation. SSAS helps you define and maintain these aggregations. Queries on OLAP cubes can also use the appropriate aggregations seamlessly.
Reasons for not using Analysis Services
1. The market for Analysis Services is not mature enough. Tools, experience, and information materials that use SSAS are less than relational data warehouses
2. Some analysis in OLAP is very difficult. It can be very difficult to analyze data by using events that are implied in the fact table rather than dimension attributes.
Analysis Services OLAP Overview 2