Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jul 11, 2005

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

By William Pearson

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.

14.  Click the Queries button in the Objects pane of the Database View.

15.  Double-click Create query in Design view, as shown in Illustration 17.

Click for larger image

Illustration 17: Beginning the Creation of Views ...

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

16.  Select the inventory_fact_1997 table, as depicted in Illustration 18.


Illustration 18: Beginning the Creation of Views ...

17.  Click Add to include the table in the upper portion of the Design View.

18.  Click Close to close the Show Table dialog.

19.  Select View --> SQL View from the main menu, as shown in Illustration 19.


Illustration 19: Shift to SQL View ...

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

20.  Type (or cut and paste) the following query into the query pane, replacing the existing string:


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.


Illustration 20: Query to Generate View to Support Relative Time

21.  Click the Run (!) button in the toolbar to execute the query.

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


Illustration 21: Partial Representation of the New View

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.

22.  Select File --> Close.

23.  Click Yes when prompted to save the query.

24.  Type the following into the Save As dialog that next appears:

vW_inventory_fact_1997

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


Illustration 22: Saving the New View

25.  Click OK to save the view and close the dialog.

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.)

26.  Double-click Create query in Design view, once again.

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

27.  Select the inventory_fact_1998 table in the Show Table dialog that appears.

28.  Click Add to include the table in the upper portion of the Design View.

29.  Click Close to close the Show Table dialog.

30.  Select View --> SQL View from the main menu, as we did earlier.

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

31.  Replace the existing "template" query string that appears in the query pane, as before, this time with the following query:


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;

32.  Click the Run (!) button in the toolbar to execute the query.

The query runs, and returns the data, simultaneously shifting to Datasheet View, in a manner similar to that we saw in the creation of vW_inventory_fact_1997 earlier.

33.  Select File --> Close.

34.  Click Yes when prompted to save the query, once again.

35.  Type the following into the Save As dialog that next appears:

vW_inventory_fact_1998

36.  Click OK to save the view and close the dialog.

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.


Illustration 23: The Two New Views Appear (Compressed View)

37.  Select File --> Exit to close MS Access and the clone database, saving as necessary.

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date