Project REAL Analysis Service Technology (1)

Content directory

Project Real
Introduction
Interaction with Analysis Services
Database Design
Data Source and data resource view 
Data Source
Best Practice: Remember your password when working in standard security mode. When you move an object, you often need to enter a password.
Data resource view
Best practices
Use the data source view to expand metadata)
Ignore objects from the data source view
Modify Data Source view
Conclusion
Dimension (dimensions) 
Dimension key
Hierarchies)
Dimension cache in analytics 2005
Attribute Association
Best Practice: spend more time on dimension design and capture this attribute relationship in dimensions
Key uniqueness
Best Practice (required): always ensure the uniqueness of the property key.
Best Practice: Always scan your dimensions to make sure they contain the broad and even distributed members you expected.
Best Practice: If you tell the system that attributes are associated, they must be associated.
Convert a virtual dimension to an attribute level
Best Practice: convert a virtual dimension in SQL Server 2000 to an attribute level
Possible Name conflicts
Promote columns in a relational table to attributes in multi-dimensional design
Data Type that does not match the tinyint key
Unknown Member
Best Practice: Create your own unknown members wherever possible. Use an unknown member generated by the system.
Smart time wizard
Best Practice: place where a single-host time dimension table may be created
Best Practice: In the time dimension wizard, enter a key column rather than a name column for each attribute, for example, year, month, or week.
Measurement Group 
Split table
Other Cube objects
Server Settings
Design choices
How to represent a vendor
Inventory measurement that can be added or subtracted
Best practice: if you use a semi-incrementing metric, make sure that the Cube contains no more than one dimension.
Conclusion
Appendix A: Automatic Table Partitioning 

Project Real

Project Real is Microsoft's effort to provide best practices for creating business intelligence applications. These programs are in Microsoft®SQL Server™2005. This means that the real customer data can be substituted into the system and can cope with the same problems that the customer will encounter during the development process. These problems include:

◆ Model design-relational model and analytical service model
◆ Data extraction, data conversion, and data loading (ETL)
◆ Client system design and development, including data reports and Interactive Analysis
◆ Product system Classification
◆ Management and maintenance of the operating system, including continuous updating of data

Through our work experience in such a real deployment environment, we have gained a complete understanding of how to use these tools. Our goal is to focus on all the problems that large companies encounter during their actual deployment.

This White Paper provides a technical discussion on Analysis Services design and best practices in Project REAL. We have discussed in depth the details of each type of objects, such as data sources, data source views, dimensions, layers, attributes, measurement groups, and table partitions. And pointed out the important problems we encountered in the process of moving forward.

To view the Overview of Rroject REAL, you can view the Project REAL: Technical Overview White Paper. A considerable portion of the materials, tools, and examples are generated in the Project REAL life cycle. To find the latest information, you can view the relevant information (http://www.microsoft.com/ SQL /bi/ProjectReal/) through the Project REAL Web site connection ).

Note: This article is just a draft article that includes some constructive practical methods based on our previous experience in SQL Server 2005 Community Technology Preview (CTP. The description in the White Paper is accurate before the product is released. The product functionality described in this document may change. In the future, we may provide better practical solutions. SQL Server 2005 is a development tool used in our practice routines.

Introduction

This article reviews the technical design of the Project REAL Analysis Service and discusses various design impact issues. We assume that the reader is familiar with the analysis service design and has practiced the Project REAL model. For example, we assume that the reader already knows the existence of many-to-many vendor dimensions. Our discussion focuses on why it exists and the alternatives we have considered before finalizing the design ).

In this article, we examine various types of analysis service objects applied in multi-dimensional design. Start with the physical mode object, such as the data source and Data Source view. Next we will discuss logical objects, such as dimensions, user-defined hierarchies, attribute hierarchies, and measurement groups. Next, we will go deep into metric group features, such as segmentation, aggregate design, and proactive caching ). This part finally discusses other logic designs, including computing, key performance indicators (KPIs), activities, pivoting, custom assembly, user-defined functions (UDFs), and MDX scripts.

In the last chapter, we discuss in detail the two optional and reasonable design schemes in the analysis service model design stage. We have provided the goal, which is what we are trying to do, and what we are doing.

This article ends with an introduction to server settings, mainly discussing why we need to change these configurations.

Project REAL design is strongly dependent on partitioning. Hundreds of such split tables are defined in all measurement groups. in Appendix, we will show you how to solve the management problem of creating and managing the split watchband in various databases.