retrieving parent(ascendants)

i have a dimension named 'sdoc' which has 2 members
1) symptom word( first hierarchical level) and
2) doc id (second hierarchical level)

more than one 'symptom word' can have the same 'doc id'.
i want to know which all symptom words have a particular doc id (say 10.)
if i give [10].parent then it returns only the first parent whereas it actually has more than one parent.
how to retrieve all the parents.more than one 'symptom word' can have the same 'doc id'. So...there is a one-to-many relationship between 'doc id' and 'symptom word'?
if i give [10].parent then it returns only the first parent whereas it actually has more than one parent.So...there is a one-to-many relationship between 'symptom word' and 'doc id'?

Huh?

In database relationships, records normally have only one parent...

Please post the DDL code for the tables you are referencing, along with the SQL code you have tried.

Retrieving FirstChild, LastChild from a Distant Parent

I have a Time Dimension, [Time].[Fiscal Year] which has three levels: Year, Qtr, Month.

My situation requires that I get the First member of the first Qtr (month) and the last member of the last Qtr (month) given only the Year. I am close to the solution, if this is possible, and really need some help to finish this. Here is a speific scenario:

From my application, user selects "Fiscal Year" as one of the fiters. Year = 2007 (No additional level is given to select).
The calculation I am trying to form is for % growth ((Measures for FY 2007 - Measures for FY 2006) / Measures for FY 2006).

My problem is that the the total periods for FY 2007 do not yet equal the total periods for 2006. So what I want to do is calculate the growth by summing the periods that exist in 2007 at the month level. Then summing those same periods for the previous period. I can do this by getting the first existing member of the first month and the last existing member of the last month. To expand on the scenario:

FY 2007 has 6 Months of data:

FY 2007
Q1
July
Aug
Sep
Q2
Oct
Nov
Dec

FY 2006 has all 12 Months of Data (Q1, Q2, Q3, Q4)

Given only FY 2007, I need to retrieve Member July and Dec.

The closest I have come to this is:

SET [FirstMonth] AS
'{(Descendants([Time].[Fiscal Year].[2007].FirstChild, 2, SELF))}'

SET [LastMonth] AS
'{(Descendants([Time].[Fiscal Year].[2007].LastChild, 2, SELF))}'

This gives me:

[FirstMonth]

July
Aug
Sep

[LasstMonth]

Oct
Nov
Dec

When running in Select statement. Can this even be accomplished with MDX?

Take a look at OpeningPeriod() and ClosingPeriod() MDX functions - something like:

OpeningPeriod([Time].[Fiscal Year].[Month]) and ClosingPeriod([Time].[Fiscal Year].[Month])

http://msdn2.microsoft.com/en-us/library/ms145992.aspx

>>

SQL Server 2005 Books Online

OpeningPeriod (MDX)

Updated: 17 July 2006

Returns the first sibling among the descendants of a specified level, optionally at a specified member.

...

>>

|||Nice! Thank you very much.

Retrieve value within a data flow

hi,

I am doing a data flow to insert data into a dimension table(A) and master surrogate key table(B). Master surrogate key table(B) will be inserted prior to dimension table(A). My dimension table(A)'s key is depend on the last key in master surrogate key table(B). The data flow started from a flat file and checking if the key found in dimension table. If not found, insert a record into B. Then generate the new record to A with max id from B.

My question is how to retrieve the max id in master surrogate key table(B) in a data flow and use it as an input to my dimension table?

Have you tried the LOOKUP component? That's the sort of thing it is designed to do.

-Jamie

retrieve the value that occurs the most

Hi,

I am trying to retrieve the value that occurs the most at a dimension level. For example, I have to analyze the product type mostly used in a specific target segment by number of products.

The mathematical function normally used is MODE. However, in AS2005 there is not such function. I tried to use TopCount but it is not as easy as I thought.

Any suggestion?

Thanks!

As far as I know, TopCount should work.

This is a sample from Adventure Works:

with

set [TopCustomers] as

TOPCOUNT(

[Dim Customer].[Dim Customer].[Dim Customer].members,

1,

[Measures].[Sales Amount]

)

select {[Measures].[Sales Amount]} on 0,

[TopCustomers] on 1

from [Adventure Works DW]

Hope this helps,

Santi

|||I think TopCount would only get you a mode if you were to use it against a count measure. I am guessing that you want to find out which member from the product dimension has the most transactions against it for a given set of criteria. Depending on the granularity of your dimensions you may have difficulty doing this without having some sort of count measure.