S
Shanmugavel
OLAP Report viewing using EXCEL XP
Background:
The Product that we are offering is a BI product build on
SQL Server 2000 and SQL Analysis Server 2000 running on
Windows 2000 Advanced Server. SP3 has been applied to SQL
Server and Analysis services in the environment used for
the testing.
The client machine is a Windows 2000 Professional Machine
which is installed with Office XP. The machine has 256MB
RAM and 10 GB of Hard disk. The free space of Hard disk in
the C Drive is about 500 MB and D Drive is about 700MB.
The data warehouse on which the cube is created is built
on the Siebel application as the OLTP data source. The
EXCEL of Office XP is being used to launch an OLAP Report.
The Underlying OLAP cube is built using MOLAP.
The Report is launched by clicking a link on a HTML page.
The excel Report is stored on the Application Server. At
the time of launch, the Client side Excel application is
used to view the report.
Problem Description:
An OLAP report has been designed containing about 23
dimensions.
There are some dimensions where the number of members of
those dimensions is large. Example, contacts - about 15
thousand, Organizations about 5 thousand, Activities -
About 70 thousand.
Some of these dimensions have been modeled as a hierarchy.
For example Contacts has the hierarchy created on the
alphabetical grouping of names like ABI to AZIM, BALA to
BUDDHA, etc.
Activities have grouped by Activity Type - For example -
Meetings, Email Inbound, Email Out bound, etc.
When this report is launched, the default dimensions on
the Report body are
Subject Interest (Dimension) and No. of contacts (Measure)
The rest of the 22 Dimensions are in the header.
The Following sequence of steps are carried out when the
user is using he report
1. Filter one area of subject. The Contact Dimension
from the header is dropped into the report body.
2. The Organization Dimension is then dropped into
the report body beside the contact dimension.
The above combination takes about 18 Minutes to show up on
the report.
3. Finally the Organization agency staff dimension is
dropped beside the Organization dimension.
At this point, the report fails to launch or gives the
error even after the report has been running for more than
1.5 hours. The footer of the report shows a message that
it is executing the OLAP query.
Details of attachment for simulation:
To aid in your simulation, we are enclosing the different
Cube (samplecube.zip) file of a sample having the full
volume of records on dimensions and a sample number of
Facts data.
The dimensions in the cube are
1. Activity
2. Agency Staff
3. Contact
4. Organization
Measures:
1. Contact Sid
The sequence for you to simulate the problem is as follows:
SQL Analysis manager steps (on server):
0) Copy the enclosed files to a local folder. Extract the
zip file to this local folder.
1) Launch SQL Analysis manager.
2) Choose the Analysis server.
3) Choose restore database option in the right click menu.
4) In Open Archive file dialog, choose the cab file from
the local folder.
5) In Restore database dialog, click Restore.
6) In Restore database progress dialog, click Close once
you get the "Database successfully restored" dialog. Once
this is done, the restored database (BIP_PILOT_TESTING)
appears below the chosen server in SQL Analysis manager.
XL steps (On Win 2K Professional client):
0) Copy the enclosed files to a local folder. Extract the
zip file to this local folder.
1) Launch XL.
2) Open the XLS file that was copied to the local folder.
3) Right click on the report. Choose Refresh data. The
Multidimensional connection dialog appears.
4) Retain the Analysis server location. Give the name of
the server to which the cube was restored. Click Next.
5) Choose BIP_PILOT_TESTING database in Multidimensional
connection dialog.
6) Click Finish in Multidimensional connection dialog. The
report gets refreshed with data.
7) Choose one of the contact groups.
8) In the report, drag and drop Agency staff on report
body. XL takes some time (approx 10 to 15 mins). Finally
the data is displayed in the report.
9) In the report, drag and drop Activity on the report
body. At this point XL shows "Running OLAP query" for a
very long time. Results do not appear in XL. The CPU usage
by Excel is 98% during this point.
The following settings have been done on the Server side
in SQL analysis services using Analysis Manager
Background:
The Product that we are offering is a BI product build on
SQL Server 2000 and SQL Analysis Server 2000 running on
Windows 2000 Advanced Server. SP3 has been applied to SQL
Server and Analysis services in the environment used for
the testing.
The client machine is a Windows 2000 Professional Machine
which is installed with Office XP. The machine has 256MB
RAM and 10 GB of Hard disk. The free space of Hard disk in
the C Drive is about 500 MB and D Drive is about 700MB.
The data warehouse on which the cube is created is built
on the Siebel application as the OLTP data source. The
EXCEL of Office XP is being used to launch an OLAP Report.
The Underlying OLAP cube is built using MOLAP.
The Report is launched by clicking a link on a HTML page.
The excel Report is stored on the Application Server. At
the time of launch, the Client side Excel application is
used to view the report.
Problem Description:
An OLAP report has been designed containing about 23
dimensions.
There are some dimensions where the number of members of
those dimensions is large. Example, contacts - about 15
thousand, Organizations about 5 thousand, Activities -
About 70 thousand.
Some of these dimensions have been modeled as a hierarchy.
For example Contacts has the hierarchy created on the
alphabetical grouping of names like ABI to AZIM, BALA to
BUDDHA, etc.
Activities have grouped by Activity Type - For example -
Meetings, Email Inbound, Email Out bound, etc.
When this report is launched, the default dimensions on
the Report body are
Subject Interest (Dimension) and No. of contacts (Measure)
The rest of the 22 Dimensions are in the header.
The Following sequence of steps are carried out when the
user is using he report
1. Filter one area of subject. The Contact Dimension
from the header is dropped into the report body.
2. The Organization Dimension is then dropped into
the report body beside the contact dimension.
The above combination takes about 18 Minutes to show up on
the report.
3. Finally the Organization agency staff dimension is
dropped beside the Organization dimension.
At this point, the report fails to launch or gives the
error even after the report has been running for more than
1.5 hours. The footer of the report shows a message that
it is executing the OLAP query.
Details of attachment for simulation:
To aid in your simulation, we are enclosing the different
Cube (samplecube.zip) file of a sample having the full
volume of records on dimensions and a sample number of
Facts data.
The dimensions in the cube are
1. Activity
2. Agency Staff
3. Contact
4. Organization
Measures:
1. Contact Sid
The sequence for you to simulate the problem is as follows:
SQL Analysis manager steps (on server):
0) Copy the enclosed files to a local folder. Extract the
zip file to this local folder.
1) Launch SQL Analysis manager.
2) Choose the Analysis server.
3) Choose restore database option in the right click menu.
4) In Open Archive file dialog, choose the cab file from
the local folder.
5) In Restore database dialog, click Restore.
6) In Restore database progress dialog, click Close once
you get the "Database successfully restored" dialog. Once
this is done, the restored database (BIP_PILOT_TESTING)
appears below the chosen server in SQL Analysis manager.
XL steps (On Win 2K Professional client):
0) Copy the enclosed files to a local folder. Extract the
zip file to this local folder.
1) Launch XL.
2) Open the XLS file that was copied to the local folder.
3) Right click on the report. Choose Refresh data. The
Multidimensional connection dialog appears.
4) Retain the Analysis server location. Give the name of
the server to which the cube was restored. Click Next.
5) Choose BIP_PILOT_TESTING database in Multidimensional
connection dialog.
6) Click Finish in Multidimensional connection dialog. The
report gets refreshed with data.
7) Choose one of the contact groups.
8) In the report, drag and drop Agency staff on report
body. XL takes some time (approx 10 to 15 mins). Finally
the data is displayed in the report.
9) In the report, drag and drop Activity on the report
body. At this point XL shows "Running OLAP query" for a
very long time. Results do not appear in XL. The CPU usage
by Excel is 98% during this point.
The following settings have been done on the Server side
in SQL analysis services using Analysis Manager