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