该报告是2006年10月林子雨在北京大学数据库实验室攻读博士学位期间制作的。Outline: Project introduction;Real-Time Data Warehousing: Challenges and Solutions;Our Research Work;Reference

林子雨

Xiamen University Institutional Repository

Ziyu LinPeking UniversityOct 19, 2006Survey Report on Real-time Data WarehousesDepartment of Computer Science and Technology, Peking University, Oct 19, 2006Project introductionReal-Time Data Warehousing: Challenges and SolutionsOur Research WorkReferenceOutline Department of Computer Science and Technology, Peking University, Oct 19, 2006Project BackgroundReporting Analysis Prediction Operationalize Activate1 2 3 4 5Department of Computer Science and Technology, Peking University, Oct 19, 2006Scalable, Real-Time and Active MPP based Data Warehouse For Telecommunications Industryadvance the Real-Time Active Data Warehouse(RTADW) technology on a scalable, paralleldatabase system platformdemonstrate its applicability in tackling emergingbusiness system challenges in thetelecommunication industry.Project ObjectiveDepartment of Computer Science and Technology, Peking University, Oct 19, 2006Project introductionReal-Time Data Warehousing: Challenges and SolutionsOur Research WorkReferenceOutline Department of Computer Science and Technology, Peking University, Oct 19, 2006Real-Time Data Warehousing: Challenges and Solutions4 – scalability & query contention3 – OLAP queries vs. changing data2 – modeling real-time fact tables1 – enabling real-time ETLDepartment of Computer Science and Technology, Peking University, Oct 19, 2006Challenge 1：Enabling Real-time ETL– almost all ETL tools andsystems, whether based onoff-the-shelf products orcustom-coded, operate in abatch mode.– ETL process typicallyinvolves downtime of thedata warehouseETL in batch mode– there can't be any systemdowntime– The requirements forcontinuous updates with nowarehouse downtime aregenerally inconsistent withtraditional ETL tools andsystems.Real-time ETLProblemStatementDepartment of Computer Science and Technology, Peking University, Oct 19, 2006Near real-time ETLReal-time ETLExternal Real-time Data CacheDirect trickle feedTrickle & Flip12 34Challenge 1：Enabling Real-time ETLDepartment of Computer Science and Technology, Peking University, Oct 19, 2006Challenge 2：Modeling Real-time Fact Tables– where the real-time data is stored– how best to link it into the rest of the data modelProblem StatementDepartment of Computer Science and Technology, Peking University, Oct 19, 2006Modeling as Usual with Direct Fact Table FeedModeling Real-time Fact TablesModeling with an External Real-time Data CacheSeparate Real-time PartitionIntegrated Real-time through Views12 34Challenge 2：Modeling Real-time Fact TablesDepartment of Computer Science and Technology, Peking University, Oct 19, 2006Challenge 3:OLAP Queries vs. Changing Data– OLAP and Query tools were designed to operate on top of unchanging, static historical data – the results may be negatively influenced by data changes concurrent to query execution– relational OLAP tools are particularly sensitive to this problemProblem StatementDepartment of Computer Science and Technology, Peking University, Oct 19, 2006OLAP Queries vs. Changing DataUse a Near Real-time ApproachUse an External Real-time Data CacheRisk Mitigation for True Real-time123Challenge 3:OLAP Queries vs. Changing DataDepartment of Computer Science and Technology, Peking University, Oct 19, 2006Challenge 4: Scalability & Query Contentionthe issue of query contention and scalability is the most difficult issue facing organizations deploying real-time data warehouse solutions.1Problem Statementin a real-time system, the additional burden of continuously loading and updating data further strains system resources. 1the contention between complex selects and continuous inserts tends to severely limit scalability 1Department of Computer Science and Technology, Peking University, Oct 19, 2006Reverse Just-in-time Data MergeJust-in-time Information MergeSimplify and Limit Real-time ReportingApply More Database HorsepowerSeparate & Isolate in a Real-time Data CacheScalability & Query Contention51234Challenge 4: Scalability & Query ContentionDepartment of Computer Science and Technology, Peking University, Oct 19, 2006Project introductionReal-Time Data Warehousing: Challenges and SolutionsOur Research WorkReferenceOutline Department of Computer Science and Technology, Peking University, Oct 19, 2006Introduction•OLAP and Query tools: designed to operate on top of unchanging, static historical data assume that the underlying data is not changing the results they produce may be negatively influenced by data changes concurrent to query executionIn some cases, this can lead to inconsistent and confusing query results, which is called internal inconsistency of report.Department of Computer Science and Technology, Peking University, Oct 19, 2006Introduction0:00 create table TEMP1(Category_Id LONG, DOLLARSALES     DOUBLE)0:01 insert into TEMP1select all.[Category_Id] AS Category_Id,sum (all.[Tot_Dollar_Sales]) AS DOLLARSALESfrom [YR_CATEGORY_SLS] allgroup by  all.[Category_Id]0:05 create table TEMP2 (ALLPRODUCTSD DOUBLE)0:06 insert into TEMP2select sum((all.[Tot_Dollar_Sales]) ASALLPRODUCTSDfrom [YR_CATEGORY_SLS] all0:08 select distinct pa1.[Category_Id] AS Category_Id,all.[Category_Desc] AS Category_Desc,all.[DOLLARSALES] AS DOLLARSALES,(pa1.[DOLLARSALES]/pa2.[ALLPRODUCTSD]) AS DOLLARSALESCfrom    [TEMP1] pa1,[TEMP2] pa2,[LU_CATEGORY] allwhere     pa1.[Category_Id]=all.[Category_Id]0:09 drop table TEMP10:10 drop table TEMP2Table 1: A multi-pass SQL statementDepartment of Computer Science and Technology, Peking University, Oct 19, 2006The description of layer-based view approachLayer 1 Layer 2 Layer 3Layer 1Layer 2Layer 3This is what we seeFigure Layer technology used in painting softwareDepartment of Computer Science and Technology, Peking University, Oct 19, 2006The description of layer-based view approachLayer 1 Layer 2This is what OLAP toolssee at time T2data at time T1 with the top-right part lockedchanged data betweenT1 and T2 in locked areaView 1 ( Layer 1+ Layer 2 )timeT1 T2When to update already read-locked data, Layer 2 is generated above Layer 1.Layer 1Layer 2timeT1 T2read lockwrite lockFigure Layer technology used in this paperDepartment of Computer Science and Technology, Peking University, Oct 19, 2006The architecture of real-time OLAPData SourceETLRTDC7695116128607693102.740608010012014000年 05年 10年 15年预测1预测2Data WarehouseOLAPbatch loadData SourceData Sourcereal-time loadFigure The architecture of real-time OLAP Department of Computer Science and Technology, Peking University, Oct 19, 2006The architecture of real-time OLAP•Data modeling no special data modeling is required generally modeled identically to the data warehouse typically contains only the tables that are real-time.•Data integrating batch loading  change data capture•Data merging JIM (Just-in-time information merging ) RJIM (Reverse Just-in-time information merging)Department of Computer Science and Technology, Peking University, Oct 19, 2006Project introductionReal-Time Data Warehousing: Challenges and SolutionsOur Research WorkReferenceOutline Department of Computer Science and Technology, Peking University, Oct 19, 2006Reference[1]  Langseth, J., "Real-Time Data Warehousing: Challenges and Solutions", DSSResources.COM, 02/08/2004.[2] Hongfei Guo, Per-Ake Larson, Raghu Ramakrishnan, Jonathan Goldstein. Relaxed currency and consistency: how to say "good enough" in SQL. In SIGMOD 2004.P:815-826.[3] Robert M. Bruckner and A.M. Tjoa. Managing Time Consistency for Active Data Warehouse Environments. DaWaK 2001, LNCS 2114, pp. 254–263, 2001.[4] Robert M. Bruckner and A.M. Tjoa. Capturing Delays and Valid Times in Data Warehouses—Towards Timely Consistent Analyses. Journal of Intelligent Information Systems, 19:2, 169–190, 2002[5] Zaniolo, C., Ceri, S., Faloutsos, C., Snodgrass, R.T., Subrahmanian,V.S., and Zicari, R. Advanced DatabaseSystems. San Francisco: Morgan Kaufmann Publishers. 1997.[6] Gregersen, H. and Jensen, C.S. Temporal Entity-Relationship Models—A Survey. IEEE Transactions on Knowledge and Data Engineering, 11(3), 464–497, 1999.Department of Computer Science and Technology, Peking University, Oct 19, 2006Reference[7] Widom, J. (1995). Research Problems in Data Warehousing. In Proc. of the 5th Intl. Conference on Information and Knowledge Management (CIKM), Baltimore, Maryland (pp. 25–30). ACM-Press.[8] Yang, J.. Temporal DataWarehousing. Ph.D. Thesis, Department of Computer Science, Stanford University. 2001.[9] Yang, J. and Widom, J. .Maintaining Temporal Views over Nontemporal Information Sources for Data Warehousing. In Proc. of the 6th Intl. Conf. On Extending Database Technology (EDBT), Valencia, Spain, Springer LNCS Vol. 1377 (pp. 389–403). 1998.[10] Yang, J. and Widom, J.. Temporal View Self-Maintenance. In Proc. of the 7th Intl. Conf. On Extending Database Technology (EDBT), Konstanz, Germany, Springer LNCS, Vol. 1777 (pp. 395–412). 2000.[11] Pedersen, T.B., Jensen, C.S., and Dyreson, C.E.. A Foundation for Capturing and Querying Complex Multidimensional Data. Information Systems, 26(5), 383–423.2001.Department of Computer Science and Technology, Peking University, Oct 19, 2006Reference[12] Tho, M. Nguyen; Tjoa, A. Min. ―Zero-Latency Data Warehousing for Heterogeneous Data Sources and Continuous Data Streams‖;Proceedings of iiWAS 2003, Fifth International Conference on Information and Web-based Applications Services, Jakarta, Indonesia; Austrian Computer Society (OCG) (2003), 3-902134-72-0; 55–64.[13] S. S. Conn. OLTP and OLAP data integration: a review of feasible implementation methods and architectures for real time data analysis. In: Southeast Con, 2005. Proceedings. IEEE., pages 515-520, 2005.[14] Itamar Ankorion. Change Data Capture – Efficient ETL for Real-Time BI. Article published in DM Review Magazine, January 2005 Issue.[15] T. Thalhammer and M. Schrefl. Realizing active data warehouses with off-the-shelf database technology. software-Practice & Experience, ACM, 32(12), pages 1193-1222, 2002. Department of Computer Science and Technology, Peking University, Oct 19, 2006北京大学计算机系数据库实验室 2006年8月18日主动数据仓库调研报告Department of Computer Science and Technology,  Peking University, Oct , 2006

Survey Report on Real-time Data Warehouses

https://dspace.xmu.edu.cn/bitstream/2288/71565/1/Survey%20Report%20on%20Real-time%20Data%20Warehouses.pdf

Survey Report on Real-time Data Warehouses

Abstract

Similar works

Full text

Available Versions

Xiamen University Institutional Repository