Defining Analysis Services Named Calculation

Hi,

I am creating a new Named Caluclation for time dimension in dsv and added in the time dimension cube from the data source view in the time dimension structre.

While processing it is giving the error message that

"Memory error: The operation cannot be completed because the memory quota estimate exceeds the available system memory"

Can you please help me.

Thanks

Dinesh

What does the named calculation look like? And does it process if you take it out?

Cheers

Matt

|||

Show us the named calculation statment...

Works ok without the Named Calculation?!

Regards

|||

Hi

I have used the named calculation "ReportDate + ' ' + Session" in time dimension.

can you let me know how to add this namedcalculation to the cube after creating the named calcualtion in dsv.

Thanks

Dinesh

|||

Hi,

ReportDate and Session are both strings? If not you probably get problems, you will need to cast them as a string/varchar in order for your named calculation to work.

Adding to a dimension, just edit your dimension and drag it onto the attributes list. If it is a new measure, edit your cube and right click, add new measure, select the new named calculation.

Hope that helps

Matt

|||

You already created the NC in the DSV? Correct?

The cube processing generate errors only after your created the NC, correct?

Regards

|||

Hi,

ReportDate and Session are both strings. I added that into atribute list and processed.

It is giving the error message like

"Memory error: The operation cannot be completed because the memory quota estimate (1976MB) exceeds the available system memory (659MB). "

So, what to do for this type of error

Thanks

Dinesh

|||

Hi,

Looks like this is a known bug with SSAS, I haven't seen it before, but it looks like there is a hotfix for it.

http://support.microsoft.com/kb/914595

Hope that helps

Matt

|||

Hi,

Yes after creating the NC only this problem came. before i tested the cube there is no problem.

can you please let me know what is the problem.

Thanks

Dinesh

|||

Check the Matt link and fix the bug, if you will still need some help, tell us!

regards!

Define Pre-Calculated AVG measures?

It is possible in SQL Server Analysis to define Pre Calculated AVG measure?

Because If you are defining AVG measure for Calculated member then your report performance will down, so I need Pre Calculated AVG measure.

let me know anybody know any solution?

Dinesh Patel

You may want to read this:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=548837&SiteID=1

Also, I think you can get SSAS 2005 to cache results when using MDX scripts to define calculations.

The following example for the use of CACHE function is listed on MSDN:

Scope (Customers.Geography.USA);
Sales = 1.1 * (Sales, Time.Year.PrevMember);
Cache (Sales, Time.Year);
End Scope;

|||Even though the CACHE statement is available and can be used, it is not supported. As such, there is not guarantee that it will work as expected.

Define MySQL data source in a SQL Server Analysis Service Project

I want to use MySQL database as a data source fo an Analysis Service Project.

But there is no Provider that support MySQL Databases.

If anybody have any experiance about this, Please help me.

Thanks

Hi,

I know there are some providers out there, but I don't have any experience using them, but I do know they exist. Here is one that I found doing a quick search.

http://sourceforge.net/projects/myoledb/

David

|||

Loading data from MySQL database is not officially supported by Analysis Services.

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

|||

Just because its not supported, doesn't mean it can't be done.

They only problem you may face in trying this provider is that it may not support all of the properties that Analysis Services requires to be implemented. But its worth a shot.

David

define default for date report parameter / analysis services

I have a report which will one day display some data from an analysis services cube. my first step is to create a drop down parameter enabling the user to choose the date. I'd like to display only dates that have data, and I'd like it to default to today.

So I've created a dataset that will be the datasource for the dropdown displaying the available non-empty dates, which works fine.

SELECT measures.turnover ON COLUMNS,

nonempty([TBL DIM DATE].[DATE_ONLY].[DATE_ONLY].ALLMEMBERS ) ON ROWS

FROM [Itdev1 Hk]

I've also set the report parameter up to be a queried paramter,and to use the above dataset as it source, with [DATE_ONLY] displayed. and [DATE_ONLY] as the value.

Now, how do I get it to default to the last valid member in the list?

I presume you are trying to have the latest date selected by default? If so, return your dataset in descending order (do an ORDER(<set>, DESC) on the rows), so that your latest date is at the top of the list.

|||this is helpful since now when I click the drop down the most likely values for me to use are at the top. but it has not caused any value to be selected by default.
|||You can use the same dataset in the default value query, which seems to collapse to the first row returned. I have no idea what the implications of doing this are, I happened on it by accident.|||

this is a helpful tip!

|||

i;m attempting to order by date in descending order. but it seems to sort in a random order when I do this ....

SELECT NON EMPTY { [Measures].[TURNOVER - WM INTERDAY] } ON COLUMNS, NON EMPTY { order([Time].[Date].[Date].ALLMEMBERS, [Time].[Date], desc ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Itdev1 Hk] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

and then in alphabetical order when I sort like this

SELECT NON EMPTY { [Measures].[TURNOVER - WM INTERDAY] } ON COLUMNS, NON EMPTY { order([Time].[Date].[Date].ALLMEMBERS, [Time].[Date].MemberValue, desc ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Itdev1 Hk] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

how do I get it to sort by real date?

DefaultMember of Time Dimension (Analysis Services 2005)

I have a very small time dimension. This time dimension has only Year, Quarter, and Month (plus DateID which is just an identity column). I have one hierarchy for Y-Q-M. The DateID attribute is hidden.

I have the default member of the Date ID attribute set to:

Tail(NonEmptyCrossjoin([Date].[Date ID].Members, 1), 1).Item(0).Item(0)

This works if I do not choose any dates, meaning if my last date is December of 2006, I see the values for December 2006 by default. However, if I add the Month attribute into the browser as a row or a column, I only see October, November, and December (though every month has data)

If I remove the DefaultMember property, I can see all months data when I add the months to the columns or rows, but the data doesn't default to the month (without manually selecting a date) like I want it to. I tried removing the Y-Q-M hierarchy and just making a Y-M hierarchy, but still the same results.

I swear this was working once upon a time (maybe before the latest SP install?) ... I cannot recall exactly.

has anyone else experienced this? am I missing something?

Thanks,

Jason

Hi jsaido,

The behaviour of your solution is right, but unfortunatly is not right for you.

It's named autoexist.

Why you don't use Named sets?

For example:

CREATE SET CURRENTCUBE.[Current Date]

AS Tail(NonEmptyCrossJoin([Date].[Date ID].[Date ID].Members, 1), 1);

CREATE SET CURRENTCUBE.[Current Montht]

AS EXISTS([Date].[Month].[Month].members, [Current Date]);

CREATE SET CURRENTCUBE.[Current Quarter]

AS EXISTS([Date].[Quarter].[Quarter].members, [Current Date]);

CREATE SET CURRENTCUBE.[Current Year]

AS EXISTS([Date].[Year].[Year].members, [Current Date]);

|||I did try named sets - my delivery vehicle for this cube is Excel 2003 pivot tables which do not support named sets.

DefaultMember of Time Dimension (Analysis Services 2005)

I have a very small time dimension. This time dimension has only Year, Quarter, and Month (plus DateID which is just an identity column). I have one hierarchy for Y-Q-M. The DateID attribute is hidden.

I have the default member of the Date ID attribute set to:

Tail(NonEmptyCrossjoin([Date].[Date ID].Members, 1), 1).Item(0).Item(0)

This works if I do not choose any dates, meaning if my last date is December of 2006, I see the values for December 2006 by default. However, if I add the Month attribute into the browser as a row or a column, I only see October, November, and December (though every month has data)

If I remove the DefaultMember property, I can see all months data when I add the months to the columns or rows, but the data doesn't default to the month (without manually selecting a date) like I want it to. I tried removing the Y-Q-M hierarchy and just making a Y-M hierarchy, but still the same results.

I swear this was working once upon a time (maybe before the latest SP install?) ... I cannot recall exactly.

has anyone else experienced this? am I missing something?

Thanks,

Jason

Hi jsaido,

The behaviour of your solution is right, but unfortunatly is not right for you.

It's named autoexist.

Why you don't use Named sets?

For example:

CREATE SET CURRENTCUBE.[Current Date]

AS Tail(NonEmptyCrossJoin([Date].[Date ID].[Date ID].Members, 1), 1);

CREATE SET CURRENTCUBE.[Current Montht]

AS EXISTS([Date].[Month].[Month].members, [Current Date]);

CREATE SET CURRENTCUBE.[Current Quarter]

AS EXISTS([Date].[Quarter].[Quarter].members, [Current Date]);

CREATE SET CURRENTCUBE.[Current Year]

AS EXISTS([Date].[Year].[Year].members, [Current Date]);

|||I did try named sets - my delivery vehicle for this cube is Excel 2003 pivot tables which do not support named sets.

default view for OLAP cube when first accessed

Hello,

I am using MS Analysis server for OLAP cubes and viewing these cubes using Cognos. What I want to do is to have defualt view defined for the particular cube whenever it is first accessed. I have been able to set the default month (i.e. current month). But in the default view I want data shown across multiple dimensions. E.g. on first view my client should be able to see data against dimensions Date and Product nested on y-axis and dimensions Destination and Time nested on x-axis.

Is it possible to achieve this using MDX query?

Any ideas will be greatly appreciated.

Thanks,

Akber

Hello!

I think that this can only be achieved if you have a client tool that supports writing MDX queries/selects. You cannot do it directly in AS2000 or SSAS2005, you will need a client. Cubes are not repositories for reports.

Since you are using Cognos you will have to rely of this supplier's tools for creating and saving reports.

HTH

Thomas Ivarsson