Help with cupe partitioning/processing

Hello guys

I have an issue with one of our cubes which i hoped someone could help me with... The problem is a historical cube which is currently growing quite drastically, and includes daily history from 2004 until today. The cube is just processed full every single day, and this means processing 80+ million rows every day, when actually most of the data is not changed at all. I decided I want to split my cube in data for 2004, 2005, 2006, 2007 and then the current month. Once the current month is over then i would merge it with the year to date partition and create a new partition for the new month.

My problem is how to implement the processing in a simple way? Right now the processing is done in SSIS by just processing full the db, so I guessed i have to make 2 tasks, one to process full all the dimensions first , and one to process full the cubes afterwards in which i process for this specific cube only the latest partition. But this means at the end of the month I would have to change the package manually, since i need to merge some partitions and then process full only the newest partition again...

Is there any simpler way of doing this? Is my approach correct in the partitioning way and processing full of only the newest partition, except at the end of the month when i would need to merge and process the ytd partition?

I think you are headed down the right path, but I'd suggest you read through the partitioning white paper from Project REAL. It provides some good examples and thoughts related to partitioning within SSAS (and within SQL Server) and the ETL implementation for the solution there. I think you could probably use some of the same ideas and design patterns to solve your problem...

The partitioning white paper can be found at the following URL:

http://www.microsoft.com/technet/prodtechnol/sql/2005/realpart.mspx

Good luck!

Dave Fackler

Help with cube measures

I'm relatively new to AS but have managed to get a data mart, dimensions and
cubes up and running in AS 2005 (SP1).

I'm having difficulty trying to do something seemingly easy with respect to
measures.

One of my dimensions is Accounts. I have about 7 additional dimensions
including Time.
I have 5 measures. One of these measures is heavily used most all of the
time.
I need to filter this measure by the Account dimension to include only
measures >= <defined_number>.
The other measures should reflect this change also, but I also need to
analyze other dimensions with or without the Accounts dimension in the
crosstab.
I need this to be put in the aggregations, not a temporary dynamic
calculation.

Aside from doing this at the database level, is their a way to do this in AS
either with a calculated member, named calculation or other?
I've been trying for a few days but just can't seem to get it.

Please, any help would be great.

-Troy

Let me see if I can go at this a different way.

If I try to create a calculated member as follows in the VS 2005 designer:

Name: Filtered Volumes

Parent Hierarchy: MEASURES

Expression: [Measures].[Export TEUS] >= 500.00

Format String: "#,#.0"

Visible: True

Non-Empty Behavior: Export TEUS

After processing the cube, the values for any and all cells is -1.0

Maybe if someone could explain the why to me, it could start the wheels turning more and perhaps help anyone viewing theis post.

Thanks,

-Troy

|||

What's happening with our calculated member is that the Expression "[Measures].[Export TEUS] >= 500.00" is evaulated and returns True which is being displayed as a numberic value and True is -1.0 while False is 0 in numeric terms. (Actually any non-zero value is consider True, but True generally converts to 1.0 or -1.0 depending on the system.)

I don't quite understand what you're trying to achieve, but what you might want to try doing is using something like if([Measures].[Export TEUS] >= 500.00, [Measures].[Export TEUS], Null). (If you are using Analysis Services 2000 both the second and third parameters to the iff functions will need to be of the same type so you'll need to change the null to something like 0.)

Another possibility, depending on what you are trying to do, is to use a the Filter() mdx function in an MDX query.

|||

Matt,

Your suggestion is exactly what I am trying to do. Thank you.

The expression is evaluating as I would have hoped. However, the measure is displaying NULL cells, which is one thing I don't want.

Could you suggest a way to prevent this? I have looked at NONEMPTY, but because of my lack of experience writing MDX I've had no luck.

Again, thanks for the help!!

|||

Here's an example of the use of non empty in an MDX Query in order to filter rows containing only nulls:

First the query that returns rows with null:

with member measures.a as iif([Measures].[Unit Sales]>500, [Measures].[Unit Sales], Null)
select {measures.a} on 0,
[Customer].[City].members on 1

Then the query with non empty added to remove the null rows:

with member measures.a as iif([Measures].[Unit Sales]>500, [Measures].[Unit Sales], Null)
select {measures.a} on 0,
non empty [Customer].[City].members on 1

Help understanding generated MDX WHERE clause

When your report datasource is a cube, MDX is generated when you use the design view. In the MDX editor the generated MDX can be viewed. Using parameters I always get a where clause with code like the following:

IIF( STRTOSET(@.OrgLevelname, CONSTRAINED).Count = 1, STRTOSET(@.OrgLevelname, CONSTRAINED), [Organisation].[Level 2 name].currentmember )

I like to understand what is generated. Is there something I can read on the generated WHERE clause (I do understand the generated SELECT and FROM clauses)? Or can someone shed a light on it?

Why does the MDX need to branch on 'Count = 1' In what way does the result slice my data when Count = 1 or when Count <> 1?

Thanks,
Henk

Really no MDX guru around who can explain this to me?|||

Henk,

I would suggest posting your MDX question in the SQL Server Analysis Services forum at this url:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=83&SiteID=1

|||Thanks, I'll do that.