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 May 17, 2004

Introduction to MSSQL Server 2000 Analysis Services: Introduction to Local Cubes - Page 6

By William Pearson

Designing & Creating the Local Cube with the PivotTable Report

We now have a PivotTable report, with its connection to the Warehouse and Sales server cube, in place, from which we can create our local cube. The rest of the process is easy, although planning is critical to ascertain the needs of the information consumers for whose remote use we are designing and creating the local cube.

For more information on the general creation and use of a PivotTable report, see Reporting Options for Analysis Services Cubes: MS Excel 2002.

23.  Click-select the Customers dimension on the PivotTable Field List.

24.  In the lower section of the PivotTable Field List, select Row Area.

25.  Click the Add button to add Customers to the Row Area of the PivotTable report.

26.  Click-select the Time dimension on the PivotTable Field List.

27.  In the lower section of the PivotTable Field List, select Page Area.

28.  Click the Add button to add Customers to the Page Area of the PivotTable report.

29.  Click-select the Supply Time dimension on the PivotTable Field List.

30.  In the lower section of the PivotTable Field List, select Data Area.

31.  Click the Add button to add Customers to the Data Area of the PivotTable report.

The resulting, admittedly minimal, PivotTable report should resemble the one shown in Illustration 11.


Illustration 11: The Shell PivotTable Report

We will use the above as a basis for creating our local cube. We will see that this is enough to get the process underway in the next steps.

32.  Click the PivotTable report to select it.

33.  Click the downward pointing arrow on the right side of PivotTable, on the PivotTable toolbar.

34.  Select Offline OLAP ... from the dropdown menu, as depicted in Illustration 12.


Illustration 12: Select Offline OLAP ... from the PivotTable Menu

The Offline OLAP Settings dialog appears, as shown in Illustration 13.


Illustration 13: The Offline OLAP Settings Dialog

35.  Click the Create Offline Data File button on the Offline OLAP Settings dialog (circled in red in Illustration 13 above).

The Create Cube File - Step 1 of 4 dialog appears, as depicted in Illustration 14.


Illustration 14: The Create Cube File - Step 1 of 4 Dialog

36.  Click Next.

The Create Cube File - Step 2 of 4 dialog appears, as shown in Illustration 15.


Illustration 15: The Create Cube File - Step 2 of 4 Dialog

A comparison to the items on the PivotTable Toolbar will reveal that the Create Cube File Step 2 of 4 dialog contains all the dimensions that are available for selection in the cube residing on the server. The checked boxes indicate the dimensions that appear in the PivotTable report itself.

Note: This avenue of selection, within the Create Cube File - Step 2 of 4 dialog, means we can do all selection here, avoiding the need to create an elaborate PivotTable report that includes all the dimensions and measures we want to incorporate into our local cube. This is an opportunity to save time, and the reason that we put a minimal amount of design into the PivotTable report earlier. (There is a minimal requirement for data to enable the Offline OLAP Settings option on the dropdown menu, however.)



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