AAloudataDataAloudataDataAlwaysReady引言01為什么需要關(guān)注數(shù)據(jù)編織02數(shù)據(jù)編織價(jià)值實(shí)現(xiàn)機(jī)制06數(shù)據(jù)編織價(jià)值評(píng)估方法16數(shù)據(jù)編織價(jià)值案例介紹19總結(jié)與展望24引言@Aloudata2024ODS層DWD層DIM層DWS層ADS層ODS層DWD層DIM層DWS層ADS層@Aloudata202403@Aloudata2024@Aloudata2024業(yè)務(wù)需求描述DW源表目標(biāo)表業(yè)務(wù)需求理解DW源表目標(biāo)表報(bào)表@Aloudata2024ETL需求理解ETL數(shù)據(jù)探查ETL鏈路設(shè)計(jì)DW MYSQL數(shù)據(jù)源 MYSQLMYSQL數(shù)據(jù)源 MYSQL07@Aloudata2024傳統(tǒng)ETL需求實(shí)現(xiàn)2--假設(shè)上述三張表已經(jīng)采集到數(shù)據(jù)倉(cāng)庫(kù)內(nèi)部35,CREATETABLEwide_order6stat_dateDATE7order_idVARCHAR648member_idVARCHAR649member_levelvARCHAR64--增加會(huì)員等級(jí)10product_idvARCHAR64product_categoryvARCHAR64--增加產(chǎn)品類別12order_amountNUMBER1416,INSERTINTowide_orderorder_idmember_idmember_levelproduct_idproduct_categoryorder_amount17,SELECTaorder_idamember_idbmember_levelaproduct_idaproduct_categoryaorder_amount18,FROMorderaLEFTJOINmemberboNamember_id=bmember_id19vLEFTJOINproductcONaproduct_idcproduct_id20vastat_date12123vCREATETABLEt_rpt_a24stat_dateDATE25member_levelvARCHAR6426order_amountNUMBER272830vINSERTINTotaskt_rpt_astat_datemember_levelorder_amount31SELECTstat_datemember_levelsumorder_amount32FROMWide_order33GROUPBystat_datemember_level34WHEREastat_date=current_date-13537CREATETABLEt_rpt_b38stat_dateDATE39product_categoryvARCHAR6408@Aloudata20244040order_amountNUMBER4244vINSERTINTocategory_order_amountstat_dateproduct_categoryorder_amount45SELECTstat_dateproduct_categorysumorder_amount46FROMWide_order47GROUPBystat_dateproduct_category48WHEREastat_date=current_date-1數(shù)據(jù)編織需求實(shí)現(xiàn)VARCHAR2,CREATEORREPLACEVIEwwide_orderbiz_dateVARCHAR3,SELECTaorder_idamember_idbmember_levelaproduct_idcproduct_categoryaorder_amount4,FROMorderaLEFTJOINmemberboNamember_id=bmember_id5vLEFTJOINproductcONaproduct_idcproduct_id6,WHEREastat_date='$biz_date'710USING12measuresorder_amountSUM13partitionbystat_date1409@Aloudata20242FROMgausstpcdscatalog_salesa3JOINmysqltpcdscall_centerboNacs_call_center_sk=bcc_callL_center_sk4GROUPBybcc_class@Aloudata2024100條數(shù)據(jù)抽取100條數(shù)據(jù)抽取2億數(shù)據(jù)抽取100條數(shù)據(jù)抽取100條數(shù)據(jù)抽取100條數(shù)據(jù)抽取2億數(shù)據(jù)抽取100條數(shù)據(jù)抽取11@Aloudata2024舉個(gè)例子說(shuō)明數(shù)據(jù)投影技術(shù),假設(shè)系統(tǒng)已構(gòu)建出如下數(shù)據(jù)投影,稱之為DP1,其SQL邏輯如下:11,SELECT2ss_addr_sk3ss_sold_date_sk4FROMstore_salesLEFTJOINdate_dim5ONSs_sold_date_skd_date_sk當(dāng)有如下的查詢SQL提交給數(shù)據(jù)虛擬化引擎時(shí),11,SELECT2ss_addr_sk3ss_sold_date_sk4FROM5store_salesINNERJOINdate_dim6ONSs_sold_date_skd_date_sk7WHEREd_date_sk>2451167數(shù)據(jù)虛擬化引擎會(huì)自動(dòng)進(jìn)行識(shí)別和改寫(xiě),其改寫(xiě)結(jié)果如下:11,SELECT2ss_addr_sk3ss_sold_date_sk4FROMDP15WHEREd_date_sk>2451167當(dāng)有更復(fù)雜的SQL提交給數(shù)據(jù)虛擬化引擎時(shí),比如:11,SELECTSS_addr_skss_sold_date_sk2FROM3SELECTSS_addr_sk,ss_sold_date_sk4FROMStore_sales5WHERESs_sold_date_sk=24511677INNERJOINdate_dim8ONSs_sold_date_skd_date_sk12@Aloudata2024數(shù)據(jù)虛擬化引擎同樣會(huì)進(jìn)行識(shí)別和改寫(xiě),其改寫(xiě)結(jié)果如下:11,SELECT2ss_addr_sk3ss_sold_date_sk4FROMDP15WHEREd_date_sk=2451167product_IDproduct_IDDivision_ID13@Aloudata20242FROMFact_ordera3GROUPByaproduct_id當(dāng)出現(xiàn)以下查詢SQL時(shí),11,SELECTbproduct_qualitysuMorder_factsales_quantitySUMorder_factsales_amount2FROMFact_orderaLEFTJOINDim_productboNaproduct_id=bproduct_id3GROUPBYbproduct_quality該查詢SQL會(huì)被改寫(xiě)成:11,SELECTbproduct_qualitySUMasales_qua2FROMDpaLEFTJOINDim_productboNaproduct_id=bproduct_id3GROUPBYbproduct_quality14@Aloudata2024可以用以下2個(gè)指標(biāo)進(jìn)行衡量:當(dāng)天需求滿足率當(dāng)天需求滿足率當(dāng)天數(shù)據(jù)動(dòng)銷率當(dāng)天數(shù)據(jù)動(dòng)銷率@Aloudata202430%70%30%70%ETETL生產(chǎn)力變革物理建模代碼開(kāi)發(fā)Aloudlatc數(shù)據(jù)工程開(kāi)發(fā)(面向數(shù)據(jù))傳統(tǒng)數(shù)據(jù)工程開(kāi)發(fā)(面向系統(tǒng))ETETL存算成本變革數(shù)據(jù)消費(fèi)者邏輯數(shù)據(jù)模型物理數(shù)據(jù)模型原始數(shù)據(jù)傳統(tǒng)數(shù)據(jù)工程開(kāi)發(fā)(數(shù)據(jù)物理搬運(yùn),先生產(chǎn)后消費(fèi),存在浪費(fèi)的存算)關(guān)系投影關(guān)系投影AloudAloudata數(shù)據(jù)工程開(kāi)發(fā)(數(shù)據(jù)邏輯集成,以消定產(chǎn),減少存算的浪費(fèi))17@Aloudata2024效率6張表16個(gè)任務(wù)成本存儲(chǔ)計(jì)算計(jì)算存儲(chǔ)計(jì)算memberproductorderwide_order10億*1億*100萬(wàn)wide_orderwide_ordert_rpt_rpt_a10億聚合dp_auto10*20010億*1億*100萬(wàn)t_t_rpt_b10億聚合總結(jié)@Aloud@Aloudata2024S@Aloudata2024S相較于傳統(tǒng)ETL通過(guò)頻繁拼接各類寬表、接口表和改變指標(biāo)管理模式改變指標(biāo)管理模式改變指標(biāo)開(kāi)發(fā)模式改變指標(biāo)開(kāi)發(fā)模式改變指標(biāo)消費(fèi)模式改變指標(biāo)消費(fèi)模式20@Aloudata2024證券行業(yè)背景痛點(diǎn)場(chǎng)景方案應(yīng)用產(chǎn)品方案亮點(diǎn)客戶行業(yè)證券行業(yè)背景痛點(diǎn)場(chǎng)景方案應(yīng)用產(chǎn)品方案亮點(diǎn)客戶行業(yè)傳統(tǒng)"數(shù)倉(cāng)+BI"的架構(gòu)難以適應(yīng)新時(shí)代數(shù)智化需求邏輯化數(shù)據(jù)集成整合+"管研用一體"的指標(biāo)開(kāi)發(fā)與管理AloudataAIR邏輯數(shù)據(jù)編織平臺(tái)+AloudatacAN自動(dòng)化指標(biāo)平臺(tái)業(yè)務(wù)價(jià)值業(yè)務(wù)價(jià)值21