Analysis Services

I am trying to work through the tutorial when you load SQL 2005. I am running the 64 bit version. I have deployed my cube and am trying to display data in the browser. I move over the sales amount into the data area I get the below message. What should I do to fix this issue?

The query could not be processed:

Memory error: While attempting to store a string, a string was found that was larger than the page size selected. The operation cannot be completed.

Sounds similar to the problem discussed in this thread:

http://groups.google.com/group/microsoft.public.sqlserver.olap/msg/d2ca4c8981db915a
>>

" noWrap width="1%" bgColor=#b71c0c>Mosha Pasumansky [MS] Oct 17, 2:24 pm show options

Newsgroups: microsoft.public.sqlserver.olap From: "Mosha Pasumansky [MS]" <mos...@.online.microsoft.com> - Find messages by this author Date: Mon, 17 Oct 2005 11:24:25 -0700 Local: Mon, Oct 17 2005 2:24 pm Subject: Re: AS 2005: ....a string was found that was larger than the page size Reply | Reply to Author | Forward | Print | View Thread | Show original | Report Abuse

Please read this thread - there is plenty of feedback from the product team.
It is a bug in CTP16 that is fixed in RTM. If you are TAP customer - you can
get CTP16 hotfix for it.

--
==============================­====================
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebL­og/mosha
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==============================­====================
>>

Analysis Server 2000 performance Benchmark on FoodMart 2000

I'd like to have a performance benchmark on analysis services. I deployed a solution based on analysis services and I'd like to understand if performances are in line with other solutions. I 'm afraid that some hardware configuration is not properly set. So I ask to you if you can tell me how much time takes a report run on FoodMart 2000 , cube Warehouse and Sales.

I built the following report, and the MDX code has been automatically generated by our Client Tool. Even if is not the best MDX coding, I just need to unserstand if there are some differences in response time.

Thanks in advance

WITH measures.[__Customers] as '"[__Customers]"',visible=0 Member measures.[__Customers_selection] as'"{ AddCalculatedMembers({[Customers].[Country].AllMembers}) }"' ,visible=0Set [__Customers_main] as '{ Hierarchize(Distinct(StrToSet(CStr(measures.[__Customers_selection])))) }'SET [__Customers_RootMembers_Fast] As'{[__Customers_main]}' SET [__Customers] As'[__Customers_RootMembers_Fast]' measures.[__Product] as '"[__Product]"',visible=0 Member measures.[__Product_selection] as'"{ AddCalculatedMembers({[Product].[Product Subcategory].AllMembers}) }"' ,visible=0Set [__Product_main] as '{ Hierarchize(Distinct(StrToSet(CStr(measures.[__Product_selection])))) }'SET [__Product_RootMembers_Fast] As'{[__Product_main]}' SET [__Product] As'[__Product_RootMembers_Fast]' measures.[__Store] as '"[__Store]"',visible=0 Member measures.[__Store_selection] as'"{ AddCalculatedMembers({[Store].[Store State].AllMembers}) }"' ,visible=0Set [__Store_main] as '{ Hierarchize(Distinct(StrToSet(CStr(measures.[__Store_selection])))) }'SET [__Store_RootMembers_Fast] As'{[__Store_main]}' SET [__Store] As'[__Store_RootMembers_Fast]' SET [_Rows] as'Crossjoin({Crossjoin({[__Customers]}, {[__Product]})}, {[__Store]})' measures.[__Time] as '"[__Time]"',visible=0 SET [__Time_RootMembers_Fast] As'[Time].Levels(0).AllMembers' SET [__Time] As'[__Time_RootMembers_Fast]' measures.[__Measures] as '"[__Measures]"',visible=0 Member measures.[__Measures_selection] as'"{ [Measures].[Warehouse Cost], [Measures].[Warehouse Sales], [Measures].[Units Shipped], [Measures].[Units Ordered], [Measures].[Warehouse Profit], [Measures].[Unit Sales] }"' ,visible=0Set [__Measures_main] as'{ Hierarchize(Distinct(StrToSet(CStr(measures.[__Measures_selection])))) }'Set [__Measures_RootMembers_Smart] as '{ Filter([__Measures_main], Count(Intersect(Ascendants([Measures].CurrentMember),[__Measures_main])) = 1) }' SET [__Measures] As'[__Measures_RootMembers_Smart]' SET [_Columns] as'Crossjoin({[__Time]}, {[__Measures]})' SELECT [_Columns]DIMENSION PROPERTIES Parent_Unique_Name on columns, [_Rows]DIMENSION PROPERTIES Parent_Unique_Name on rows FROM[Warehouse and Sales] CELL PROPERTIES back_color,fore_color,format_string,font_name,font_size,font_flags,cell_evaluation_list,value

Well, I haven't the time to tell you why, but the MDX above will not run in either of my MDX tools|||

I think it is due to code formatting when I attached the code. I tried to copy and paste the code and it is true it doesn't work.

If you can try again......

Thanks in advance

Code Snippet

WITH
Member measures.[__Customers] as '"[__Customers]"',visible=0
Member measures.[__Customers_selection] as '"{ AddCalculatedMembers({[Customers].[Country].AllMembers}) }"' ,visible=0 Set [__Customers_main] as
'{ Hierarchize(Distinct(StrToSet(CStr(measures.[__Customers_selection])))) }' SET [__Customers_RootMembers_Fast] As '{[__Customers_main]}'
SET [__Customers] As '[__Customers_RootMembers_Fast]'
Member measures.[__Product] as '"[__Product]"',visible=0
Member measures.[__Product_selection] as '"{ AddCalculatedMembers({[Product].[Product Subcategory].AllMembers}) }"' ,visible=0 Set [__Product_main] as
'{ Hierarchize(Distinct(StrToSet(CStr(measures.[__Product_selection])))) }' SET [__Product_RootMembers_Fast] As '{[__Product_main]}'
SET [__Product] As '[__Product_RootMembers_Fast]'
Member measures.[__Store] as '"[__Store]"',visible=0
Member measures.[__Store_selection] as '"{ AddCalculatedMembers({[Store].[Store State].AllMembers}) }"' ,visible=0 Set [__Store_main] as
'{ Hierarchize(Distinct(StrToSet(CStr(measures.[__Store_selection])))) }' SET [__Store_RootMembers_Fast] As '{[__Store_main]}'
SET [__Store] As '[__Store_RootMembers_Fast]'
SET [_Rows] as'Crossjoin({Crossjoin({[__Customers]}, {[__Product]})}, {[__Store]})'
Member measures.[__Time] as '"[__Time]"',visible=0
SET [__Time_RootMembers_Fast] As '[Time].Levels(0).AllMembers'
SET [__Time] As '[__Time_RootMembers_Fast]'
Member measures.[__Measures] as '"[__Measures]"',visible=0
Member measures.[__Measures_selection] as '"{ [Measures].[Warehouse Cost], [Measures].[Warehouse Sales], [Measures].[Units Shipped], [Measures].[Units Ordered], [Measures].[Warehouse Profit], [Measures].[Unit Sales] }"' ,visible=0 Set
[__Measures_main] as '{ Hierarchize(Distinct(StrToSet(CStr(measures.[__Measures_selection])))) }' Set [__Measures_RootMembers_Smart] as
'{ Filter([__Measures_main], Count(Intersect(Ascendants([Measures].CurrentMember),[__Measures_main])) = 1) }'
SET [__Measures] As '[__Measures_RootMembers_Smart]'
SET [_Columns] as'Crossjoin({[__Time]}, {[__Measures]})'
SELECT [_Columns] DIMENSION PROPERTIES Parent_Unique_Name on columns, [_Rows] DIMENSION PROPERTIES Parent_Unique_Name on rows
FROM [Warehouse and Sales]
CELL PROPERTIES back_color,fore_color,format_string,font_name,font_size,font_flags,cell_evaluation_list,value

|||

Seems to take 2-4 seconds

2 on the server

4 remoting from my machine using Proclarity

|||

Against AS2000 it takes more or less 10 seconds using MSX sample application on a client machine.

If I run it on the server machine it seems that nothing change (1 second difference)

It is more than the double.

Now I have to understand why?

We have installed the default configuration of AS2000 on the server.

|||

On AS2005 it takes:

Cold cache: 4 seconds in SQL Server Management Studio, but in the server it takes only 0.812 seconds. Warm cache: 4 seconds in SQL Server Management Studio, but in the server it takes only 0.625 seconds.|||

Thanks for your intervention.

I have a question, sorry but I'm not so smart in performance tuning. This is not my daily job.

How Do you trace the time that the query takes on the server when you run it from the client application? Do you have a tool or Do you use the standard performance counter. If so which is the counter that you trace?

|||

Well, in AS2005 is very easy! Just turn on the Profiler. However, it does not work in AS2000.

In AS2000, we have a add-in to Excel that lets you time the duration of the Pivot Table refresh.

There was a mdx_applet.xls file that allow you to run queries in MDX, from Excel. There you should implement the timing feature.

analysis server

I am getting following deployment fail error when I try to deploy the
cube on the server:
"Error 1 The project could not be deployed to the
'myserver_2005'
server because of the following connectivity problems : A connection
cannot be made to redirector. Ensure that 'SQL Browser' service is
running. To verify or update the name of the target server, right-
click on the project in Solution Explorer, select Project Properties,
click on the Deployment tab, and then enter the name of the
server. 0
0 "
How do I start the analysis server so that I can deploy the cube? I
have already manually started the analysis server and browser is
running. How do I connect to the analysis server using SSMS? I am
getting an error when I use SSMS to connect to analysis server 'a
connection can not be made to the redirector. Make sure that 'sql
browser service' is running'. I made sure. Any ideas?On Sep 18, 10:54 am, SB <othell...@.yahoo.com> wrote:
> I am getting following deployment fail error when I try to deploy the
> cube on the server:
> "Error 1 The project could not be deployed to the
> 'myserver_2005'
> server because of the following connectivity problems : A connection
> cannot be made to redirector. Ensure that 'SQL Browser' service is
> running. To verify or update the name of the target server, right-
> click on the project in Solution Explorer, select Project Properties,
> click on the Deployment tab, and then enter the name of the
> server. 0
> 0 "
> How do I start the analysis server so that I can deploy the cube? I
> have already manually started the analysis server and browser is
> running. How do I connect to the analysis server using SSMS? I am
> getting an error when I use SSMS to connect to analysis server 'a
> connection can not be made to the redirector. Make sure that 'sql
> browser service' is running'. I made sure. Any ideas?
Okay this may be a bug. Can anyone please point me to a link how do I
fix this? I have already installed sp2.|||Why are you starting a new thread with a question you've already asked?
Look at my response to your original post.
ML
--
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/

Amending a stored procedure

Hi

I have a stored procedure that I have deployed with a database to an sqlserver. To access the database and the stored procedure I am now using SQL Server Management Studio Express. When I was amending the stored procedure locally, I simply "opened" it, made the amendments and then "saved" it. With S.M.S.E. if you click on "modify" a SQLQuery opens up and then after making the amendments you can save the SQLQuery. How do you go from here to effect that change to the stored procedure resident on the sqlserver.

Chris

I've worked out that all I have to do is run the query after amending the stored procedure detail.

Chris

|||

When you click the Modify button, it should show you the stored procedure and it will have an ALTER PROCEDURE command at the top. This just tells the database that you want to overwrite the existing stored procedure with whatever text follows. So, if you click the Execute button (it should have an exclamation mark next to it) the command should execute against the database and your stored procedure will be updated.

|||

Mark

Thanks for your reply

Chris

Always Fit-To-Page

All of the reports we have deployed (over 100) are wider than a standard letter sized page. In order to print them correctly, the Fit-To-Page option must be selected in the print dialog.

We desperately need a way to make this automatic. I know our users have killed a forest with the paper printed when they forget to select the option. If anyone knows of a way to make the setting of print options like Fit-To-Page automatic or programmatic please respond!

Thanks!

Mark

Go to Control Panel and go to properties of the printer and click Printing Preferences. For Paper Output, select Letter and click OK. Just try and let me know.

Shyam

|||

That might work but would not be a solution because it would effect every document printed off that workstation, not just the reports. We need a way to change the print properties for just the reports.

Thanks!

Mark.