Excel crosstabs, pivot tables FAQ and best practices


1.3. Oqy Excel cube configuration file contents




The .oqy excel configuration file used in the example has the following contents:

QueryType=OLEDB
Version=1
CommandType=Cube
Connection=Provider=MSOLAP;
Initial Catalog=[OCWCube];
Data Source=C:\etltools.cub;
CreateCube=CREATE CUBE [OCWCube] ( 
DIMENSION [YEAR_ID],LEVEL [All] TYPE ALL, LEVEL [YEAR_ID],
LEVEL [QUARTER_ID],
LEVEL [MONTH_ID],LEVEL [WEEK_ID], 
DIMENSION [CUST_GROUP_NAME],LEVEL [All] TYPE ALL,LEVEL [CUST_GROUP_NAME],
LEVEL [CUST_ID], 
DIMENSION [COUNTRY_TEXT],LEVEL [All] TYPE ALL,LEVEL [COUNTRY_TEXT], 
DIMENSION [INV_CURRENCY],LEVEL [All] TYPE ALL,LEVEL [INV_CURRENCY], 
DIMENSION [KAM_NAME],LEVEL [All] TYPE ALL,LEVEL [KAM_NAME], 
DIMENSION [BRAND_TEXT],LEVEL [All] TYPE ALL,LEVEL [BRAND_TEXT],
LEVEL [PROD_TEXT], 
MEASURE [Sum Of WEIGHT_NET]FUNCTION SUM, 
MEASURE [Sum Of TURNOVER_EUR]FUNCTION SUM);
InsertInto=INSERT INTO OCWCube([WEEK_ID], [MONTH_ID], [QUARTER_ID], 
[YEAR_ID].[YEAR_ID], 
[KAM_NAME].[KAM_NAME], [PROD_TEXT], [BRAND_TEXT].[BRAND_TEXT], [CUST_ID], 
[CUST_GROUP_NAME].[CUST_GROUP_NAME], [COUNTRY_TEXT].[COUNTRY_TEXT], 
[Sum Of WEIGHT_NET], [INV_CURRENCY].[INV_CURRENCY], [Sum Of TURNOVER_EUR])
OPTIONS ATTEMPT_ANALYSISSELECT F_SALES_DN.WEEK_ID, F_SALES_DN.MONTH_ID, 
F_SALES_DN.QUARTER_ID, F_SALES_DN.YEAR_ID, F_SALES_DN.KAM_NAME, 
F_SALES_DN.PROD_TEXT, F_SALES_DN.BRAND_TEXT, F_SALES_DN.CUST_ID, 
F_SALES_DN.CUST_GROUP_NAME,
 F_SALES_DN.COUNTRY_TEXT, F_SALES_DN.WEIGHT_NET, F_SALES_DN.INV_CURRENCY, 
F_SALES_DN.TURNOVER_EURFROM `C:\etltools`.F_SALES_DN F_SALES_DN;
Source_DSN="DSN=etltools;DBQ=C:\etltools.mdb;DriverId=25;FIL=MS Access;
MaxBufferSize=2048;PageTimeout=5;";
UseExistingFile=True
CommandText=OCWCube

Back to the list of all excel crosstabs FAQ topics