Extremely Slow Query Times

Is there something that I can do to improve the query times when using Excel to query my AS2005 cube? It's EXTREMELY slow, even if I'm the only one querying the cube.
The AS server should not be a bottle neck (Windows 2003 x64-bit, dual core AMD Opteron, 7 + gigs of memory, etc.). I fee like our cube is very small with little data s well.
In addition to the slow query times when using Excel, using the Browser in Visual Studio locally on the server results in queries taking longer to execute than I would expect.
What are some things that I can do to improve performance?

This is just a shot in the dark, but have you specified all the member property relationships between the attributes on your dimensions correctly? If you've created your dimensions using the wizard then the vast majority (possibly all) of your attributes will be directly linked to the key attribute only, and this can lead to less than optimal performance.

Here's an example of what I mean: say you have a Geography dimension with Continent, Country, State and City attributes and Address as the key attribute. You know there's a many-to-one relationship between Address and City, City and State, State and Country and Country and Continent, but by default the wizard will only create relationships on Address and City, Address and State, Address and Country and Address and Continent. What you need to do is go to the dimension editor in VS, then in the Attributes pane on the left hand side drag and drop attributes onto other attributes to create these relationships (AS knows about transitive relationships too, so you can delete ones like Address and Continent). Once you've done this then redesign your aggregations (usage-based optimisation might be a good idea too in the medium term) and reprocess, and you should see an improvement in performance.

Chris

|||Chris,

I know what you're referring to, but I don't really understand how to implement it correctly.

Take the following as my example. Let's say I have Product SKU and SKU Description as available attributes in my Products dimension. While editing the Products dimension, I notice that I can drag SKU Description to create a relationship under Product SKU. I can also do the opposite and drag Product SKU to create a relationship under SKU Description. However, I can't do both. Therefore, what is the difference between the 2 relationships. Ultimately, a Product SKU can have only one SKU Description and vice-versa.

Thanks!|||

In this case, yes, you have a 1:1 relationship between Product SKU and SKU Description, but I believe it's still beneficial to put the relationship in (probably by making Description a property of Product SKU, if the latter is the key attribute of your dimension). Relationships are useful for AS when it tries to design aggregations, use aggregations during querying, and for working out which attributes 'exist' with each other, all of which will improve query performance. Defining 1:1 relationships aren't going to have such a big impact though; do you have any 1:M relationships such as Product Category to Product SKU, Year to Month etc? Defining them in the dimension is likely to have a much more obvious effect.

Extremely slow Excel MDX

Using Excel as a client is most of the time exceedingly slow. For example writing a simple query of the type:

SELECT [Measures].[Some Measure] ON 0,
[Product].[Product-Version].[Product] ON 1
FROM [Cubename]

in Management studio is in Excel transformed to:

SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Product].[Product-Version].[All]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [Cubename] WHERE ([Measures].[Some Measure])

which takes several times longer to execute. As one starts drilling down it becomes increasingly worse with excel producing MDX that takes 100:s of times longer to execute then if I handwrite the mdx. This is with a very simple cube where Some Measure is not a calculated member. I can't even begin to imagine how slow it would be with a more complex cube. Is there anything to be done about this, any guidelines to follow to make it easer for Excel to generate "normal" mdx?

I had similar problem with Excel and OWC when accessing OLAP Cubes. Unfortunately, unless you optimize your cube, there is nothing can be done with how Excel generate mdx to retrieve data. Excel and OWC is closed code, and recently Microsoft announced that they will be stopping new releases for OWC. Howerver, Excel PivotTable has new version, take a look at Office 2007 in Beta version. It generates more efficient MDXs compared to Office 2003/2000.

Downside, it will take another 2-3 years for Office 2007 to be as popular as Office 2003, so, distribution of your solution in Office 2007 might be an issue if u decide to switch to Office 2007 in larger scale enterprise.

|||Thanks. Is there anything special you have in mind when you say "unless you optimize your cube"? Or do you mean the "ordinary" optimizations one does to make the server work decently fast? I will try to have a look at the 2007 beta though.|||

As mentioned earlier, you can take a look at Office 2007 sending bit different MDX queries. You also take a look at the ProClarity recently aquired by Microsoft see if you get better performance using it.

Also make sure you install latest service pack - SP1. There has been some performance improvements in it. You will see event more performance improvements in upcoming service pack 2. Watch for announcements of Community Technology Preview (CTP) to get your hands on upcoming SP2.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Extracting MS Excel Data in MSSQL Server 2005 Express

Is there a way of extracting data from MS Excel using MS SQL Server 2005 Express?

hi,

you can set up a linked server (http://msdn2.microsoft.com/en-us/library/ms190479.aspx) to the xls file and get relevant data into your SQLExpress table...
consider having a d:\Students.xls file with 2 columns, FirstName and LastName ...

SET NOCOUNT ON;

CREATE TABLE dbo.Students (

FirstName VARCHAR(10) ,

LastName VARCHAR(10)

);

GO

EXEC sp_addlinkedserver 'ExcelSource',

'Jet 4.0',

'Microsoft.Jet.OLEDB.4.0',

'd:\Students.xls',

NULL,

'Excel 5.0';

GO

EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL ;

GO

SELECT * FROM ExcelSource...[Sheet1$];

-- import into your base table...

INSERT INTO dbo.Students SELECT * FROM ExcelSource...[Sheet1$]; /* [Sheet1$] is for English version :D as it is localized in the Excel language localization */

SELECT * FROM dbo.Students;

GO

-- final clean up, dropping the linked server and the base table

EXEC sp_dropserver 'ExcelSource', 'droplogins';

GO

DROP TABLE dbo.Students;

regards

extracting data from sql server 2000 to draw bar charts in powerpoint/excel

Dear all,

I am doing a project where I heed to extract data from a table in a sql server and then automatically create a bar chart with this data preferrably in PowerPoint (or excel). I was thinking myself that it may be done in vba/macro added to a button in powerpoint. If anyone knows how to do this i would be very grateful or if anyone know a useful website could they post it up.

Thanks for your help

Laura

The Microsoft Office web site has a good overview of how to import data into Excel from SQL Server. That data could then be used to draw your chart. Check out http://office.microsoft.com/en-us/excel/HA010864661033.aspx for more information. Once you've set up the datasource connection, there is already a menu item/toolbar button to refresh the workbook's data from the server.|||There are several ways to export data into Excel

1. Use Jet OLEDB Provider. You need to create INSERT SQL statements and call them to insert one row of data at a time. Jet works pretty fast, but has some limitations when data in a same column contains different types.

2. Use Microsoft Office Tools for .NET. This way is slower, but gives you more functionality and allows formatting of the data. It allows you to control your output, but could be very slow and you need to pay attention to releasing all the resources associated with it, because it is COM-based. You have to pay attention to memory releasing, especially in ASP.NET environment

3. Write code that stores data into CSV or HTML. Those formats recognized by Excel

4. Use third party components that could provide desired functionality

Following are some links wit the samples

http://support.microsoft.com/kb/306022/en-us

http://support.microsoft.com/kb/326548/en-us

http://support.microsoft.com/kb/307029/en-us

extracting data from sql server 2000 to draw bar charts in powerpoint/excel

Dear all,

I am doing a project where I heed to extract data from a table in a sql server and then automatically create a bar chart with this data preferrably in PowerPoint (or excel). I was thinking myself that it may be done in vba/macro added to a button in powerpoint. If anyone knows how to do this i would be very grateful or if anyone know a useful website could they post it up.

Thanks for your help

Laura

The Microsoft Office web site has a good overview of how to import data into Excel from SQL Server. That data could then be used to draw your chart. Check out http://office.microsoft.com/en-us/excel/HA010864661033.aspx for more information. Once you've set up the datasource connection, there is already a menu item/toolbar button to refresh the workbook's data from the server.|||There are several ways to export data into Excel

1. Use Jet OLEDB Provider. You need to create INSERT SQL statements and call them to insert one row of data at a time. Jet works pretty fast, but has some limitations when data in a same column contains different types.

2. Use Microsoft Office Tools for .NET. This way is slower, but gives you more functionality and allows formatting of the data. It allows you to control your output, but could be very slow and you need to pay attention to releasing all the resources associated with it, because it is COM-based. You have to pay attention to memory releasing, especially in ASP.NET environment

3. Write code that stores data into CSV or HTML. Those formats recognized by Excel

4. Use third party components that could provide desired functionality

Following are some links wit the samples

http://support.microsoft.com/kb/306022/en-us

http://support.microsoft.com/kb/326548/en-us

http://support.microsoft.com/kb/307029/en-us