New partition (SSAS 2005) - named query not in "available tables"

I'm trying to create another partition in an existing measure group.
I start the new partition wizard and choose my data source view in the
"Look in", and then click "Find tables".
My named query does not appear in the list. What could be wrong?

I have saved and refreshed the data source view. The named query has the
same column names and data types that the original table has (that the
measure group is using now).

I also tried to make the named query a ordinary view in the source
database but that does not appear in the list either (I choose the
data source then instead of the data source view).

Hi,

I wouldn't suspect that you'd see a named query in the list of tables when you go to create a new partition. I'm not sure if that list of tables shows views from the underlying DB, either..But, you can make your partition query binding, instead of table binding, and you can make the partition source the same query you're using in your named query, or a subset of it with a where clause, or whatever you choose.

HTH,
C
|||

I don't understand...

In the "New partition wizard" there isn't a whole lot to choose from... Pick either a data source or a data source view, and then pick the "table" - which I suppose could be a view, table, named query... When I choose data source view I can only see the table that is already being used as a fact table in the measure group (and this is actually a named query and not a table). When I choose the data soure, eg. the underlying database, I get nothing.

|||

I found the answer myself.

I thought I had the same datatypes because I created my new named query from the original table that the first named query (the one being used in the measure group already) was based on. But it turned out that the first named query did not get the same datatypes i all columns as the underlying table.

In BOL it states that the tables (or named queries) used in the same measure group must be sufficiently similar - I would call it exactly the same :-)

New Measure Based on Attribute Values

I'd like to add a new measure to my cube which is based upon values of an attribute. For example I'd like the measures to always apply the filter [Geography].[Country].&[Australia] applied even if the Geography attribute isn't included in the SELECT.

I've tried:

CREATEMEMBERCURRENTCUBE.[measures].[Oz Internet]

AS

AGGREGATE

(

EXISTING

(

{

[Geography].[Country].&[Australia]

}

*

[Sales Channel].[Sales Channel].&[Internet]

)

),

but this doesn't return anything. Is there a way to do this?

Here is an example where I limit a specific measure by a dimension member:

Code Snippet

withmember [Measures].[Reseller Sales Amount Bikes] as

([Measures].[Reseller Sales Amount],[Product].[Category].[Bikes])

select

{[Measures].[Reseller Sales Amount],[Measures].[Reseller Sales Amount Bikes]} on 0,

[Date].[Calendar].[Month].Memberson 1

from [Adventure Works]

Bryan|||Thanks for that. What if I needed it to work for more than one category eg Bikes and Accessories? I'm sure it's going to be simple but I can't get the syntax right.|||

One way to pull out the individual measure values and add them. That's seen in the first calculated member.

In the next calculated member, you can see I build a set of members, get the measure against this set and then aggregate. You can build that first set many different ways.

Good luck,
Bryan

Code Snippet

withmember [Measures].[Reseller Sales Amount Bikes+] as

([Measures].[Reseller Sales Amount],[Product].[Category].[Bikes])+

([Measures].[Reseller Sales Amount],[Product].[Category].[Accessories])

member [Measures].[Reseller Sales Amount Bikes+ Too] as

AGGREGATE({[Product].[Category].[Bikes],[Product].[Category].[Accessories]},[Measures].[Reseller Sales Amount])

select

{[Measures].[Reseller Sales Amount],

[Measures].[Reseller Sales Amount Bikes+],

[Measures].[Reseller Sales Amount Bikes+ Too]} on 0,

[Date].[Calendar].[Month].Memberson 1

from [Adventure Works]

|||

That's very helpful. Thanks Bryan.

Mark

New Measure Based on Attribute Values

I'd like to add a new measure to my cube which is based upon values of an attribute. For example I'd like the measures to always apply the filter [Geography].[Country].&[Australia] applied even if the Geography attribute isn't included in the SELECT.

I've tried:

CREATEMEMBERCURRENTCUBE.[measures].[Oz Internet]

AS

AGGREGATE

(

EXISTING

(

{

[Geography].[Country].&[Australia]

}

*

[Sales Channel].[Sales Channel].&[Internet]

)

),

but this doesn't return anything. Is there a way to do this?

Here is an example where I limit a specific measure by a dimension member:

Code Snippet

withmember [Measures].[Reseller Sales Amount Bikes] as

([Measures].[Reseller Sales Amount],[Product].[Category].[Bikes])

select

{[Measures].[Reseller Sales Amount],[Measures].[Reseller Sales Amount Bikes]} on 0,

[Date].[Calendar].[Month].Memberson 1

from [Adventure Works]

Bryan|||Thanks for that. What if I needed it to work for more than one category eg Bikes and Accessories? I'm sure it's going to be simple but I can't get the syntax right.|||

One way to pull out the individual measure values and add them. That's seen in the first calculated member.

In the next calculated member, you can see I build a set of members, get the measure against this set and then aggregate. You can build that first set many different ways.

Good luck,
Bryan

Code Snippet

withmember [Measures].[Reseller Sales Amount Bikes+] as

([Measures].[Reseller Sales Amount],[Product].[Category].[Bikes])+

([Measures].[Reseller Sales Amount],[Product].[Category].[Accessories])

member [Measures].[Reseller Sales Amount Bikes+ Too] as

AGGREGATE({[Product].[Category].[Bikes],[Product].[Category].[Accessories]},[Measures].[Reseller Sales Amount])

select

{[Measures].[Reseller Sales Amount],

[Measures].[Reseller Sales Amount Bikes+],

[Measures].[Reseller Sales Amount Bikes+ Too]} on 0,

[Date].[Calendar].[Month].Memberson 1

from [Adventure Works]

|||

That's very helpful. Thanks Bryan.

Mark