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 Apr 26, 2004

MDX in Analysis Services: Introducing DISTINCT COUNT - Page 3

By William Pearson



12.  Click-select product_id in the Measures folder, if required.


13.  If necessary, click the downward arrow beneath the Cube Tree to open the Properties pane.


14.  Click the Basic tab.


15.  Modify the default Name of Product Id to the following:


Product Count

16.  Type the following into the empty Description box, just below the Name box:


Distinct Count - Products

17.  Click the box to the right of the Aggregate Function label, to enable the selector.


18.  Select Distinct Count in the Aggregate Function selector.


The Basic tab of the Properties pane appears as shown in Illustration 8.



Illustration 8: Product Count Measure - Properties Pane - Basic Tab

19.  Click the Data tab as if going to the Data View to perform a routine browse.

A warning briefly appears, indicating that sample data is being generated, and that the cube requires processing, as a result of our modifications. The sample data then appears, along with a static warning below it, to ensure that we are aware that the data is not what it might appear to be, and that the cube must be processed to make updated, actual data available, as partially depicted in Illustration 9.


Illustration 9: Data View (Partial and Compressed) - With "Staleness" Warning at its Foot

Let's process the cube to activate our changes.

20.  Select File --> Save to save the cube in its modified state.

21.  Select Tools --> Process Cube to initialize the processing steps.

A message box appears, stating that the cube has no aggregations, and asking if we wish to design them at this time, as shown in Illustration 10.


Illustration 10: Aggregations Message Box - Just Say "No"

NOTE: The message box may not appear, if the cube has been altered with regard to aggregations since its installation as an MSAS sample. If not, the next box will appear instead, skipping this one.

22.  Click No to skip designing aggregations at present.

The Select the Processing Method dialog appears, as depicted in Illustration 11.


Illustration 11: The Select the Processing Method Dialog

Full Processing is the default, and only option, as the Warehouse cube has not been processed since the structural change we have made to it.

23.  Leaving settings at default, click OK.

Processing begins, and runs rapidly, as evidenced by the Process viewer's presentation of processing log events in real time. The Processing cycle ends and the success of the evolution is indicated by the appearance of the Processing Completed Successfully message (in green letters) at the bottom of the viewer, as shown in Illustration 12.


Illustration 12: Indication of Successful Processing

24.  Click Close.

We are returned to the Cube Editor. We can now browse the data and see our new Distinct Count measure in action.

25.  Click the Data tab, if necessary.

The Data View refreshes and data appear in the default formation, ready for our manipulations review. A portion of the Data View, depicting the Warehouse Profit and new Product Count measures, appears in Illustration 13.


Illustration 13: Warehouse Profit and Product Count Measures in the Data View

Now that we have a credible result set with which to compare, let's take a look at replicating the same results using MDX. We can leave the Data View as it is, for easy referral against our next results dataset, which we will generate independently within the MDX Sample Application.



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