LastNonEmpty and AverageOfChildren

Hello,

we are using SQL 2005 with SP1 and we have quite small cube (<1GB).

We have a couple of measures with aggregate function AverageOfChildren or LastNonEmpty.

End users have very poor performances while browsing cube using these measures.

When we replace agg. function with SUM, for example, performances become normal.

I saw simillar discusion on OLAP forum, but I did not understand what is the best recommendation for this situation?

Is this known issue, and how it can be avoided?

Best regards

Borko

Borko,

What's your aggregation strategy for this measure group? Queries to semi-additive measures are resolved by initially going down to granularity for the measure group's time dimension and subsequently computing the aggregate. Because of this, it's important to insure that you have an aggregation that includes the granularity attribute. If not already there, you can generally force the attribute to be included by setting the AggregationUsage property to "Full" in the cube editor. However, since this setting applies across all measure groups, you may want to set it, design aggregations for this measure group, and then rest the value.

I'm not positive that this will solve your issue, but this is where I would start.

-rob

LastNonEmpty + Time PrevMember don't aggregate correctly

I'm using the Account Intelligence and when I have a balance account modified with a mdx script in cube calculation using the Time prevmember it don't aggregates correctly. I think that it's a solver order problem.

I have a account dimension like that:

Balance (balance account)

Asset (balance account)

Computers (balance account)

Result (flow account)

Computers Investments (flow account)

In the cube calculation I have these formula:

Computers = (Computers, Time.Currentmember.Prevmember) + Computer Investments;

When I query these data I receive these:

2007 Jan Fev Mar

Balance 0 100 50 0

Asset 0 100 50 0

Computers 150 100 150 150

Result 150 100 50 0

Computers Investments 150 100 50 0

It's not aggregatin the (Computers, Time.Currentmember.Prevmember) in account hierarchy, any Idea why is that?

I'm using SQL Server 2005 Enterprise SP1.

I was using the follow Time Hierarchy:

Year

Last period as initial value but still have period as parameter?

Hi all,

I'm using an olap cube in a report (dash board).
The last "actual" period should be viewed when the user opens the report. I'm using the MDX tail function for that.
Once the report is opened the users want to select another period. Then I want to put period as a parameter.
But that is not possible since the parameter check box is no longer available when using a MDX filter expression.

Is there any solution to this problem?

Any help will be appreciated.

If you know MDX, then don't use the query designer and just write your own queries. You'll need to have 2 maybe 3 datasets:

Main query dataset

Last Month calculation of selected Dates

I have a time dimension and a Last Month calculation.

In Cube Browsing, when I only select some dates in a month (e.g. 4th and 5th May 2007), at month level (May 2007), I found that the "Current Time" calculation gives me the sum of 4th May and 5th May. However, my LM calcuation gives me the total sum of LM value for the whole May 2007.

My LM calculation is as follows:

[Time].[Month Comparison].DefaultMember,
ParallelPeriod(
[Time].[Date with Month].[Month],
1,
[Time].[Date with Month].CurrentMember
)

Can anyone tell me how can I archive the calculation of LM at month level that only sum up the LM-value of the selected dates?

Thanks in advance.

Sorry, but I'm a little confused about what it is you are calculating. Could you provide more info, maybe a full MDX statement or query?

Thanks, Bryan

Last Month calculation of selected Dates

I have a time dimension and a Last Month calculation.

In Cube Browsing, when I only select some dates in a month (e.g. 4th and 5th May 2007), at month level (May 2007), I found that the "Current Time" calculation gives me the sum of 4th May and 5th May. However, my LM calcuation gives me the total sum of LM value for the whole May 2007.

My LM calculation is as follows:

[Time].[Month Comparison].DefaultMember,
ParallelPeriod(
[Time].[Date with Month].[Month],
1,
[Time].[Date with Month].CurrentMember
)

Can anyone tell me how can I archive the calculation of LM at month level that only sum up the LM-value of the selected dates?

Thanks in advance.

Sorry, but I'm a little confused about what it is you are calculating. Could you provide more info, maybe a full MDX statement or query?

Thanks, Bryan

Last Cube Process Date/Time

Is there anyway to programmatically retrieve the last date/time that an SSAS cube was processed?

I would like to display this date/time in reports.

I could add a step to the job that runs the process to add a row to a table upon successful completion, but don't want to re-invent the wheel.

Thanks!
BobPIt is available via both DSO (for AS2K) and AMO (for AS2K5).
Just look in BOL and you will see it.

_-_-_ Dave|||

hi,

can you post an example to show the date cube was last updated in a text box on a report.

Thanks,

|||

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.AnalysisServices;

namespace GetCubeProcessingTime

{

class Program

{

static void Main(string[] args)

{

string date = string.Empty;

string cubeName = string.Empty;

try

{

// Connect to the SSAS server

Server server = new Server();

server.Connect(@."Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Adventure Works DW;Data Source=localhost\YUKON");

// Get the Adventure Works cube(s)

Database database = server.Databases.GetByName("Adventure Works DW");

foreach (Cube cube in database.Cubes)

{

date = cube.LastProcessed.ToString("yyyy-MM-dd HH:mm:ss");

cubeName = cube.Name;

Console.WriteLine(string.Format("Cube [{0}] was processed: {1}", cubeName, date));

}

}

catch (Exception exception)

{

// Uups

Console.WriteLine(exception.Message);

}

}

}

}

Last Cube Process Date/Time

Is there anyway to programmatically retrieve the last date/time that an SSAS cube was processed?

I would like to display this date/time in reports.

I could add a step to the job that runs the process to add a row to a table upon successful completion, but don't want to re-invent the wheel.

Thanks!
BobPIt is available via both DSO (for AS2K) and AMO (for AS2K5).
Just look in BOL and you will see it.

_-_-_ Dave|||

hi,

can you post an example to show the date cube was last updated in a text box on a report.

Thanks,

|||

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.AnalysisServices;

namespace GetCubeProcessingTime

{

class Program

{

static void Main(string[] args)

{

string date = string.Empty;

string cubeName = string.Empty;

try

{

// Connect to the SSAS server

Server server = new Server();

server.Connect(@."Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Adventure Works DW;Data Source=localhost\YUKON");

// Get the Adventure Works cube(s)

Database database = server.Databases.GetByName("Adventure Works DW");

foreach (Cube cube in database.Cubes)

{

date = cube.LastProcessed.ToString("yyyy-MM-dd HH:mm:ss");

cubeName = cube.Name;

Console.WriteLine(string.Format("Cube [{0}] was processed: {1}", cubeName, date));

}

}

catch (Exception exception)

{

// Uups

Console.WriteLine(exception.Message);

}

}

}

}

Last child of all months

Hey everybody..
I need a little help getting the correct info out of my cube. I have
a cube that stores the balance for every day over the year.
What I need is to get the balance for every last day of every month
Ill describe my cube a bit. I have a dimention that contains the
various systems used (AH.SP,IG... ), a dimention Time (year, month,
day) and
the Measures. (balance, count)
What I need is to get the balance for jan.31, feb.29, mar 31.. and so
forth. For every system. So it would look something like
jan feb mar apr mai
AH 54 4 43 43 43
SP 43 3 45 56 56
IG 3 5 6 6 6
Thanks for any and all help
Arnar
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/OLAP-child-...pict207887.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz
.com/eform.php?p=709627http://groups-beta.google.com/group...erver.olap/msg/
50d474b9b11d21ff[vbcol=seagreen]
Newsgroups: microsoft.public.sqlserver.olap
From: Deepak Puri <deepak_p...@.progressive.com> Date: Wed, 16 Mar 2005
14:51:42 -0800
Subject: Re: multiple entries with dates
There are well-known techniques for dealing with semi-additive
"snapshot" measures, which are discussed in this MSDN paper. Please note
that Analysis Services 2005 now has built-in balances:
http://msdn.microsoft.com/libr_ary/...ry/e_n-us/dnsq.
.
Analysis Services: Semiadditive Measures and Inventory Snapshots
Amir Netz
Microsoft Corporation
Updated May 18, 2004
Applies to:
Microsoft SQL Server 2000
Microsoft SQL Server 2000 Analysis Services
Summary: Focusing on a classic inventory problem, this article describes
the implementation techniques of semiadditive measures in online
analytical processing.
.
http://www.microsoft.com/techn_et/p...alua_te/dwsqlsy
.m
spx
.
One of the greatest arguments for using an analytical server such as
Analysis Services is the ability to define complex calculations
centrally. Analysis Services has always delivered rich analytics, but
some complex concepts have been difficult to implement.
One such concept is that of a semi-additive measure. Most common
measures, such as [Sales], aggregate cleanly along all dimensions:
[Total Sales] for all time is the sales for all products, for all
customers, and for all time. A semi-additive measure, by contrast, may
be additive in some dimensions but not in others. The most common
scenario is a balance, such as the number of items in a warehouse. The
aggregate balance for yesterday plus today is not, of course, the sum of
yesterday's balance plus today's balance. Instead it's probably the
ending balance, although in some scenarios it is the beginning balance.
In Analysis Services 2000 you would have to define a complex MDX
calculation to deliver the correct measure. With Analysis Services 2005,
beginning and end balances are native aggregation types.
.[vbcol=seagreen]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

large value data type (nvarchar(max))

hi,

I have inserted a new column in my db table (type: nvarchar(max))
now I can't process the cube anymore ... is there a possibility to store large text files in the cube?

thanks,

rhapsy

Hi Rhapsy

What are you trying to achieve with this data? Would it not be easier to try and have an action to get that text if it is needed?

Regards

Mike

|||hey mike,

probably it would be easier, but I'd like to get all my information from the same source ...
and I'm a little bit curious if thias is possible ...

thanks,
rhapsy