lessons learned from the Qlikview loads the data from DB into qvd files

来源：互联网发布：非农数据网址编辑：程序博客网时间：2024/05/02 04:29

Qlikview加载数据库里的数据，一般都是通过ODBC和数据库进行连接，然后把数据库里的相应的表里的数据加载到本地的disk folder里，后期就可以直接从用本地的disk folder里的QVD的文件获取数据来做DASHBOARD了。本人一开始加载数据的时候老是会死机，原因是我在加载好一个数据库里的表里数据到本地的QVD文件后，没有及时的从内存里把相应的表给删除掉，导致了多个表都在内存里，而且他们还会通过相同的自动名称进行关联，有时候QLIKVIEW 会自动的产生很多个synthetic的key. 所以在加载数据的时候要及时的把内存里的表给删除掉是个好办法。

下面是我加载数据库里的数据并且把它保存到本来生成QVD 文件的例子：

//Loading the data from DB to local disk folder
BI_CUSTOMER:
LOAD CITY,
    "CUSTOMER_ID",
    "CUSTOMER_INDUSTRY",
    "CUSTOMER_NAME",
    "CUSTOMER_NUMBER",
    LOB,
    "OPERATION_UNIT",
    "ORGANIZATION_ID";
SQL SELECT CITY,
    "CUSTOMER_ID",
    "CUSTOMER_INDUSTRY",
    "CUSTOMER_NAME",
    "CUSTOMER_NUMBER",
    LOB,
    "OPERATION_UNIT",
    "ORGANIZATION_ID"
FROM DW.dbo."BI_CUSTOMER";
STORE BI_CUSTOMER INTO D:\Qlikview_QVD_NEWDW\DW\BI_CUSTOMER.qvd (QVD);
drop table BI_CUSTOMER;

BI_CUSTOMER_ANLY:
LOAD "AR_OS",
    "CUSTOMER_ID",
    "CUSTOMER_NAME",
    "CUSTOMER_NUMBER",
    DSO,
    "DSO_YTD",
    "MARKET_SEGMENT",
    MONTH,
    "OPERATING_UNIT",
    "OU_ID";
SQL SELECT "AR_OS",
    "CUSTOMER_ID",
    "CUSTOMER_NAME",
    "CUSTOMER_NUMBER",
    DSO,
    "DSO_YTD",
    "MARKET_SEGMENT",
    MONTH,
    "OPERATING_UNIT",
    "OU_ID"
FROM DW.dbo."BI_CUSTOMER_ANLY";

STORE BI_CUSTOMER_ANLY INTO D:\Qlikview_QVD_NEWDW\DW\BI_CUSTOMER_ANLY.qvd (QVD);
drop table BI_CUSTOMER_ANLY;

BI_INV_HEADERS:
LOAD "CUSTOMER_ID",
    "CUSTOMER_NAME",
    "CUSTOMER_NUMBER",
    "INVOICE_DATE",
    "INVOICE_NUMBER",
    LOB,
    "OPERATING_UNIT",
    "ORGANIZATION_ID",
    "PRIMARY_SALESREP_ID",
    "TRANSACTION_ID";
SQL SELECT "CUSTOMER_ID",
    "CUSTOMER_NAME",
    "CUSTOMER_NUMBER",
    "INVOICE_DATE",
    "INVOICE_NUMBER",
    LOB,
    "OPERATING_UNIT",
    "ORGANIZATION_ID",
    "PRIMARY_SALESREP_ID",
    "TRANSACTION_ID"
FROM DW.dbo."BI_INV_HEADERS";
STORE BI_INV_HEADERS INTO D:\Qlikview_QVD_NEWDW\DW\BI_INV_HEADERS.qvd (QVD);
drop table BI_INV_HEADERS;

BI_INV_LINES:
LOAD "CCY_CODE",
    "CCY_RATE",
    "CCY_RATE_TO_REPORTING",
    COGS,
    "CONTRIBUTED_MARGIN",
    "GROSS_PROFIT",
    "INVENTORY_ITEM_ID",
    "ITEM_NUMBER",
    "MARKET_SEGMENT",
    "ORDER_HEADER_ID",
    "ORDER_LINE_ID",
    "ORDER_NUMBER",
    "ORDER_TYPE",
    "ORGANIZATION_ID",
    "PRIMARY_QUANTITY",
    "PRIMARY_UNIT_SELLING_PRICE",
    "PRIMARY_UOM_CODE",
    "SALES_CHANNEL",
    "SALES_OFFICE_CODE",
    "SALES_REGION_CODE",
    "SALES_TYPE",
    "SALESREP_ID",
    "TRANSACTION_ID",
    "TRX_DATE";
SQL SELECT "CCY_CODE",
    "CCY_RATE",
    "CCY_RATE_TO_REPORTING",
    COGS,
    "CONTRIBUTED_MARGIN",
    "GROSS_PROFIT",
    "INVENTORY_ITEM_ID",
    "ITEM_NUMBER",
    "MARKET_SEGMENT",
    "ORDER_HEADER_ID",
    "ORDER_LINE_ID",
    "ORDER_NUMBER",
    "ORDER_TYPE",
    "ORGANIZATION_ID",
    "PRIMARY_QUANTITY",
    "PRIMARY_UNIT_SELLING_PRICE",
    "PRIMARY_UOM_CODE",
    "SALES_CHANNEL",
    "SALES_OFFICE_CODE",
    "SALES_REGION_CODE",
    "SALES_TYPE",
    "SALESREP_ID",
    "TRANSACTION_ID",
    "TRX_DATE"
FROM DW.dbo."BI_INV_LINES" WHERE TRX_DATE>'2012-03-31';
STORE BI_INV_LINES INTO D:\Qlikview_QVD_NEWDW\DW\BI_INV_LINES.qvd (QVD);
drop table BI_INV_LINES;

BI_ITEM:
LOAD BRAND,
    "CAT_SEG1",
    "CAT_SEG2",
    COO,
    "INVENTORY_ITEM_ID",
    "ITEM_CODE",
    "ITEM_DESCRIPTION",
    LOB,
    MANUFACTURER,
    MODEL,
    "ORGANIZATION_CODE",
    "ORGANIZATION_ID",
    SERIES,
    "UOM_CODE";
SQL SELECT BRAND,
    "CAT_SEG1",
    "CAT_SEG2",
    COO,
    "INVENTORY_ITEM_ID",
    "ITEM_CODE",
    "ITEM_DESCRIPTION",
    LOB,
    MANUFACTURER,
    MODEL,
    "ORGANIZATION_CODE",
    "ORGANIZATION_ID",
    SERIES,
    "UOM_CODE"
FROM DW.dbo."BI_ITEM";
STORE BI_ITEM INTO D:\Qlikview_QVD_NEWDW\DW\BI_ITEM.qvd (QVD);
drop table BI_ITEM;

BI_ITEM_ANLY:
LOAD DOS,
    "DOS_YTD",
    "INVENTORY_ITEM_ID",
    "ITEM_NUMBER",
    LOB,
    MONTH,
    "ORGANIZATION_CODE",
    "ORGANIZATION_ID";
SQL SELECT DOS,
    "DOS_YTD",
    "INVENTORY_ITEM_ID",
    "ITEM_NUMBER",
    LOB,
    MONTH,
    "ORGANIZATION_CODE",
    "ORGANIZATION_ID"
FROM DW.dbo."BI_ITEM_ANLY";
STORE BI_ITEM_ANLY INTO D:\Qlikview_QVD_NEWDW\DW\BI_ITEM_ANLY.qvd (QVD);
drop table BI_ITEM_ANLY;

BI_OU:
LOAD "ccy_code",
    "organization_id",
    "ou_code",
    "ou_name",
    "short_name";
SQL SELECT "ccy_code",
    "organization_id",
    "ou_code",
    "ou_name",
    "short_name"
FROM DW.dbo."BI_OU";
STORE BI_OU INTO D:\Qlikview_QVD_NEWDW\DW\BI_OU.qvd (QVD);
drop table BI_OU;

BI_SALESREPS:
LOAD "END_DATE_ACTIVE",
    "SALES_OFFICE_CODE",
    "SALES_OFFICE_NAME",
    "SALES_REGION_CODE",
    "SALES_REGION_NAME",
    "SALESREP_ID",
    "SALESREP_NAME",
    "SALESREP_NUMBER";
SQL SELECT "END_DATE_ACTIVE",
    "SALES_OFFICE_CODE",
    "SALES_OFFICE_NAME",
    "SALES_REGION_CODE",
    "SALES_REGION_NAME",
    "SALESREP_ID",
    "SALESREP_NAME",
    "SALESREP_NUMBER"
FROM DW.dbo."BI_SALESREPS";
STORE BI_SALESREPS INTO D:\Qlikview_QVD_NEWDW\DW\BI_SALESREPS.qvd (QVD);
drop table BI_SALESREPS;

BI_SALESREPS_ANLY:
LOAD "AR_OS",
    DSO,
    "DSO_YTD",
    MONTH,
    "OPERATING_UNIT",
    "OU_ID",
    "SALESREP_ID",
    "SALESREP_NAME",
    "SALESREP_NUMBER";
SQL SELECT "AR_OS",
    DSO,
    "DSO_YTD",
    MONTH,
    "OPERATING_UNIT",
    "OU_ID",
    "SALESREP_ID",
    "SALESREP_NAME",
    "SALESREP_NUMBER"
FROM DW.dbo."BI_SALESREPS_ANLY";
STORE BI_SALESREPS_ANLY INTO D:\Qlikview_QVD_NEWDW\DW\BI_SALESREPS_ANLY.qvd (QVD);
drop table BI_SALESREPS_ANLY;

从上面可以看出，我加载完一个表后就把此表从内存中删除掉了，这样会释放出内存给后面的表来使用。

0 0