Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
OLAP Report viewing fails to Launch using EXCEL XP
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
OLAP Report viewing fails to Launch using EXCEL XP
A couple of things -
1. Can you view and get reasonable performance when noit using Excel ? e.g. in the Browse Data option in Analysis Manger 2. What 'Performance Gain' setting are you using for the build of the cube. I suspect that your cube is just too much for Excel. My suggestion is to try an alternative - maybe look at Reporting Services Beta which should be able to access cubes or use a trial version of another third party tool. Problems such as this can be identified by starting of with few dimensions and then adding more until the system does not work. Remember adding one dimension with 2 members will 'theoretically' double the complexity of the cube (although in practice Analysis Services is too clever to do this). Adding hierarchies will cause data explosion and make matters even worse. -----Original Message----- 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
OLAP Report viewing fails to Launch using EXCEL XP
Hi Shanmugavel,
Thank you for posting in MSDN managed newsgroup! From my understanding to your issue and based on my expeirence, when opening a connection through the PivotTable Service(PTS) you can request that PTS remote the execution of the query to the server by specifying "Execution Location=3; Default Isolation Mode=1" on the connect string. This way, PTS will attempt to execute the query on the server. However PTS will still need to perform certain validation checks. Not all queries can be remoted to the server even if requested. Please feel free to let me know if you have any further questions. Does this answer your question? Thank you for using Microsoft NewsGroup! Wei-Dong Xu Microsoft Product Support Services Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
OLAP Report viewing fails to Launch using EXCEL XP
Hi Shanmugavel,
Furthermore, I'd suggeset one kb article Q323048 regarding SQL analysis service will help you some on this issue. Please go to: 323048 INF: Using Data Definition Language with SQL Server 2000 Analysis http://support.microsoft.com/?id=323048 Thank you for using Microsoft NewsGroup! Wei-Dong Xu Microsoft Product Support Services Get Secure! - www.microsoft.com/security This posting is provided "AS IS" with no warranties, and confers no rights. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't launch Excel | Excel Discussion (Misc queries) | |||
Automate Excel report to place certain data into existing report? | Excel Worksheet Functions | |||
zero values in olap pivot table report | Excel Discussion (Misc queries) | |||
Summary Report (OLAP kind) | Charts and Charting in Excel | |||
excel fails to launch | Excel Discussion (Misc queries) |