SQLServerAnalysis Service学习资料.ppt
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
– Classify by Business – Classify by process – Classify by data (history and temporary) Recommend by process – Bidw_org original data storage – Bidw_fact fact data storage – Bidw_dim dimension data storage
• • • •
Database Design
• • • • • • • Database design File Group design History and temporary table design No Partition but view Database link Log table Increment ETL
– – – – Universal dimension from different department. Dimension design include NULL value or violate constraint Define mid-exchange table But we didn’t control the data quality caused by man-made
Subject-Oriented
• Subject-Oriented data is organized around major subject areas of an enterprise and is useful for an enterprise-wide understanding of those subjects. For Example, a banking operational system maintains independent records of customer savings, loans, and other transactions. A warehouse pulls this independent data together to provide financial information. The data from diverse sources is transformed so that it is consistent and meaningful for the warehouse. So the main work is around the fact table.
File Group design Database design can be classified:
– – – – Classify by Business Classify by process Classify by data Classify by physical storage
Recommend by physical storage and business – History data – Temporary data – index
– Dimension data process – Original data process – Fact data process
• OLAP partition and Increment • Tune
Database design
Database design can be classified:
• • •
Time-Variant
• Warehouse data is by nature historical; data is retained for a long time, from two to ten years, compared with one to three months of data for a typical operational system. The data allows for analysis of past and present trends, and for forecasting, using what-if scenarios. Base the Time-Variant and data volume, we must consider : The design about the database The design about the table The OLAP increment
Database Link
Database link means linked server. it can connect the different data source. Such as Oracle, Sybase... It can be used as follows:
1. 2. SELECT * FROM DatabaseLink..usename.table SELECT * FROM OPENQUERY(DatabaseLink, 'SELECT * FROM table')
SQL Server 2000 Analysis Service
Agenda
• Data Warehouse Concept • Database Design (Physics and Logic) • Backup
Data Warehouse Concept
A data warehouse is a subject-oriented, integrated, nonvolatile, time-variant collection of data designed to support management DSS needs.
– DTS design and schedule – The dimension data is incremental, it is to say that they can be inserted and updated, but can’t be deleted. And dimension data must be unique. – The original or fact data is incremental, but according to requirement, it can be updated or deleted under the control. The full process is danger and impossible when the data volume is too huge.
•
Nonvolatile
• Typically, data in the data warehouse is read-only (less volatile than operational systems). Data is loaded into the data warehouse for the first-time load, and then refreshed regularly. Warehouse data is accessed by business users. Warehouse operations typically involve: Loading the initial set of wareBaidu Nhomakorabeaouse data (often called the first-time load) Refreshing the data regularly (called the refresh cycle) So the main work is around:
No Partition but View
It is still the performance bottleneck of SQL Server.
– SQL Server 2000 not support the partition – SQL Server 2000 encounter bottleneck when the data volume is more than 10,000,000. – But It support view union. – The large-volume table can be divide into different table like tablename_yyyymm, then create a view to union them. Like create view v_fact_table as select * from t_fact_table_200601 union all select * from t_fact_table_200602 union all select * from t_fact_table_200603
– – – – – – Original data from data source Dimension Measurement dimension granularity Star model or snowflake model OLAP model
• •
Integrated
• • Data on a given subject is integrated. In many organizations, data resides in diverse independent systems, making it difficult to integrate the information into a single set of meaningful data from analysis. A key characteristic of a warehouse is that data is completely consolidated or integrated. Data is structured in a globally accepted manner, even when the underlying source data is structured differently (conforming dimension). Integration and transformation processes can be time-consuming and costly. It requires commitment from every part of the organization, particularly top-level managers who make the decisions and allocate resources and funds. So the main work may be like :
Recommend:
1 is very clear and understandable 2 can utilize fully the index of database linked. It is more efficient than 1. So Dimension table can adopt 1 Original table can adopt 2
history and temporary table
The large-volume table can be divided into:
– Temporary table – History table It may be inconvenient. But it can improve the performance, temporary table is used to make fact table quickly. Temporary data should be transfer into history table periodically.
• • • •
Database Design
• • • • • • • Database design File Group design History and temporary table design No Partition but view Database link Log table Increment ETL
– – – – Universal dimension from different department. Dimension design include NULL value or violate constraint Define mid-exchange table But we didn’t control the data quality caused by man-made
Subject-Oriented
• Subject-Oriented data is organized around major subject areas of an enterprise and is useful for an enterprise-wide understanding of those subjects. For Example, a banking operational system maintains independent records of customer savings, loans, and other transactions. A warehouse pulls this independent data together to provide financial information. The data from diverse sources is transformed so that it is consistent and meaningful for the warehouse. So the main work is around the fact table.
File Group design Database design can be classified:
– – – – Classify by Business Classify by process Classify by data Classify by physical storage
Recommend by physical storage and business – History data – Temporary data – index
– Dimension data process – Original data process – Fact data process
• OLAP partition and Increment • Tune
Database design
Database design can be classified:
• • •
Time-Variant
• Warehouse data is by nature historical; data is retained for a long time, from two to ten years, compared with one to three months of data for a typical operational system. The data allows for analysis of past and present trends, and for forecasting, using what-if scenarios. Base the Time-Variant and data volume, we must consider : The design about the database The design about the table The OLAP increment
Database Link
Database link means linked server. it can connect the different data source. Such as Oracle, Sybase... It can be used as follows:
1. 2. SELECT * FROM DatabaseLink..usename.table SELECT * FROM OPENQUERY(DatabaseLink, 'SELECT * FROM table')
SQL Server 2000 Analysis Service
Agenda
• Data Warehouse Concept • Database Design (Physics and Logic) • Backup
Data Warehouse Concept
A data warehouse is a subject-oriented, integrated, nonvolatile, time-variant collection of data designed to support management DSS needs.
– DTS design and schedule – The dimension data is incremental, it is to say that they can be inserted and updated, but can’t be deleted. And dimension data must be unique. – The original or fact data is incremental, but according to requirement, it can be updated or deleted under the control. The full process is danger and impossible when the data volume is too huge.
•
Nonvolatile
• Typically, data in the data warehouse is read-only (less volatile than operational systems). Data is loaded into the data warehouse for the first-time load, and then refreshed regularly. Warehouse data is accessed by business users. Warehouse operations typically involve: Loading the initial set of wareBaidu Nhomakorabeaouse data (often called the first-time load) Refreshing the data regularly (called the refresh cycle) So the main work is around:
No Partition but View
It is still the performance bottleneck of SQL Server.
– SQL Server 2000 not support the partition – SQL Server 2000 encounter bottleneck when the data volume is more than 10,000,000. – But It support view union. – The large-volume table can be divide into different table like tablename_yyyymm, then create a view to union them. Like create view v_fact_table as select * from t_fact_table_200601 union all select * from t_fact_table_200602 union all select * from t_fact_table_200603
– – – – – – Original data from data source Dimension Measurement dimension granularity Star model or snowflake model OLAP model
• •
Integrated
• • Data on a given subject is integrated. In many organizations, data resides in diverse independent systems, making it difficult to integrate the information into a single set of meaningful data from analysis. A key characteristic of a warehouse is that data is completely consolidated or integrated. Data is structured in a globally accepted manner, even when the underlying source data is structured differently (conforming dimension). Integration and transformation processes can be time-consuming and costly. It requires commitment from every part of the organization, particularly top-level managers who make the decisions and allocate resources and funds. So the main work may be like :
Recommend:
1 is very clear and understandable 2 can utilize fully the index of database linked. It is more efficient than 1. So Dimension table can adopt 1 Original table can adopt 2
history and temporary table
The large-volume table can be divided into:
– Temporary table – History table It may be inconvenient. But it can improve the performance, temporary table is used to make fact table quickly. Temporary data should be transfer into history table periodically.