Autoexist when queying

Hi all,

I think it's a simple question but i can't seem to get it right:

I have a parent child dimension, with key Object, and an attribute Type. Every Object has a type, consider this example

Object Type
--
Top Model
Child 1 Portfolio
Child 2 PortFolio

When i run this query:

select
time.month.members on 0,
objects.object.members * objects.[type].members on 1
from [Dream2007]

I get this on Axis 0:
Top Model
Top Portfolio
Child 1 Model
Child 1 Portfolio
Child 2 Model
Child 2 Portfolio

How can i het the following result with only the existing combinations?:

Top Model
Child 1 Portfolio
Child 2 PortFolio

I tried non empty, but that doesn't work because they can be null. I also have an attribute relationship between Object and Type.

Any ideas?

TIA,

GJ

Maybe there's some issue with the attribute relationships - you can compare with the Adventure Works Organization parent-child dimension, where each Organization has a Currency Code:

>>

select {} on 0,

[Organization].[Organization].[Organization].Members

* [Organization].[Currency Code].[Currency Code].Members on 1

from [Adventure Works]

-

AdventureWorks Cycle USD
Australia AUD
Canadian Division CAD
Central Division USD
European Operations EUR
France EUR
Germany EUR
North America Operations USD
Northeast Division USD
Northwest Division USD
Pacific Operations AUD
Southeast Division USD
Southwest Division USD
USA Operations USD

>>

|||

I tried to set the Cardinality of the relationship to one, but this did not change it.

Actually, when i looked at it more closely, higher members have their own type, and the types of their children too. So the top object has all the types of all members, the leaf members for the PC hierarchy have only their own type (and the [all] type if i set IsAggretable to true) I only checked the top members at first, so it seemd as if it was the crossproduct of all members from both hierarchies.

Is there any way to disable this behaviour, so a member would only have the type it gets from the dimtabel row, not all its children? Or is this conceptually impossible?

I have a lot of caluculations that need to apply to a specific type of member only. I could scope on Level.Ordinal, but then i would have to keep track of which types of objects exist on which levels.

Regards,

GJ

|||

Going back to the Adventure Works Organization dimension, I think this query illustrates the issue which you raise:

>>

select {} on 0,

[Organization].[Organizations].Members

* [Organization].[Currency Code].[Currency Code].Members on 1

from [Adventure Works]

AdventureWorks Cycle AUD
AdventureWorks Cycle CAD
AdventureWorks Cycle EUR
AdventureWorks Cycle USD
European Operations EUR
France EUR
Germany EUR
North America Operations CAD
North America Operations USD
Canadian Division CAD
USA Operations USD
Central Division USD
Northeast Division USD
Northwest Division USD
Southeast Division USD
Southwest Division USD
Pacific Operations AUD
Australia AUD

>>

Comparing it to the earlier query, each member is located in both the dimension key attribute hierarchy: [Organization].[Organization], and in the parent-child hierarchy: [Organization].[Organizations]. And in the attribute hierachy, each member is only associated with a single currency. Can you give an idea of what scoped calculations you need; and could these be translated to the Adventure Works Organization dimension?

|||

I tried it with the key hierarchy, not the PC, and indead now get the results i expected.

I guess it would be logical for members in a PC hierarchy to have the attributes of it's descendants too for rolling up additive measures, because else there would be nowhere for these aggregates to go, or they wouldn't show up in queries.

The problem we have is we're looking to migrate a forecasting app we built on AS2000 to 2005. we have loads of calculated members like this one that all build upon each other

CREATE MEMBER CURRENTCUBE.[PP].[Calcs200].[WO_component_ML_maandbedrag] AS
iif(Object.CurrentMember.Level Is Object.Levels(7),
Iif(Object.CurrentMember.Properties("ObjectType") = "WO_component",
Iif(Object.CurrentMember.Properties("ObjectLevel") = "8",
iif(Tijd.CurrentMember Is Tijd.Members.Item(Val(Object.Currentmember.Properties("ObjectStartDateOrdinal"))) ,
0 + (0 + Val([Object].Properties("Component_Maandbedrag"))),
0 + (Tijd.Members.Item(Val(Object.Currentmember.Properties("ObjectStartDateOrdinal"))), [PP].[WO_component_ML_maandbedrag] )
),
0),
0),
iif(Object.CurrentMember.Level.Ordinal < 7,
0 + SUM(Filter(Descendants(Object.CurrentMember, Object.levels(7)), Object.CurrentMember.Properties("ObjectLevel") = "8" AND Object.CurrentMember.Properties("ObjectType") = "WO_component"), ([PP].[WO_component_ML_maandbedrag]) ) ,
0 + (Ancestor(Object.CurrentMember, Object.levels(7)), [PP].[WO_component_ML_maandbedrag] )
)
), SOLVE_ORDER= 11 , FORMAT_STRING='#,##.##' ;

Where we use iif(Object.Currentmember.Level or Object.Currentmember.Properties to make sure the calcs get done (only) on the right spot.

So we were pretty excited about Scope, because then we wouldn't have to use Currentmember and Filter all the time, this would eliminate all the extra things we have to do to solve performace.

Now i'm trying to understand how this works exactly, but i'm still having trouble getting my head around 2005. 2000 was alot easier in this respect.

I don't think adventureworks has anything that comes near, but i'll look into it.

Regards, GJ

|||

Unfortunately, I can't quite comprehend all the logic in the calculated member above. But for the problem of filtering members of a parent-child hierarchy with a specific property value, using "Autoexist" behavior - here's an Adventure Works query which returns members of the [Organization].[Organizations] hierarchy whose Currency Code is "CAD" - and it excludes any ancestors, since their own Currency Code isn't "CAD":

>>

select {} on 0,

Generate([Organization].[Organizations].Members,

{([Organization].[Organizations].CurrentMember,

StrToMember("[Organization].[Organization].&["

+ [Organization].[Organizations].Properties("Key")

+ "]"),

[Organization].[Currency Code].[CAD])}) on 1

from [Adventure Works]

Canadian Division Canadian Division CAD

>>

|||

PS: maybe I did learn something at last month's SQL PASS in Seattle - my memory just flashed back to a session where the use of LinkMember() in lieu of StrToMember() was mentioned (blog link below); so here's a cleaner version of the query:

select {} on 0,
Generate([Organization].[Organizations].Members,
{([Organization].[Organizations].CurrentMember,
LinkMember([Organization].[Organizations].CurrentMember,
[Organization].[Organization]),
[Organization].[Currency Code].[CAD])}) on 1
from [Adventure Works]

http://sqljunkies.com/WebLog/mosha/archive/2006/11/16.aspx

>>

SQL PASS in Seattle: What has the UDM Done For You Lately by Dan Bulos

.. 'bad' MDX functions for AS2005. StrToMember is one of them..we agreed that in that particular scenario the script indeed could've been rewritten to use LinkMember..

>>

Auto increment in SQL

Hi. I'm trying to use an auto increment field in SQL, but it isn't quite doing what I want. I have a parent table and the child tables are set up with IDENTITY. The only problem is that SQL auto increments over all the tables, instead of auto-incrementing for each table. For example, KB table 1 has CDSS_key fields 1, 3, 4, 6 and KB table 2 has CDSS_key fields 2, 5, 7. I would like to have KB table 1 to have CDSS_key fields 1, 2, 3, 4 and KB table 2 to have CDSS_key fields 1, 2, 3, 4. Can anyone help? This is my create script:

CREATE TABLE CDSS (
CDSS_app char(10),
timestamp datetime DEFAULT getdate(),
PRIMARY KEY (CDSS_app) )

CREATE TABLE KB (
CDSS_key int IDENTITY(1, 1),
submit_by char(50),
timestamp datetime DEFAULT getdate(),
common_prob char(100),
prob_sol char(511),
CDSS_app char(10),
PRIMARY KEY (CDSS_key),
FOREIGN KEY (CDSS_app) REFERENCES CDSS (CDSS_app) )Please explain more clearly. KB table 1? KB table 2? What are they really named? Do they have identical structures? I only see you creating a single "KB" table.

blindman|||Originally posted by blindman
Please explain more clearly. KB table 1? KB table 2? What are they really named? Do they have identical structures? I only see you creating a single "KB" table.

blindman

Here's the script. The results are below. Thanks in advance!

CREATE TABLE CDSS (
CDSS_app char(10),
timestamp datetime DEFAULT getdate(),
PRIMARY KEY (CDSS_app) )

INSERT INTO CDSS (CDSS_app) VALUES ('OASIS')
INSERT INTO CDSS (CDSS_app) VALUES ('CIS')

CREATE TABLE KB (
CDSS_key int IDENTITY(1, 1),
submit_by char(50),
timestamp datetime DEFAULT getdate(),
common_prob char(100),
prob_sol char(511),
CDSS_app char(10),
PRIMARY KEY (CDSS_key),
FOREIGN KEY (CDSS_app) REFERENCES CDSS (CDSS_app) )

INSERT INTO KB (submit_by, common_prob, prob_sol, CDSS_app)
VALUES ('test1', 'test1', 'test1', 'OASIS')

INSERT INTO KB (submit_by, common_prob, prob_sol, CDSS_app)
VALUES ('test2', 'test2', 'test2', 'OASIS')

INSERT INTO KB (submit_by, common_prob, prob_sol, CDSS_app)
VALUES ('test3', 'test3', 'test3', 'CIS')

INSERT INTO KB (submit_by, common_prob, prob_sol, CDSS_app)
VALUES ('test4', 'test4', 'test4', 'OASIS')

SELECT CDSS_key, CDSS_app FROM KB

Results:

CDSS_key CDSS_app

1 OASIS
2 OASIS
3 CIS
4 OASIS

I want it to be:

CDSS_key CDSS_app

1 OASIS
2 OASIS
1 CIS
3 OASIS|||I'm sorry but you can't do this automatically. You would need to create your index field as a simple integer value and then write code that would update new values to the maximum existing value for the CDSS_app + 1. You could put it in a trigger, or in the stored procedure used to populate the table.

Generally, an issue such as this indicates a problem with the database design. I encourage you to rethink your application and see if you can come up with a better implementation.

blindman