Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube, Part II - Page 5

We will next create views, simply to establish a common key within each of our fact tables, so as to leverage our new table, as we shall see. Because the Warehouse sample cube is drawing upon "facts for two years" (with the years, in effect, serving as the basis for partitioning of the cube, we will create a couple of views where we might only create one in a simpler cube. Rather than attempt to rework the cube to make its structure more basic in this regard, we will simply create two views. Keep this in mind in your own environment as you go forward in attempting to tailor the foregoing steps to a real world scenario, where this step will likely be unnecessary, as it is a mere expedient for the practice example, and has little to do with the conceptual scope of our session.

The Design View opens, together with the Show Table dialog.

Design View becomes SQL View, where we see a "template" query string automatically appear.

SELECT 
     1 AS relativetime_id,
     inventory_fact_1997.product_id,
     inventory_fact_1997.time_id, 
     inventory_fact_1997.warehouse_id, 
     inventory_fact_1997.store_id,
     inventory_fact_1997.units_ordered,
     inventory_fact_1997.units_shipped,
     inventory_fact_1997.warehouse_sales,
     inventory_fact_1997.warehouse_cost,
     inventory_fact_1997.supply_time,
     inventory_fact_1997.store_invoice

FROM 
    inventory_fact_1997;

The query pane appears, with our input, as depicted in Illustration 20.

The query runs, and returns the data, simultaneously shifting to Datasheet View. A partial representation of the results appears in Illustration 21.

We will see, as we move into the Analysis Services portion of our practice session, the importance of the creation of the relativetime_id key here.

vW_inventory_fact_1997

The Save As dialog appears, with our input, as depicted in Illustration 22.

We return to the Database View, where we see our new view in place. We now need to repeat several of the steps above to create an identical view based upon the inventory_fact_1998 table. As I mentioned earlier, this is only to streamline our practice session, going forward, rather than restructuring the Warehouse cube, which happens to be partitioned on operating years. This would not be required / appropriate in a real world scenario where the relative time structures would ideally be constructed from the beginning, particularly before cube storage was designed and so forth.

As we shall see, the view itself is key to the process. (For that matter, I generally use views underneath my Analysis Services structures, albeit in a major RDBMS, as this practice offers many benefits, not the least of which is flexibility in design changes with minimal impact on both the underlying data source and the cube structure. A change such as the above to an existing, already aligned view would allow a simple change to the view itself, with subsequent refreshment of the data source (with some possible realignment) in Analysis Services. In the absence of a view between the data source tables and the Analysis Services layer, we would be forced to manipulate the schema of the data source, before performing subsequent re-alignments in Analysis Services.)

The Design View opens, together with the Show Table dialog.

Design View becomes SQL View, where we see a "template" query string automatically appear.

SELECT 
     1 AS relativetime_id,
     inventory_fact_1998.product_id,
     inventory_fact_1998.time_id, 
     inventory_fact_1998.warehouse_id, 
     inventory_fact_1998.store_id,
     inventory_fact_1998.units_ordered,
     inventory_fact_1998.units_shipped,
     inventory_fact_1998.warehouse_sales,
     inventory_fact_1998.warehouse_cost,
     inventory_fact_1998.supply_time,
     inventory_fact_1998.store_invoice

FROM 
    inventory_fact_1998;

vW_inventory_fact_1998

We return to the Database View, where we see our new view in place, underneath the first view we created, as shown in Illustration 23.

This concludes the preparation steps at the source database level. We will next move into Analysis Services, where we will finalize preparation for our practice exercise by creating a copy of the FoodMart 2000 sample Analysis Services database, within which we can undertake the structural setup required to support the relative time capabilities requested by the client information consumers.