Example: Using the Source Editor to Submit User-Written Code for a Cube

< Day Day Up >

This example demonstrates how to use the Source Editor window in SAS ETL Studio to submit user-written OLAP procedure code. The code will store the cube definition in a project repository, then create the specified cube on the file system. You must then run SAS ETL Studio and check in the new cube, just as you would if you had used the Cube Designer to create the cube. The content and structure of the cube is the same as the cube that is described in "Example: Building a Cube from a Star Schema" on page 164.

Preparation

For this example, assume that the following statements are true:

A warehouse project plan identified the need for a SAS cube to support OLAP reporting.
The cube will be based on a star schema in which ORDER_FACT is the central fact table, and CUSTOMER_DIM, GEOGRAPHY_DIM, ORGANIZATION_DIM, and TIME_DIM are the dimension tables. For details about this star schema, see "Identifying Targets" on page 34.
The star schema has already been created, and metadata for the star schema has already been added to a metadata repository.
The metadata repository is under change-management control. For details about cubes and change management, see "Working under Change-Management Control" on page 162.
The prerequisites that are described in "Prerequisites for Cubes" on page 162 have been met.

Write the Code

Use the SAS OLAP procedure to write a program that will store the cube definition in a project repository, then create the specified cube on the file system. Here is an example program:

   proc olap cube=Star             path=c:\cubes             fact=olapsio.ordfact   metasvr host=localhost            port=9999t            protocol=bridge            userid=userid            pw=pw            repository=Project:etlUser1            olap_schema=OLAP Schema            ;   dimension Time hierarchies=(YWD YMD YQMD) type=time              dimtbl=olapsio.timedim dimkey=date_ID factkey=order_date              ;   hierarchy YWD caption="Year-Week-Day"              levels=(Year_ID Week_Name Date_ID );   hierarchy YMD caption="Year-Month-Day"              levels=(Year_ID Month_Name Date_ID);   hierarchy YQMD caption="Year-Quarter-Month-Day"              levels=(Year_ID Quarter Month_name Date_ID);   level year_ID       type=year;   level quarter       type=quarters;   level month_name    type=months;   level week_name     type=weeks;   level date_ID       type=days;   property WeekDay_Number_US  caption="US WeekDay Number" column=weekday_no   level=date;   property WeekDay_Number_EU  caption="EU WeekDay Number" column=weekday_eu   level=date;   property Week_Number_EU     caption="EU Week Number"    column=week_no   hierarchy=YWD   level=week_name;   property Month_Number       caption="Month Number"      column=month_no   hierarchy=YMD   level=month_name;   property Month_Number       caption="Month Number"      column=month_no   hierarchy=YQMD  level=month_name;   property Holidays_US        caption="US Holidays"       column=Holiday_us   level=date;   dimension Customers hierarchies=(PersonalData CompanyUsage)               dimtbl=olapsio.custdim dimkey=customer_id factkey=customer_id;   hierarchy PersonalData levels=(Customer_Name Customer_Age Customer_Gender);   hierarchy CompanyUsage               empty_char=_missing_               levels=(Customer_Group Customer_Type);   dimension Geography hierarchies=(Geography)              dimtbl=olapsio.geogdim dimkey=street_id factkey=street_id;   hierarchy Geography              empty_char=_missing_              levels=(Continent_Name Country State Region Province County City)              ;   dimension Organization hierarchies=(PersonalStats Organization)              dimtbl=olapsio.orgdim dimkey=employee_id factkey=employee_id;   hierarchy PersonalStats levels=(Employee_name Job_Title Salary Gender);   hierarchy Organization             empty_char=_missing_             levels=(Company Department Org_Group Section Job_Title);   MEASURE DiscountSUM STAT=SUM COLUMN=Discount;   MEASURE CostPrice_Per_UnitSUM STAT=SUM COLUMN=CostPrice_Per_Unit             FORMAT=DOLLAR10.2              ;   MEASURE QuantitySUM STAT=SUM COLUMN=Quantity             CAPTION='Sum of Quantity'              ;   MEASURE Total_Retail_PriceSUM STAT=SUM COLUMN=Total_Retail_Price             FORMAT=DOLLAR12.2             ;   AGGREGATION Continent_Name Country State Region Customer_Group Customer_Type                / NAME='RegionalCustomerUse'                 ;   AGGREGATION Year Quarter Customer_Group Customer_Type                 / NAME='QuarterlyCustomerUse'                 ;   AGGREGATION Year Customer_Group Customer_Type                 / NAME='YearlyCustomerUse'                 ;   AGGREGATION Continent_Name Country State Region Province Company Department                  Org_Group Section                 / NAME='WorldwideStaff'                 ;   AGGREGATION Continent_Name Country State Region Province Employee_Name                 Job_Title Salary                 / NAME='WorldwideSalaries'                 ;   run;

For details about the OLAP procedure, see the SAS OLAP Server Administrator's Guide.

Submit the Code

Perform these steps to submit your SAS code to the Source Editor window in SAS ETL Studio:

From the SAS ETL Studio desktop, select Tools Source Editor from the menu bar. The Source Editor window is displayed.
Paste the SAS code for the cube into the Source Editor window.
To submit the code, select Editor Submit from the menu bar. The code is submitted to the default SAS application server.
After the code has completed execution, use the Log tab on the Source Editor window to view any messages, statistics, warnings, or errors. If you find errors, edit and resubmit the code until the code runs successfully.

In the current example, when the code is successful, it will write the cube to the specified project repository. The next task is to run SAS ETL Studio and check in the cube to the change-managed repository (just as you would if you used the Cube Designer to create a new cube).

Check In the Cube

Under change management, new metadata objects are added to the Project tree on the SAS ETL Studio desktop. You must check in the new table metadata in order to save it to the change-managed repository. Perform these steps to check-in a cube:

Run SAS ETL Studio and open the metadata profile that specifies the project repository where the new cube was added.
In the Project tree, select the repository icon (such as Project: etlUser1).
From the menu bar on the SAS ETL Studio desktop, select Project Check In Repository.

All metadata objects in the project repository will be checked in to the change-managed repository. The new objects will be visible in the Inventory tree.

< Day Day Up >