"BI Project Notes" Report Information Analysis cube

In the original: BI Project notes report information Analysis cube

Data Source settings:

Data processing logic:

--handling lost foreign key relationship dataSELECT  * fromT_reportleafgradeWHEREFsubfid not inch(SELECTFID fromt_raceleafreport)DELETE   fromT_reportleafgradeWHEREFsubfid not inch(SELECTFID fromt_raceleafreport)--dealing with supplier relationshipsSELECT  * fromT_raceleafreportWHEREV_customer not inch(SELECTC_custcode fromt_providerinfo)--Change the field typeALTER TABLET_raceleafreportALTER COLUMNV_customerINT --set Primary key to not NULLALTER TABLET_providerinfoALTER COLUMNC_custcodeINT  not NULL--Set Primary KeyALTER TABLET_providerinfoADD CONSTRAINTPk_custcodePRIMARY KEY(C_custcode)--Unit of CarriageSELECT  * fromT_raceleafreportWHEREI_carryid not inch(SELECTI_carryid fromt_racecarryunitinfo)--Transportation ToolsSELECT  * fromT_raceleafreportWHEREI_conveyanceid not inch(SELECTI_conveyanceid fromt_raceconveyance)--Acquisition TypeSELECT  * fromT_raceleafreportWHEREI_purchasetypeid not inch(SELECTI_purchaseid fromT_purchasetype)--type of processing--New I_tlproctypecode FieldALTER TABLET_raceleafreportADDI_tlproctypecodeINT NULLUPDATET_raceleafreportSETT_raceleafreport.i_tlproctypecode=T_tlproctype.i_tlproctypecode fromT_tlproctypeWHERET_raceleafreport.v_tlproctype=T_tlproctype.v_tlproctypeDELETE   fromT_raceleafreportWHEREI_tlproctypecode is NULL--commit flag \ Delete TagDELETE   fromT_raceleafreportWHEREV_issubmit= 'Not submitted'DELETE   fromT_raceleafreportWHEREI_deleteflaginch(1,2 )--New business Date businessdate fieldALTER TABLET_raceleafreportADDBusinessdateINT NULL--Convert datetime to intUPDATET_raceleafreportSETBusinessdate= CAST(REPLACE(CONVERT(CHAR(Ten), D_reportdate, -),'-',                                    "') as INT)                                                                        --origin processingDELETE   fromT_reportleafgradeWHEREI_shapecode not inch(SELECTI_originid fromT_origin)

Dimension tables that need to be extracted:

Serial number	Table name	Description	Note
1	T_providerinfo	Suppliers
2	T_racecarryunitinfo	Unit of carriage
3	T_raceconveyance	Transportation Tools
4	T_gbgradecode	Tobacco leaf Grade
5	T_distinction	Tobacco Grade
6	T_origin	Origin
7	T_purchasetype	Acquisition Type
8	T_tlproctype	Type of processing

The fact table that needs to be extracted:

Serial number	Table name	Description	Note
1	T_raceleafreport	Registration Main Table
2	T_reportleafgrade	Registration Sub-table

Main indicators:
I_piececount Number of pieces _ Total
I_weight Weight _ Total

Project structure:

Browsing effect:

Client effects:

"BI Project Notes" Report Information Analysis cube