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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 18, 2003

Introduction to MSSQL Server 2000 Analysis Services: Custom Cubes: Financial Reporting - Part II - Page 4

By William Pearson

We are now ready to test the query to ascertain that it produces the correct results.

1.      Click the Run button (illustrated below) to execute the new query.

Illustration 11: The Run Button atop the Design Query Toolbar

After a moment or so, the query results appear as partially depicted in Illustration 12 below.

Illustration 12: The UNION ALL Query Result Set (Partial)

We note that 173,674 records are returned. This is exactly double the number of rows in the sales_fact_1997 table (86,837), and acts as a quick confirmation that the population returned is complete.

2.      Select File -> Close from the top menu.

We are asked if we wish to save the query.

3.      Click Yes.

The Save As dialog appears.

4.      Type revenue_fact_Query into the dialog box, as shown below.

Illustration 13: Save the Query as revenue_fact_Query

Revenue_fact_Query appears among the queries that existed upon our arrival, as shown in Illustration 14. The icon to the left of the query identifies it as a UNION query.

Illustration 14: Revenue_fact_Query Appears

Before we leave MS Access, we will perform an insertion to the account table to provide for a need we will explain later in the lesson.

5.      Click the Tables icon in the Objects pane to display the FoodMart 2000 database tables.

6.      Double click the account table to open it in Table view.

7.      Maximize the window to be able to see the table completely.

8.      Adjust the width of the right-most column, labeled Custom Members.

We observe the following expression, containing a LookupCube function, in the Custom Members field for Account 3100:

LookUpCube("[Sales]","(Measures.[Store Sales],"+time.currentmember.UniqueName+","
	+ Store.currentmember.UniqueName+")")

(This expression exists as a part of the scenario set underpinning the samples that accompany MSSQL Server 2000 Analysis Services. Its purpose is to illustrate the use of an expression in this context for the entrainment of data from the Sales cube, clearly the most famous of the bundled samples (most Analysis Services writers never discuss any of the other cubes that are distributed with Analysis Services - which possibly accounts for the relative absence of practical business guidance out there ...).

9.      Saving the expression to a text or other file, if you wish, or, perhaps better, backing up the table to be able to access it later (make a copy of the table, and rename the copy account_bak, or some such, for an easy "restore" after the lesson is done), replace the above expression with the following expression:

[Account].[All Accounts].[5000   Net Income].[3000   Net Sales].[3100   Gross 
	"+ Store.currentmember.UniqueName+")")

10.  We will add the following additional expression to the row immediately below the one to which we have added the last expression (again, in the Custom Members field, this time for Account 3200):

[Account].[All Accounts].[5000   Net Income].[3000   Net Sales].[3200   Cost 
	of Goods Sold],"+calendar.time.currentmember.UniqueName+",
	"+ Store.currentmember.UniqueName+")")

Note: If the above expressions present a challenge to type correctly, because of the newness to some of us of MDX (syntax, spacing, etc.), we can simply cut and paste the expressions, removing the "9" character first. (The notation is an indicator that the line is broken due to margin constraints of the document, and does not represent a "natural" break in the syntax). Leaving the character in place while pasting will cause issues later on, when we attempt to work with the expressions above.

We will revisit the above expressions in the final section of the lesson, explaining their purposes and uses in helping us to achieve our objectives of creating an integrated Financial Reporting cube.

11.  Select File -> Exit to close and leave MS Access, saving modifications if prompted.

MS SQL Archives

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