Distinct Count Grand Total issue SSAS 2005 (bug ?)

Hi,
I recently noticed in Excel and OWC and BI studio browser - that reports that use distinct count measures return incorrect Grand totals with row/column axis filters/selections. When the selections are more than 1 level deep it does not take the filter into consideration and returns a larger Grand total - as if the filter never took place. I have Sql Server 2005 Developers edition with both SP1 and Post-Sp1 hotfix (build 9.0.2153)

I have successfully replicated this in AdventureWorks sample easily. Here are the steps:

In the Cube browser just pull in the Date.Calendar hierarchy to the row axis and the Order Count measure (from SalesOrders folder) . The total is 31,455 Now try to select/filter using the row axis (dropdown) on specific Semesters , Quarters or lower levels - the Grand total will never change ! (still 31,455) Only if the filter is on year itself will it change - nothing lower. If filter is in the Page axis or Filter pane in BI studio it will work ok - but not on row/col.

This will also happen with Product Category Heirarchy if selecting categories and specific subcategories on row/col. Basically whenever the selections are 2 or more levels deep the grand total will not change to reflect the selected deeper levels.

Is this a bug? Any help/ feedback would be appreciated.

Thank You,
HaroldW.

Sounds like a real problem. Please contact Customer Support and report this. You can also use http://connect.microsoft.com/SQLServer/feedback to log it.

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

|||

Thanks Edward. I am not sure how to contact Customer Support - but I did log it using the URL you provided. I have a feeling it is a Client side (excel/owc) issue

Thanks,

HaroldW.

|||

Just wanted to update status. I reported the bug to Microsoft - using "connect" website. They responded that the bug will be fixed in sp2 :)

-HaroldW

Distinct Count Grand Total issue SSAS 2005 (bug ?)

Hi,
I recently noticed in Excel and OWC and BI studio browser - that reports that use distinct count measures return incorrect Grand totals with row/column axis filters/selections. When the selections are more than 1 level deep it does not take the filter into consideration and returns a larger Grand total - as if the filter never took place. I have Sql Server 2005 Developers edition with both SP1 and Post-Sp1 hotfix (build 9.0.2153)

I have successfully replicated this in AdventureWorks sample easily. Here are the steps:

In the Cube browser just pull in the Date.Calendar hierarchy to the row axis and the Order Count measure (from SalesOrders folder) . The total is 31,455 Now try to select/filter using the row axis (dropdown) on specific Semesters , Quarters or lower levels - the Grand total will never change ! (still 31,455) Only if the filter is on year itself will it change - nothing lower. If filter is in the Page axis or Filter pane in BI studio it will work ok - but not on row/col.

This will also happen with Product Category Heirarchy if selecting categories and specific subcategories on row/col. Basically whenever the selections are 2 or more levels deep the grand total will not change to reflect the selected deeper levels.

Is this a bug? Any help/ feedback would be appreciated.

Thank You,
HaroldW.

Sounds like a real problem. Please contact Customer Support and report this. You can also use http://connect.microsoft.com/SQLServer/feedback to log it.

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

|||

Thanks Edward. I am not sure how to contact Customer Support - but I did log it using the URL you provided. I have a feeling it is a Client side (excel/owc) issue

Thanks,

HaroldW.

|||

Just wanted to update status. I reported the bug to Microsoft - using "connect" website. They responded that the bug will be fixed in sp2 :)

-HaroldW

Distant access to Excel file for import

Hi,

I'm developping a web application and I need to import a data from an excel file to sql server 2005.

It works very well locally (visual studio & sql server & the excel file on the same machine).

But it doesn't work when visual studio & excel file are in a machine and sql server is in another server.

I have always the same problem:

'C:\Documents and Settings\momo\Bureau\File.xls' n'est pas un chemin d'accès valide. Assurez-vous que le nom du chemin d'accès est correct et qu'une connexion est établie avec le serveur sur lequel réside le fichier.

Could any one help me please ?!

Thank you in advance.

It would help if you translated the French error message to English.

|||

Sorry to be late !

Here is the translation :

'C:\Documents and Settings\momo\Desktop\File.xls' isn't a correct access path. Be sure that the name of the access path is correct and a connexion is established with the server where is the file.

In fact, I'm using the FileUpload control as follows:

string filemap = Convert.ToString(fileupload.PostedFile.FileName);

string conString = @."Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=""Excel 8.0;HDR=YES;"";Data Source=" + filemap;

Could you tell me, please, what do I have to modify in order to be able to import my file from any client machine (of course, each client machine has a copy of the same file locally).

Thanks in advance.

|||

You need to think of a different architecture. You should create a web service that writes to the SQL Server. On each client PC you need a windows application that can read the excel spreadsheet and write to the web service.

|||

Please, could I have more details on how to do it. I'm a begginer in ASP NET and so I do not know what a web service means exactely.

Thanks.

|||

Have a look at http://quickstarts.asp.net/QuickStartv20/webservices/default.aspx and work through the tutorial

"The ASP.NET Web Services QuickStart is a series of client and server samples along with supporting commentary designed to quickly acquaint developers with featuresin ASP.NET Web Services. The QuickStart samples are designed to be short, easy-to-understand illustrations of these features. By the time you finish reviewing these samples, you will be familiar with the majorfeature areas in ASP.NET Web Services. "

Displaying member name instead of key in query

In the tools that I have used for getting generated MDX queries (Query Analyzer, Proclarity, Excel) the query always seems to show the key value instead of the member name.

Is it possible or is there a query tool that will generate queries with the member name instead?

See example:

[Organization].&[152551]

Should read

[Organization].[By Division]

thanks,

Andrew

Set "MemberNamesUnique" to True for a particular attribute. That should do it|||

Thanks! Didn't even have to reprocess!

cheers,

Andrew

Displaying data of a cube over web hangs up server

Hi!

Can someone give me a clue in such problem?

I have published my cube in WEB to be available for other users (using save as web page in Excel). There are no problems while everyone uses Excel or export the file from WEB to MS Office Excel. But if we use web interface and try to get data, Analysis Server hangs up. It does not happening with the same cubes if they are located on local computer, so I don't think the problem is in cube. And I realy need web interface to get data from cube.

Should I reconsider another way to publish cubes in web or check some other things?

I am currently building a class library that uses ADOMD and MDX to bring back data in a DataTable. We then send the user over to Microsoft Reporting Services (reports run off the same MDX) to display graphs and give them export options. I have not had it hang up on me though.|||

Hi,

Are you publishing the web page (saved excel page) in a web server?

If so, what credentials are being passed from IIS/web server to analysis server ?

check if that credentials have permissions in Analysis Server.

check if you have allowed interactivity while creating the excel web page?

Next use sql profiler, to find out if the request is coming thru to the analysis server.

if the request itself is not coming, it means there is some connectivity, permissions or credentials issue.

if the request is coming to analysis server, then you can debug from there on....

hope this helps

Regards

|||

Hi again!

I am assure that there is all right with connectivity and permissions. The strangest thing is that previously everything worked quite well. The problem is new with previously published cubes (which worked prpperly in web interface for a long time). The problem showed up just in past week after installing SP2 on server.

|||

Hi,

Can you open SQL profiler and find out whats happening?

we can get more details to debug this problem from there.

Regards

|||

EvaZ wrote:

The problem showed up just in past week after installing SP2 on server.

Have you installed the SP2 version of the OLEDB provider on the client machine (ie. the web server)?

|||

I should ask for this one our administrator, but I doubt it. In that case, where could I get the download?

Thank you!

|||It is part of the Feature Pack, you should find the OLE DB provider download about half way down this page: http://www.microsoft.com/downloads/details.aspx?familyid=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en

Displaying data of a cube over web hangs up server

Hi!

Can someone give me a clue in such problem?

I have published my cube in WEB to be available for other users (using save as web page in Excel). There are no problems while everyone uses Excel or export the file from WEB to MS Office Excel. But if we use web interface and try to get data, Analysis Server hangs up. It does not happening with the same cubes if they are located on local computer, so I don't think the problem is in cube. And I realy need web interface to get data from cube.

Should I reconsider another way to publish cubes in web or check some other things?

I am currently building a class library that uses ADOMD and MDX to bring back data in a DataTable. We then send the user over to Microsoft Reporting Services (reports run off the same MDX) to display graphs and give them export options. I have not had it hang up on me though.
|||

Hi,

Are you publishing the web page (saved excel page) in a web server?

If so, what credentials are being passed from IIS/web server to analysis server ?

check if that credentials have permissions in Analysis Server.

check if you have allowed interactivity while creating the excel web page?

Next use sql profiler, to find out if the request is coming thru to the analysis server.

if the request itself is not coming, it means there is some connectivity, permissions or credentials issue.

if the request is coming to analysis server, then you can debug from there on....

hope this helps

Regards

|||

Hi again!

I am assure that there is all right with connectivity and permissions. The strangest thing is that previously everything worked quite well. The problem is new with previously published cubes (which worked prpperly in web interface for a long time). The problem showed up just in past week after installing SP2 on server.

|||

Hi,

Can you open SQL profiler and find out whats happening?

we can get more details to debug this problem from there.

Regards

|||

EvaZ wrote:

The problem showed up just in past week after installing SP2 on server.

Have you installed the SP2 version of the OLEDB provider on the client machine (ie. the web server)?

|||

I should ask for this one our administrator, but I doubt it. In that case, where could I get the download?

Thank you!

|||It is part of the Feature Pack, you should find the OLE DB provider download about half way down this page: http://www.microsoft.com/downloads/details.aspx?familyid=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en

Display Report Filters in Excel Problem

Dear Anyone,

I have a report that displays the user's selected filters. I have placed this on the page footer. When I export this to excel, it wont display. Am I doing something wrong?

Thanks,
JosephHi Joseph,

I had this same problem before where I place the parameter selection display in the page footer. Apparently, if you place it in the page footer, it will be exported to excel.

What you can do is place it on the body along with the report table. This wil allow you to include your additional information in excel.

Thanks,
Joseph

Display measurement description in Excel 2007

In SSAS 2005, you can specify the description for a measurement in its properties. However, how can I show this description in Excel 2007? I've been searching back and forth from the web but couldn't find the answer. Also, not sure how I can specify description for a calculated member... My users would love to see the description for the measurement and calculated member fields in Excel 2007.

It will be great if anyone can help on this!

Mitch

This is a really good question. It simply depends on if the client had implemented it. Early versions of ProClarity Professional did support descriptions from OLAP Services Cubes(SQL Server 7) but this was removed in later versions.

Measures have no member properties either and I have not seen a client that supports these descriptions in SSAS2005.

Your best opportunity is to write this information in a portal like Sharepoint Server /Services.

HTH

Thomas Ivarsson

|||

I'm a bit surprised that Excel 2007 doesn't show you the Measure description property as a tooltip when browsing the list of measures. Major shortcoming in my opinion.

Report Builder on top of a cube shows the measure description as a tooltip.

In terms of editing descriptions on calculated measures, see:

http://www.codeplex.com/bidshelper/Wiki/View.aspx?title=Calculation%20Helpers&referringTitle=Home

|||

Tracing the query that Excel sends to SSAS, it does not even ask for the description property, so there is not even a macro or anything like that which you could use to expose this property.

You can get the descriptions from the MDSCHEMA_MEASURES schema rowset. An easy way to get at this is to use the Discover stored proc that I wrote as part of the Analysis Services Stored Procedure project www.codeplex.com/ASStoredProcedures this proc is not part of the current release, but there will be a new release coming out really soon. To use this function now you would need to download the latest source code from codeplex and compile it yourself.

This would give you the ability to write an MDX query like the following:

Code Snippet

CALL ASSP.Discover("MDSCHEMA_MEASURES")

And put the results into an SSRS Report that gave you a nice list of all the measures and their descriptions for a given cube.

|||

Hopefully, Excel 2007 will expose the Measure description property down the road.

Mitch

Display Dimensions in Folders

Hello,

I created a cube, and when I show the dimensions using the PivotTable control and Excel 2007, I would like to be able to show the dimensions groupped in Folder as we can do with attributes, and measures.

Does SQL Server 2005 analysis services support that ? or some suggest how could i do it?

I hope you can help me....

Thank you!

Sorry, but this can't be done. Dimensions are a top level object.

If you are using the Enterprise Edition and you have lots of dimensions, you could use the perspectives feature to create different perspectives with sub sets of the dimensions. The only problem with this is that perspectives appear to a lot of client applications like separate cubes and as such it is not always easy to swap between them with these applications.

|||

Hello Darren,

I'll read about Perspectives.

Thanks for the TIP

Display Dimensions in Folders

Hello,

I created a cube, and when I show the dimensions using the PivotTable control and Excel 2007, I would like to be able to show the dimensions groupped in Folder as we can do with attributes, and measures.

Does SQL Server 2005 analysis services support that ? or some suggest how could i do it?

I hope you can help me....

Thank you!

Sorry, but this can't be done. Dimensions are a top level object.

If you are using the Enterprise Edition and you have lots of dimensions, you could use the perspectives feature to create different perspectives with sub sets of the dimensions. The only problem with this is that perspectives appear to a lot of client applications like separate cubes and as such it is not always easy to swap between them with these applications.

|||

Hello Darren,

I'll read about Perspectives.

Thanks for the TIP