425 lines
10 KiB
Plaintext
425 lines
10 KiB
Plaintext
table Fact_Retail_RawData
|
|
lineageTag: b02c67d9-0d03-4db3-8668-9e2f4a28b49b
|
|
|
|
column product_desc
|
|
dataType: string
|
|
lineageTag: 5627e3c0-a662-4420-99f7-34b40eaade81
|
|
summarizeBy: none
|
|
sourceColumn: product_desc
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
column product_type
|
|
dataType: string
|
|
lineageTag: 98f031c6-6a82-4e95-bd97-b18e1449006f
|
|
summarizeBy: none
|
|
sourceColumn: product_type
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
column common_name
|
|
dataType: string
|
|
lineageTag: c8334c63-ab4e-4644-8c56-97be83998a1e
|
|
summarizeBy: none
|
|
sourceColumn: common_name
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
column brand
|
|
dataType: string
|
|
lineageTag: 6a79d035-6c16-4f38-a52f-62e9f5449b93
|
|
summarizeBy: none
|
|
sourceColumn: brand
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
column corp
|
|
dataType: string
|
|
lineageTag: a37c78b2-bd4d-4be9-ac7a-97f284a1d426
|
|
summarizeBy: none
|
|
sourceColumn: corp
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
column corporation
|
|
dataType: string
|
|
lineageTag: 1517e875-e55c-4ec6-9a9f-7388034822a1
|
|
summarizeBy: none
|
|
sourceColumn: corporation
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
column corp_type
|
|
dataType: string
|
|
lineageTag: 9da51c95-60fa-4179-afba-64eb64c73f1c
|
|
summarizeBy: none
|
|
sourceColumn: corp_type
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
column sales_quarter
|
|
dataType: string
|
|
lineageTag: 4171e102-4745-43ca-965a-e061fab9fed3
|
|
summarizeBy: none
|
|
sourceColumn: sales_quarter
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
column ytd
|
|
dataType: string
|
|
lineageTag: 819a89cb-1615-4420-b491-fe9250efeeee
|
|
summarizeBy: none
|
|
sourceColumn: ytd
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
column sales_amount
|
|
dataType: double
|
|
lineageTag: 3c40bd05-4b1a-4047-936e-a63760782871
|
|
summarizeBy: sum
|
|
sourceColumn: sales_amount
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
annotation PBI_FormatHint = {"isGeneralNumber":true}
|
|
|
|
column rx_type
|
|
dataType: string
|
|
lineageTag: e59c0aeb-a4d7-4a4e-b9f1-6124d72fa0e0
|
|
summarizeBy: none
|
|
sourceColumn: rx_type
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
column top1_brand_gr
|
|
dataType: double
|
|
lineageTag: b5bfc542-1a70-44a2-bbbb-60cbec244646
|
|
summarizeBy: sum
|
|
sourceColumn: top1_brand_gr
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
annotation PBI_FormatHint = {"isGeneralNumber":true}
|
|
|
|
column top1_brand_val
|
|
dataType: string
|
|
lineageTag: e75286e3-0857-475e-9e18-143bf110b3ab
|
|
summarizeBy: none
|
|
sourceColumn: top1_brand_val
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
column top2_brand_gr
|
|
dataType: double
|
|
lineageTag: 93e6c98c-e002-4e28-89ae-fcc020dd496b
|
|
summarizeBy: sum
|
|
sourceColumn: top2_brand_gr
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
annotation PBI_FormatHint = {"isGeneralNumber":true}
|
|
|
|
column top2_brand_val
|
|
dataType: string
|
|
lineageTag: fffce2a6-c8cf-486b-aaa6-f11b53fba7d2
|
|
summarizeBy: none
|
|
sourceColumn: top2_brand_val
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
column top1_incremental_brand
|
|
dataType: string
|
|
lineageTag: ccdf6e3b-8633-42c4-bce3-c6b385b700da
|
|
summarizeBy: none
|
|
sourceColumn: top1_incremental_brand
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
column top1_incremental_gr
|
|
dataType: double
|
|
lineageTag: 64002b9e-13fe-4baf-a152-d9e59f76c3d6
|
|
summarizeBy: sum
|
|
sourceColumn: top1_incremental_gr
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
annotation PBI_FormatHint = {"isGeneralNumber":true}
|
|
|
|
column top2_incremental_brand
|
|
dataType: string
|
|
lineageTag: 3e0eda45-9cbf-40c2-9b19-f91644bca7a5
|
|
summarizeBy: none
|
|
sourceColumn: top2_incremental_brand
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
column top2_incremental_gr
|
|
dataType: double
|
|
lineageTag: cad398da-41e6-4e89-bd80-416df8f3d4d6
|
|
summarizeBy: sum
|
|
sourceColumn: top2_incremental_gr
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
annotation PBI_FormatHint = {"isGeneralNumber":true}
|
|
|
|
column ytd_gr
|
|
dataType: double
|
|
lineageTag: 996f2bdc-f9d4-49d6-9e2b-75e5f0197488
|
|
summarizeBy: sum
|
|
sourceColumn: ytd_gr
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
annotation PBI_FormatHint = {"isGeneralNumber":true}
|
|
|
|
column ytd_ms
|
|
dataType: double
|
|
lineageTag: 2a1f7820-f452-4c0b-8cae-b94a2b385ef7
|
|
summarizeBy: sum
|
|
sourceColumn: ytd_ms
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
annotation PBI_FormatHint = {"isGeneralNumber":true}
|
|
|
|
column ytd_delta_ms
|
|
dataType: double
|
|
lineageTag: 1030dcaf-1c22-481c-ad75-0a3874fc6ebb
|
|
summarizeBy: sum
|
|
sourceColumn: ytd_delta_ms
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
annotation PBI_FormatHint = {"isGeneralNumber":true}
|
|
|
|
column dtp_name
|
|
dataType: string
|
|
lineageTag: 8ea0cfa9-27ea-4bec-a433-4815319868a4
|
|
summarizeBy: none
|
|
sourceColumn: dtp_name
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
column file_name
|
|
dataType: string
|
|
lineageTag: 7ee042f1-a2f4-456d-9bd6-22db39ad7a73
|
|
summarizeBy: none
|
|
sourceColumn: file_name
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
column RANK
|
|
dataType: string
|
|
lineageTag: 04df1b12-45b4-405d-911b-60e1b54ffad9
|
|
summarizeBy: none
|
|
sourceColumn: RANK
|
|
sortByColumn: RANK_RN
|
|
|
|
changedProperty = SortByColumn
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
column DATA_SOURCE
|
|
dataType: string
|
|
lineageTag: 415115ab-0fa6-4f42-b6df-d2c8f5adb5e6
|
|
summarizeBy: none
|
|
sourceColumn: DATA_SOURCE
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
column sales_amount_total
|
|
dataType: double
|
|
lineageTag: 2536f122-5c3e-40e6-b36d-55d99bef55ac
|
|
summarizeBy: sum
|
|
sourceColumn: sales_amount_total
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
annotation PBI_FormatHint = {"isGeneralNumber":true}
|
|
|
|
column sales_amount_ly
|
|
dataType: double
|
|
lineageTag: 7b6001e8-cc93-4bb6-bb27-78391a9cf79a
|
|
summarizeBy: sum
|
|
sourceColumn: sales_amount_ly
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
annotation PBI_FormatHint = {"isGeneralNumber":true}
|
|
|
|
column sales_amount_ly_total
|
|
dataType: double
|
|
lineageTag: d6b0341f-49d9-4331-9cb5-6c828ed2b8c3
|
|
summarizeBy: sum
|
|
sourceColumn: sales_amount_ly_total
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
annotation PBI_FormatHint = {"isGeneralNumber":true}
|
|
|
|
column TA
|
|
dataType: string
|
|
lineageTag: 1b4081b8-20b8-41e0-94ec-0e20b5a0dca4
|
|
summarizeBy: none
|
|
sourceColumn: TA
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
column RC
|
|
dataType: string
|
|
lineageTag: 545338dc-515d-4bc8-b944-cb4e53d9caac
|
|
summarizeBy: none
|
|
sourceColumn: RC
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
column yyyymm
|
|
dataType: string
|
|
lineageTag: d2325d04-9618-4991-af65-1798adf36a36
|
|
summarizeBy: none
|
|
sourceColumn: yyyymm
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
column DATE_KEY = ```
|
|
IF(SEARCH("q",[sales_quarter],1,0)=0,
|
|
DATE(LEFT([sales_quarter],4),MID([sales_quarter],5,2),1)
|
|
,DATE(LEFT([YYYYMM],4),RIGHT([YYYYMM],2),"01")
|
|
)
|
|
```
|
|
formatString: Short Date
|
|
lineageTag: 8a5254de-e927-4713-9696-db986455bf48
|
|
summarizeBy: none
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
column RANK_RN
|
|
dataType: int64
|
|
formatString: 0
|
|
lineageTag: dc505c25-8240-4413-9474-20f2c454f9c0
|
|
summarizeBy: sum
|
|
sourceColumn: RANK_RN
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
column SOURCE_TYPE
|
|
dataType: string
|
|
lineageTag: 93aad509-bbb6-405c-9c1c-863475b39920
|
|
summarizeBy: none
|
|
sourceColumn: SOURCE_TYPE
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
column TA_NAME
|
|
dataType: string
|
|
lineageTag: f6ba58c2-8e36-42eb-98ba-de849d54c194
|
|
summarizeBy: none
|
|
sourceColumn: TA_NAME
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
column SOURCE_TYPE2
|
|
dataType: string
|
|
lineageTag: 935dfaec-af56-4725-815f-0ca8a01a7889
|
|
summarizeBy: none
|
|
sourceColumn: SOURCE_TYPE2
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
column SOURCE_TYPE3
|
|
dataType: string
|
|
lineageTag: 68efca56-e9a7-459f-8861-cbcbb4d44dae
|
|
summarizeBy: none
|
|
sourceColumn: SOURCE_TYPE3
|
|
|
|
annotation SummarizationSetBy = Automatic
|
|
|
|
partition Fact_Retail_RawData = m
|
|
mode: import
|
|
queryGroup: OLD\FreeReport独有表
|
|
source = ```
|
|
let
|
|
Source =
|
|
Value.NativeQuery(
|
|
Databricks.Catalogs(
|
|
ServerAddress, HttpPath, [
|
|
Catalog = CatalogName,
|
|
Database = null,
|
|
EnableAutomaticProxyDiscovery = null,EnableQueryResultDownload="0"
|
|
]
|
|
){
|
|
[Name = CatalogName, Kind = "Database"]
|
|
}[Data],
|
|
"SELECT
|
|
CASE
|
|
WHEN RANK1 = 99 THEN 'Total'
|
|
WHEN RANK1 = 98 THEN ''
|
|
ELSE RANK1
|
|
END RANK,CASE
|
|
WHEN RANK1 = 'Total' THEN 99
|
|
ELSE INT(rank1)
|
|
END RANK_RN,
|
|
product_desc,
|
|
product_type,
|
|
common_name,
|
|
brand,
|
|
corp,
|
|
corporation,
|
|
corp_type,
|
|
sales_quarter,
|
|
ytd,
|
|
CAST(sales_amount AS DECIMAL(32, 15)) sales_amount,
|
|
rx_type,
|
|
CAST(top1_brand_gr AS DECIMAL(32, 15)) top1_brand_gr,
|
|
top1_brand_val,
|
|
CAST(top2_brand_gr AS DECIMAL(32, 15)) top2_brand_gr,
|
|
top2_brand_val,
|
|
top1_incremental_brand,
|
|
CAST(top1_incremental_gr AS DECIMAL(32, 15)) top1_incremental_gr,
|
|
top2_incremental_brand,
|
|
CAST(top2_incremental_gr AS DECIMAL(32, 15)) top2_incremental_gr,
|
|
CAST(ytd_gr AS DECIMAL(32, 15)) ytd_gr,
|
|
CAST(ytd_ms AS DECIMAL(32, 15)) ytd_ms,
|
|
CAST(ytd_delta_ms AS DECIMAL(32, 15)) ytd_delta_ms,
|
|
dtp_name,
|
|
file_name,
|
|
'Retail(Quarterly)' DATA_SOURCE,
|
|
CAST(sales_amount_total AS DECIMAL(32, 15)) sales_amount_total,
|
|
CAST(sales_amount_ly AS DECIMAL(32, 15)) sales_amount_ly,
|
|
CAST(sales_amount_ly_total AS DECIMAL(32, 15)) sales_amount_ly_total,
|
|
NVL(source_name, 'Retail Total') TA,
|
|
RC,
|
|
yyyymm,
|
|
CASE
|
|
WHEN file_name LIKE '%大区%' THEN '大区'
|
|
WHEN file_name LIKE '%集团%' THEN '集团'
|
|
WHEN file_name LIKE '%产品%' THEN '产品'
|
|
ELSE file_name
|
|
END SOURCE_TYPE,
|
|
CASE
|
|
WHEN source_name LIKE '%GI%' THEN 'GI'
|
|
ELSE NVL(source_name, 'Retail Total')
|
|
END TA_NAME,
|
|
rx_otc as SOURCE_TYPE2,
|
|
top_incre as SOURCE_TYPE3
|
|
FROM
|
|
DM.dm_zk_retail_overview_data
|
|
"
|
|
& UsingLimit,
|
|
null,
|
|
[
|
|
EnableFolding = true
|
|
]
|
|
)
|
|
in
|
|
Source
|
|
```
|
|
|
|
annotation PBI_ResultType = Table
|
|
|
|
annotation PBI_NavigationStepName = Navigation
|
|
|
|
annotation TabularEditor_TableGroup = 05_FactTable
|
|
|