Hiding a Subreport: hierarchy

Showing posts with label hierarchy. Show all posts

Friday, March 9, 2012

hierarchy structure

hello!
I have a hierarchy structure copied from excel to a table in databse SQL.
Here is what i have in SQL table:
ContinentCountryCity
Europe
Norway
Oslo
Bergen
Trondheim
Sweden
Stockholm
Gotaborg
Denmark
Kopenhagen
Aalborg
Germany
Hamburg
Munchen
Hannover
Asia
Japan
Tokyo
Hirroshima
Kina
Shanghai
Africa
South-Africa
Pretoria
AND her is what i want
ContinentCountryCity
Europe
EuropeNorway
EuropeNorwayOslo
EuropeNorwayBergen
EuropeNorwayTrondheim
EuropeSweden
EuropeSwedenStockholm
EuropeSwedenGotaborg
EuropeDenmark
EuropeDenmarkKopenhagen
EuropeDenmarkAalborg
EuropeGermany
EuropeGermanyHamburg
EuropeGermanyMunchen
EuropeGermanyHannover
Asia
AsiaJapan
AsiaJapanTokyo
AsiaJapanHirroshima
AsiaKina
AsiaKinaShanghai
AfricaKina
AfricaSouth-Africa
AfricaSouth-AfricaPretoria
How do i that?
On Wed, 25 May 2005 02:23:02 -0700, CJ wrote:

>hello!
>I have a hierarchy structure copied from excel to a table in databse SQL.
>Here is what i have in SQL table:
(snip)
Hi CJ,
Do you mean that each row has either Continent, or Country, or City
populated, but none of the other columns? I guess you're out of luck
then. Since there is no inherent order in a relational table, there is
no difference between the table you posted and this one:
ContinentCountryCity
Africa
Asia
Europe
Denmark
Germany
Japan
Kina
Norway
South-Africa
Sweden
Aalborg
Bergen
Gotaborg
Hannover
Hamburg
Hirroshima
Kopenhagen
Munchen
Oslo
Pretoria
Stockholm
Shanghai
Tokyo
Trondheim
Besides - since this table has no key, it is officially not a relational
table at all. Of course, terminology is pretty much a question of
definitions, but a table without primary key is usually impossible to do
any serious operations on.

>AND her is what i want
>ContinentCountryCity
>Europe
>EuropeNorway
>EuropeNorwayOslo
>EuropeNorwayBergen
>EuropeNorwayTrondheim
>EuropeSweden
>EuropeSwedenStockholm
>EuropeSwedenGotaborg
>EuropeDenmark
>EuropeDenmarkKopenhagen
>EuropeDenmarkAalborg
>EuropeGermany
>EuropeGermanyHamburg
>EuropeGermanyMunchen
>EuropeGermanyHannover
>Asia
>AsiaJapan
>AsiaJapanTokyo
>AsiaJapanHirroshima
>AsiaKina
>AsiaKinaShanghai
>AfricaKina
>AfricaSouth-Africa
>AfricaSouth-AfricaPretoria
>How do i that?
You don't. The data above has no key either, so it's not a relational
table. Consider moving the data to some properly normalized tables:
CREATE TABLE Countries
(Country varchar(20) NOT NULL,
Continent varchar(10) NOT NULL,
PRIMARY KEY (Country)
)
CREATE TABLE Cities
(City varchar(25) NOT NULL,
Country varchar(20) NOT NULL,
PRIMARY KEY (City),
FOREIGN KEY (Country) REFERENCES Countries (Country)
)
INSERT INTO Countries (Country, Continent)
VALUES ('Norway', 'Europe')
INSERT INTO Countries (Country, Continent)
VALUES ('Sweden', 'Europe')
....
INSERT INTO Countries (Country, Continent)
VALUES ('South-Africa', 'Africa')
INSERT INTO Cities (City, Country)
VALUES ('Oslo', 'Norway')
INSERT INTO Cities (City, Country)
VALUES ('Bergen', 'Norway')
....
INSERT INTO Cities (City, Country)
VALUES ('Pretoria', 'South-Africa')
The input needed to get the listing you describe above would be returned
by
SELECT co.Continent, co.Country, ci.City
FROM Countries AS co
INNER JOIN Cities AS ci
ON ci.Country = co.Country
ORDER BY co.Continent, co.Country, ci.City
The final reformatting to get it displayed exectly as above (including
the repeating of some lines with partial blank data) should be handled
by the presentation tier.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Hierarchy Problem after processing - Help!

Hi,

I'm using AS2005 and have time dimension with hierarchy as follows:

[yr_dt] --> [yr] --> [hyr] --> [qtr] --> [mon] --> [dt]

Normally, it show full hierarchy when I browse the hierarchy :
e.g. 2006 --> 1 half --> 1 quarter --> Jan --> 2006-01-01T00:00:00

However, after I change the data source view to include more data in specified data range and reprocess the cube, The hierarchy only shows [yr] nothing under [yr].
Only correct year is the last year (i.e. 2006). It show full hierarchy as above.

May I know what's wrong with AS2005. Or is it a bug ? Any method to solve ?
Thanks in advanceHi,

my time dimension has the following relationship:

[yr]
|-- [hyr]
cardinality : many
relationship type: rigid

[hyr]

|-- [qtr]

cardinality : many

relationship type: rigid

[mon]

|-- [qtr]

cardinality : many

relationship type: rigid

[dt]

|-- [mon]

cardinality : many

relationship type: rigid

When I remove all the above relationship and reprocess the cube. It works fine and
the full hierarchy show correctly.

May I know what's wrong with the above hierarchy relationship setting ?
thanks a lot .|||

Are your attributes unique?

i.e.

Year -> 2007

Quarters

Q1, 2007

Q2, 2007

Q3, 2007

Month

January, 2007

February, 2007

Day

1/1/2007

1/2/2007

etc

If they are not unique (i.e. A must imply B in your relationships, you can't have just January since January does not imply January in a specific year) then the aggregations don't work properly.

|||Hi mccpres,

thanks for your reply.
Is that you mean the KeyColumn unique ? or the NameColumn Unique ?

My settings for [Mon] attribute
NameColumn : abbreviation of month (i.e. Jan, Feb ...etc)
KeyColumn : integer value of month (i.e. 1 to 12)

My settings for [Qtr] attribute

NameColumn : 'Q'mm + yyyy (i.e. Q1 2002, Q2 2002 ....etc)
KeyColumn : integer value of year (i.e. 2002, 2003 etc)

Here, NameColumn is unique to corresponding year. But the KeyColumn is not.
So, seems make the NameColumn unique would solve the issue.

Also, there's ValueColumn properties for each attribute. It seems that the ValueColumn should be same as KeyColumn, right ? Then why we need ValueColumn ?

Above maybe just simple questions, but just want to clarify my understanding as I new to ssas. Thanks a lot !|||hi mccpres,

thanks for your reply.
Is that you mean Keycolumn unique or NameColumn unique ?

I change my setting for [qtr] attribute:
NameColumn: 'Q'mm+yyyy (ie. Q1 2002, Q2 2002 ...etc)
KeyColumn: integer value of quarter (i.e. 1, 2, 3, 4 )

for [yr] attribute:
NameColumn : 'Y'+yyyy (ie Y2002, Y2003 ...etc)
KeyColumn: integer value of year (ie 2002, 2003 ...etc)

Here NameColumn for [qtr] is unique to corresponding NameColumn of [yr]. But the KeyColumn is not.
I tried the above settings and seems make the NameColumn unique would solve the issue. right?

Also, is that we should set KeyColumn same as the ValueColumn ?

Above maybe just simple questions, appreciate if anyone can help as I'm new to ssas. thanks!|||Hi,

anyone can help ?

Hierarchy Problem

I need to create a stored procedure that creates a hierarchy for each employee in a large employee data table (CCINFORMATION).

Each entry in CCINFORMATION contains, among other things, the ID number of the employee, their title, their manager's ID, and their manager's title.
I need to use this table to build a string for each employee. That string should contain the ID of everyone in that employee's hierarchy up to the CEO, and then I need to place that string into a different table.

I don't know Transact-SQL really well, so I am at a little bit of a loss how to set up the logic to go through each record in CCINFORMATION, and for each one build the string as I run a series of queries based on the manager's ID until I reach the CEO. Any ideas?I managed to get this stored procedure to work, so I thought I would share, in case it helps anyone else:

Declare @.AWID varchar(50), @.Title varchar(2000), @.MgrAWID varchar(50), @.MgrTitle varchar(2000), @.hstring varchar(2000), @.hnew varchar(2000), @.SEARCHAWID varchar(50)

Declare cursor1 CURSOR FOR
Select AWID, Title, MgrAWID, MgrTitle
FROM CCINFORMATION

OPEN cursor1
FETCH NEXT FROM cursor1
INTO @.AWID, @.Title, @.MgrAWID, @.Mgrtitle
WHILE @.@.FETCH_STATUS = 0
Begin
Set @.hstring = @.AWID + ',' + @.MgrAWID
IF patindex('%CEO%', @.Mgrtitle) > 0
BEGIN
--PRINT @.hstring + ' is going in right away'
INSERT INTO HIERARCHY (AWID, Hierarchy) VALUES (@.AWID, @.hstring)
END
ELSE
BEGIN
--PRINT @.hstring + ' begin subloop'
mgrloop:
Set @.SEARCHAWID = @.MgrAWID
Declare cursor2 CURSOR FOR
Select MgrAWID, MgrTitle
FROM CCINFORMATION
WHERE AWID = @.SEARCHAWID

OPEN cursor2

FETCH NEXT FROM cursor2
INTO @.MgrAWID, @.Mgrtitle

WHILE @.@.FETCH_STATUS = 0
Begin
set @.hstring = @.hstring + ',' + @.MgrAWID
IF patindex('%CEO%', @.Mgrtitle) > 0
BEGIN
--PRINT @.hstring + ' is going into Hierarchy'
INSERT INTO HIERARCHY (AWID, Hierarchy) VALUES (@.AWID, @.hstring)
CLOSE cursor2
Deallocate cursor2
set @.hstring=''
GOTO ceofound
END
ELSE
BEGIN
--PRINT @.hstring + ' isnt ceo'
CLOSE cursor2
Deallocate cursor2
GOTO mgrloop
END
END
FETCH NEXT FROM cursor2
INTO @.MgrAWID, @.Mgrtitle

CLOSE cursor2
Deallocate cursor2
--PRINT @.hstring + ' ends before ceo'
set @.hstring = @.hstring + ',fail'
INSERT INTO HIERARCHY (AWID, Hierarchy) VALUES (@.AWID, @.hstring)
set @.hstring=''
END

ceofound:
FETCH NEXT FROM cursor1
INTO @.AWID, @.Title, @.MgrAWID, @.Mgrtitle
END

CLOSE cursor1
DEALLOCATE cursor1

Hierarchy Parent-Child

Hi.

I have a parent-child relationship in a table, by making use of an ID, and Parent ID.

Firstly how do I manually change, or setup this hierarchy.

Secondly, how do I do a rollup, along this hierarchy, sothat I can retrieve the aggregate values, using MDX?

I thank you in advance for your feedback.

Hi. Sounds like you should start with the basics in Analysis Services about parent-child hierarchies. Here's a link to MSDN on-line which explains how parent-child hierarchies work in SSAS 2005. The subsequent links at the bottom of the page can guide you to more detail on creating the parent-child hierarchy in BI Studio.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agdimensions_5ab7.asp

Your second question, "how do I do a rollup, along this hierarchy, so that I can retrieve the aggregate values, using MDX?". There is a simple answer to your question. The answer is "nothing". The normal aggregation behavior of Analysis Services will aggregate the measures up your parent-child hierarchy. If you have a measure such as 'sales' and it has an aggregtion type of SUM, then the 'sales' values are naturally summed as you roll up the parent-child hierarchy. There is no action required on your part unless you want to change the SUM aggregation of the 'sales' measure.

Hope this helps - PaulG

|||

Hi. After a more thorough look, I determined that BI Studio seem to have sucessfully determined the hierarchies.

I can say this, based on the 'levels', that is shown under the parent view. The problem that I now have, is that for two tables, of exactly the same structure, BI is showing me the ID's of one table, and the 'name' values for another. I

have already tried to set the 'NameColumn' property of the parent id, as well as the source id, but it does not seem to work. Is there any place, where I can get good articles, on how to change these hierarchies. Or send a sample to someone?

The reason why I feel that my hierachy isnt rolling up, is the following. When I run the MDX query:

select {[Measures].[People]}on columns,{[FAFA Location].[FAFA Location].[Africa]} on rows

I get 'null', although if I do a level mdx, with 'africa' included, I get a rollup value back.

Regards.

|||

Hi. For your first problem, getting a "name" to display instead of the ID, you need to manage the properties of the child attribute which is usually the primary key for your dimension. Set the NameColumn property of child attribute to the "name" column from your parent-child table.

Second problem, getting null when querying, I can't say what the problem is without debugging your model. The most common problem is that the dimension is NOT linked properly to the fact data. What is the result when you execute a query against leaf-level descendant members of [Africa] in the dimension (I'm assuming that [Africa] is a non-leaf level member)?

Hope this Helps. Paul Goldy

Hierarchy Parent-Child

Hi.

I have a parent-child relationship in a table, by making use of an ID, and Parent ID.

Firstly how do I manually change, or setup this hierarchy.

Secondly, how do I do a rollup, along this hierarchy, sothat I can retrieve the aggregate values, using MDX?

I thank you in advance for your feedback.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agdimensions_5ab7.asp

Hope this helps - PaulG

|||

Hi. After a more thorough look, I determined that BI Studio seem to have sucessfully determined the hierarchies.

The reason why I feel that my hierachy isnt rolling up, is the following. When I run the MDX query:

select {[Measures].[People]}on columns,{[FAFA Location].[FAFA Location].[Africa]} on rows

I get 'null', although if I do a level mdx, with 'africa' included, I get a rollup value back.

Regards.

|||

Hope this Helps. Paul Goldy

Hierarchy ORBER BY

Hi, I'm trying to figure out how to write I SP on the following table that will produce the results show at the bottom. My main problem is the sorting, is there a way I loop through the records in TSQL and ORDER BY SortSort where the ParentID's are the same?

Thank you.

Table example


IDParentIDSortOrderCaption
1null1Main
211Files
313Folders
422File2
512Holders
621File1
752Holder2
851Holder1

Required result example

Main
-Files
--File1
--File2
-Holders
--Holder1
--Holder2
-FoldersI don't think a simple order by statment can do this job. You may need to develop a stack algorithm and a cursor to loop through records.|||Check this article. That's your answer

www.aspalliance.com/388

AP|||Thank you! I also got some good responses from the sqlteam.com forum at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33222

Hierarchy Members

Is it possible to hide an Hierarchy member?

Regards

I am not quite sure what you are looking for here, but it is possible to hide an "attribute" that is part of your dimension definition. Click on the "attribute" you want to hide and then go to properties for that attribute. There is a property called AttributeHierarchyVisible which can be set to "False" to hide the attribute. If you are trying to hide individual members from certain groups of users you can use security roles. Create a security role and then go to the "Dimenension Data" tab. Here you can choose which members to allow or deny from members of the role.

HTH,

Steve

Hierarchy Member MDX Name

I created a 'Budgeting Time' hierarchy from a dimension table that looks like:

IDYearMonth08C5DC29-6E61-4625-BA20-CFDFE5D3232D2006Jan

KeyColumns is binded to Year/Month and so is NameColumn. The MDX representation of the year 2006 is now [Budgeting Time].[Budgeting Time].[Year].&[2006] but is there anyway to have the ID as the member identifier so the MDX looks like [Budgeting Time].[Budgeting Time].[Year].&[08C5DC29-6E61-4625-BA20-CFDFE5D3232D]?

If your entity can equally be uniquely identified either by ID or { Year, Month } pair you can choose ID to be the key column of your attribute. MDX code refers to unique names, which often use the key columns of attributes. If you choose ID to be the key column of the attribute then you will have the desired representation of the unique name.

When you browse your dimension the user interface will show captions, which will be resolved through the NameColumn binding. I do not know how you succeeded to bind Name to Year/Month since NameColumn can only be one and not a collection like KeyColumns. Probably you created a calculated column on DSV and provided a formula joining the name of the month and year together. This can stay if you choose ID to be the key column of the attribute.

I am not sure about your goal to have specific unique names, but key columns are chosen by the relationships with a fact table or a dimension table. If we are talking about the granularity attribute then it will participate in some relationship with either a fact table (regular relationship) or another dimension table (referenced relationship). If it is ID, which participates in that relationship then you would better choose ID for the key column. If your fact table or referenced dimension table contains Year/Month pair then you would better choose Year/Month to be the key columns.

Hierarchy Member MDX Name

I created a 'Budgeting Time' hierarchy from a dimension table that looks like:

Year

Month

08C5DC29-6E61-4625-BA20-CFDFE5D3232D

2006

Jan

If your

entity can equally be uniquely identified either by ID or { Year, Month } pair

you can choose ID to be the key column of your attribute. MDX code refers to

unique names, which often use the key columns of attributes. If you choose ID to

be the key column of the attribute then you will have the desired

representation of the unique name.

When you

browse your dimension the user interface will show captions, which will be

resolved through the NameColumn binding. I do not know how you succeeded to

bind Name to Year/Month since NameColumn can only be one and not a collection

like KeyColumns. Probably you created a calculated column on DSV and provided a

formula joining the name of the month and year together. This can stay if you

choose ID to be the key column of the attribute.

I am not

sure about your goal to have specific unique names, but key columns are chosen

by the relationships with a fact table or a dimension table. If we are talking

about the granularity attribute then it will participate in some relationship

with either a fact table (regular relationship) or another dimension table

(referenced relationship). If it is ID, which participates in that relationship

then you would better choose ID for the key column. If your fact table or

referenced dimension table contains Year/Month pair then you would better

choose Year/Month to be the key columns.

Hierarchy MDX

Below is my hierarchy :

Program 1

Supplier 1

Supplier 2

Supplier 3

Supplier N

I need an MDX expression which on retrieves Suppliers for Program 1 and so on.

So right now when i browse my cube I see all Suppliers for all Programs because of which there are alot of blank rows.

I need only Suppliers unique to each Program. So the final outcome of the hierarch above should be :

Program 1

Supplier 1

Supplier 5

Supplier 7

since Program 1 has only 3 suppliers.

TIA

- Russ

Not really all that clear on the structure of your dimension. Take a look at CROSSSJOIN, NONEMPTYCROSSJOIN, NONEMPTY, EXISTS, and the NON EMPTY keyword.

|||

Bryan,

Thanks for the pointers. I'll look into the Keywords that you mentioned.

However, here is my situation in a little more detail.... This is what it looks like when i browse my cube :

- Program 1

- Supplier 1

- Supplier 10

- Program 2

Supplier 1

Supplier 10

and so on..."ALL" the 10 suppliers are showing up for each program. However , Program 1 has only 3 suppliers and Program 2 has 5 suppliers. I need an MDX which can be applied as a formula at the Program level and applies to all its supplier children displaying only the ones which exist with values and not all 10 suppliers.

I could do this manuall by removing each Supplier which doesnt exist for that Program but i need a generic MDX which looks at the Program Number and applies it to its children at all levels.

Any ideas ?

Thanks

Russzee

|||

Hi russzee,

how the Programs and Suppliers are related in your cube desing? Only in fact table? Do you use any of wide spreaded SSAS Client like Excel or Proclarity or you write MDX self?

|||

Russzee,

Vladimir is trying to clarify the structure of your dimensions and cube. What is not clear is whether Programs and Suppliers exist within a single dimension or are part of two separate dimensions associated with your cube. Either one of these situations can give you a structure such as you identified.

|||

Hello,

Thanks once again for the follow up..

Program and Suppliers are two seperate dimensions in SSAS 2005 and there is a Fact Table called SupplierProgram linked in a Star Schema as shown below :

Program <- SupplierProgram > Supplier

So I have dynamic Supplier row members for each Program and i need an MDX which can identify the 'Supplier' row members on the fly from their parent 'Program' Members.

My client application is Performance Point Dashboard Designer which has no MDX support in CTP_2 so I'm looking to create this MDX in SSAS 2005 and port it to the Performance Point Designer.

Thanks

russzee

|||

Here are a few queries that might get you headed in the right direction. Within a dimension, SSAS employs auto-exists functionality for this kind of thing. Across dimensions, you have to leverage a fact table to determine what represents a valid combination. These results may all look the same (except for the baseline query that demonstrates a full cross product). Still, there are subtle differences between these you need to understand before taking one path or the other.

Good luck,
Bryan

Code Snippet

select -- BASELINE QUERY

[Measures].[Reseller Sales Amount] on 0,

Product.Category.Category.Members * Employee.Employee.Employee.Members on 1

from [Adventure Works]

;

select

[Measures].[Reseller Sales Amount] on 0,

NON EMPTY Product.Category.Category.Members * Employee.Employee.Employee.Members on 1

from [Adventure Works]

;

select

{} on 0,

NONEMPTYCROSSJOIN(Product.Category.Category.Members,Employee.Employee.Employee.Members) on 1

from [Adventure Works]

where [Measures].[Reseller Sales Amount]

;

select

{} on 0,

Product.Category.Category.Members *

NONEMPTY(Employee.Employee.Employee.Members,Product.Category.Category.Members)

on 1

from [Adventure Works]

where [Measures].[Reseller Sales Amount]

;

select

{} on 0,

Product.Category.Category.Members *

EXISTS(Employee.Employee.Employee.Members,Product.Category.Category.Members,'Reseller Sales')

on 1

from [Adventure Works]

;

Hierarchy is used more than once in the Crossjoin function

When I do this in Adventure Works:

SELECT NON EMPTY {
[Customer].[Customer Geography].[State-Province].Members *
[Customer].[Customer Geography].[City].Members
}
ON AXIS(0)
FROM [Adventure Works]

I get this: "The Customer Geography hierarchy is used more than once in the Crossjoin function."

How do I do the equivalent in an MDX statement that works? I want users to be able to drag multiple hierarchy levels onto the same axis. I have spyed on the MDX that BIDS is generating, and it seems to be cheating by not getting the states and citys back in the same MDX statement. It looks like its combining the results of two queries at run-time as the user expands nodes in the tree. Isn't there a better way?

Thanks,

Terry

Hi Terry,
Any reason why cross-joining the attribute hierarchies instead won't work for you, like:
SELECT NON EMPTY {
[Customer].[State-Province].[State-Province].Members *
[Customer].[City].[City].Members
}
ON AXIS(0)
FROM [Adventure Works]

|||

Hi Deepak,

Thanks for your reply. Our application allows users to drag-and-drop whatever they want, so in this instance I don't want to prevent the user from putting two hierarchy levels on the same axis. Maybe the solution is somehow use metadata to find the attribute names that built the hierarchy levels and use the attribute names behind the scenes in the crossjoin function? Is that possible?

|||You can use AMO to retrieve the necessary metadata to identify which attribute underlies a hierarchy level and its corresponding attribute hierarchy.

hierarchy in SSRS 2005?

is it possible to use a hierarchy (e.g: CountryCode, PostCode, CityName) as basis for a report parameter? I would like the user to have the possibility to drill down from e.g.: "Country = 'Austria' - PostCode '1010' - CityName 'Vienna'" and after that view the report with the selected parameters.

possible?

yes - no - maybe?

regards

pamike

Don't think so... At least using the standart issues!|||

is it hten possible to build the hierarchie in an Analysis Services Olap Cube and use parameters to pass througt to a predefined report? I also tried this but without any success;-(

thx

pamike

|||

Can't answer that... i'm not very experienced in Analisys Services...

Sorry...

|||

Are you looking to do this with one parameter or can it use multiple parameters? You can set up cascading parameters to do something like this. For example,

Parameter1 Datasource => SELECT countryid, country FROMCountryTable;
Parameter2 Datasource => SELECT stateid, state FROM StateTable WHERE countryid = Paramenters!Parameter1.Value;
Parameter3 Datasource => SELECT cityid, city FROM CityTable WHERE stateid = Paramenters!Parameter2.Value;

I am not sure if you can change the visibilty of each parameter after you select it (or if you need to).

|||

Small error. Those should read

Parameter1 Datasource => SELECT countryid, country FROM CountryTable;
Parameter2 Datasource => SELECT stateid, state FROM StateTable WHERE countryid = @.Parameter1 ;
Parameter3 Datasource => SELECT cityid, city FROM CityTable WHERE stateid = @.Parameter2;

|||

This is very possible using cascading parameters...

Parameter 1 (country code):

SELECT CountryCode FROM AccountGeography

Use something like the above as the dataset for the first parameter.

Multi value Parameter 2 (postal code):

SELECT PostalCode FROM AccountGeography WHERE CountryCode = @.CountryCode

Assuming you named the first parameter "CountryCode", the above statement will generate a list of postal codes that correspond to the CountryCode value selected in the first parameter.If the first parameter is a multi-valued parameter where you can select more than one CountryCode at a time then it gets a little trickier.To avoid brain numbing dynamic SQL you can use a user defined function to pass the comma delimited list of values from the CountryCode parameter to an IN statement in the child parameters.Example:

SELECT PostalCode FROM AccountGeography WHERE CountryCode IN (SELECT Param FROM ufn_MVParam(@.CountryCode,','))

Multi value Parameter 3 (city name):

SELECT CityName FROM AccountGeography WHERE PostalCode IN (SELECT Param FROM ufn_MVParam(@.PostalCode,','))

The third parameter is the same as the second except for that where parameter 2 uses the value of parameter 1 to filter its values, parameter 3 uses the value of parameter 2.

...Don't forget that when you're using reporting services parameters in your data set queries, the parameter name is case sensitive! WHERE CountryCode = @.CountryCode is not the same as WHERE CountryCode = @.Countrycode.

...SSRS will take care of generating the dynamic SQL for you!I'm not sure if that statement is technically correct, but it's at least operationally true.You will understand how wonderful this is if you have used SSRS 2000 x_x

...Here is the code to create the function which pareses the comma delimited string.I got this on the web somewhere but can't remember where:

CREATE FUNCTION [dbo].[ufn_MVParam](@.RepParam nvarchar(4000), @.Delim char(1)= ',')

RETURNS @.VALUES TABLE (Param nvarchar(4000))AS

BEGIN

DECLARE @.chrind INT

DECLARE @.Piece nvarchar(4000)

SELECT @.chrind = 1

WHILE @.chrind > 0

BEGIN

SELECT @.chrind = CHARINDEX(@.Delim,@.RepParam)

IF @.chrind > 0

SELECT @.Piece = LEFT(@.RepParam,@.chrind - 1)

ELSE

SELECT @.Piece = @.RepParam

INSERT @.VALUES(Param) VALUES(@.Piece)

SELECT @.RepParam = RIGHT(@.RepParam,LEN(@.RepParam) - @.chrind)

IF LEN(@.RepParam) = 0 BREAK

END

RETURN

END

Good luck!!

|||

My bad... you only need that crazy text parsing function when trying to pass multi value parameters to a stored procedure. If your result set is not generated using a stored procedure, all you need is the SSRS parameter name like this:

SELECT CityName FROM AccountGeography WHERE PostalCode IN (@.PostalCode)

with SSRS 2005 you would only need the additional user function when using a stored procedure as the data set in your report... and in that situation you wouldn't call the function from within SSRS, you would call it from within the sproc itself because SSRS passes the multi value parameter to SQL server as a comma delimted value string ("CountryCode1,CountryCode2,CountryCode3" etc.)

Hierarchy Equivalent of a Crossjoin

I want to pull back a cellset with two levels of a hierarchy on the same dimension. Using AdventureWorks as an example, let's say I want "Account Level 01" and "Account Level 02" both on the rows dimesion.

When I send MDX like this:

SELECT

{[Account].[Accounts].[Account Level 01].Members * [Account].[Accounts].[Account Level 02].Members}

DIMENSION PROPERTIES MEMBER_TYPE ON AXIS(0) FROM [Adventure Works]

I get this error message:

The Accounts hierarchy is used more than once in the Crossjoin function.

What MDX would I need in this situation? I've tried spying on the MDX generated by BIDs, but it uses so many temporary SETs that it's nearly impossible to follow.

Thanks for any help,

Terry

Terry,

If my understanding of what you are trying to do is correct, one possible solution would be to use the "Generate()" function:

Generate([Account].[Accounts].[Account Level 01].Members,

{[Account].[Accounts].CurrentMember,[Account].[Accounts].CurrentMember.Children})

HTH,

Steve

Hierarchy Data in XML

Hello All
I have two Table Employee and Dept
Employee Columns
EmpID
EmpName
MgrID
DeptID
EmpID(PK) MgrID (FK)
Dept COlumns
DeptID
DeptName
I need to return XMl Hierarchy when I pass DeptID as a parameter
So my final result shoudl look like this
<Dept>
<Emp ID="" Name="" ....>
<Emp ID="" Name="">
</Emp>
</Emp>
<Emp ID="" Name="">
</Emp>
<Emp ID="" Name="">
</Emp>
</Dept>
How do I write this Query
Regards
Hello Sekhar,
Take a look at the example I posted yesterday in microsoft.public.sqlserver.programming
in the post about returning arrays.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/

Hierarchy - Measure question

Hi,

In the cube, i'm using one fact table (I will call just A) as a fact (A) and dimension (A1).

From this table(A) , I created hierarchy ( division - department with primary key from this fact) in the dimension(A1).

And we have another fact table(B) which is not related to the fact (A) and dimension(A1) fisically.

Here is the fact table (B) as under:

Plan Fact : ID(pk), Item_key, Plan_Sales , Date_key

I can join this fact (B) with product dimension which has item_key (pk) and this product dimension has division and deparment key.

My question is that is there anyway to see plan sales measure with the hierarchy I created?

Please let me know.

Thanks in advance.

Hi there,

AFAIK, you could achieve this with Analysis Services 2005.

Your data source view would consist of your department dimension, linked to both of your fact tables. When creating your cube in Analysis Services 2005, the cube creation wizard should pick up that you have two fact tables and a single dimension (you may need to point it in the right direction though!).

This should generate you a cube schema, with a single dimension (department) and two measure groups. One measure group would contain measures from fact table A, and the other measure group would contain measures from fact table B. One use of a measure group is that it can group measures from a single fact table.

Using the cube browser you should be able to drag on your division-department hierarchy, along with the relevant measures from each measure group.

Hope that helps.

Cheers,

Jonathon

|||Actually you can create what is called a referenced relationship in AS2005 where one dimension is joined to a fact table through another dimension. So you could relate the A1 dimension to the Plan fact table through the Item dimension.

|||

Hi Darren,

Sorry for the late response and thanks for your reply.

I've tried to make a referenced relationship but it seems it does not give a right value.

if I select the reference dimension attribute : divdep ( which includes all attributes in divdep dimension) and intermediate dimension attribute : item_key ( pk in product dimension) which I join the fact table with, then the total is fine but in each division level and department level , the numbers are not correct.

If I select reference dimension attribute : division name and intermediate dimension attribute: division name , then I can see the right number only in division level. ( My question is do i need to select lowest level in reference dimension attribute? in this case , department name is the lowest in hierarchy. But it gives an error since this dimension does not have some attributes which appears in fact table.)

I need to view the data with division- department hierarchy and I've tried to do every single possible thing but I don't have any clue yet.

I appreciate if you can give me any comments.

Thanks.

|||

I'm not sure I'm completely understanding how you have things setup.

If you have a product dimension which includes an attribute for the department, you should be able to join the department dimension at it's key to the department attribute in the product dimension.

There is an example of a reference relationship in the Adventure Works cube, have a look at the Geography dimension and you should be able to find an example.

Wednesday, March 7, 2012

Hierarchy - Measure question

Hi,

In the cube, i'm using one fact table (I will call just A) as a fact (A) and dimension (A1).

From this table(A) , I created hierarchy ( division - department with primary key from this fact) in the dimension(A1).

And we have another fact table(B) which is not related to the fact (A) and dimension(A1) fisically.

Here is the fact table (B) as under:

Plan Fact : ID(pk), Item_key, Plan_Sales , Date_key

I can join this fact (B) with product dimension which has item_key (pk) and this product dimension has division and deparment key.

My question is that is there anyway to see plan sales measure with the hierarchy I created?

Please let me know.

Thanks in advance.

Hi there,

AFAIK, you could achieve this with Analysis Services 2005.

Using the cube browser you should be able to drag on your division-department hierarchy, along with the relevant measures from each measure group.

Hope that helps.

Cheers,

Jonathon

|||

Hi Darren,

Sorry for the late response and thanks for your reply.

I've tried to make a referenced relationship but it seems it does not give a right value.

I need to view the data with division- department hierarchy and I've tried to do every single possible thing but I don't have any clue yet.

I appreciate if you can give me any comments.

Thanks.

|||

I'm not sure I'm completely understanding how you have things setup.

If you have a product dimension which includes an attribute for the department, you should be able to join the department dimension at it's key to the department attribute in the product dimension.

There is an example of a reference relationship in the Adventure Works cube, have a look at the Geography dimension and you should be able to find an example.

Hierarchy - Measure question

Hi,

In the cube, i'm using one fact table (I will call just A) as a fact (A) and dimension (A1).

From this table(A) , I created hierarchy ( division - department with primary key from this fact) in the dimension(A1).

And we have another fact table(B) which is not related to the fact (A) and dimension(A1) fisically.

Here is the fact table (B) as under:

Plan Fact : ID(pk), Item_key, Plan_Sales , Date_key

I can join this fact (B) with product dimension which has item_key (pk) and this product dimension has division and deparment key.

My question is that is there anyway to see plan sales measure with the hierarchy I created?

Please let me know.

Thanks in advance.

Hi there,

AFAIK, you could achieve this with Analysis Services 2005.

Using the cube browser you should be able to drag on your division-department hierarchy, along with the relevant measures from each measure group.

Hope that helps.

Cheers,

Jonathon

Hi Darren,

Sorry for the late response and thanks for your reply.

I've tried to make a referenced relationship but it seems it does not give a right value.

I need to view the data with division- department hierarchy and I've tried to do every single possible thing but I don't have any clue yet.

I appreciate if you can give me any comments.

Thanks.

|||

I'm not sure I'm completely understanding how you have things setup.

If you have a product dimension which includes an attribute for the department, you should be able to join the department dimension at it's key to the department attribute in the product dimension.

There is an example of a reference relationship in the Adventure Works cube, have a look at the Geography dimension and you should be able to find an example.

Hierarchy - Finding loops in the data

I've spent hours trying to find the answer to this with no luck. One
of our systems tracks connections between objects. If a user enters an
object that is connected to to another object that is connect to its
parent object, we have problems.
We have many diffent type of objects, so I will try to give a simple
example. If we have a meter "Meter A" that is connected to a tank
"Tank A" and that tank is connected to another meter "Meter B" which is
connected to "Tank B". The problem arises when "Tank B" is connected
to "Meter A"
Meter A <-> Tank A <-> MeterB <--> Tank B <-//-> Meter A
As you can see, there is a "loop" in connectivity. The data is store
as hierarchial data as upstream and downstream attributes. Using
hierarchial queries, I've been able to retrieve the data in a
hierarachial representation, but my goal is to determine if there is a
loop in the data. Just to make it clear, its the same as the
Manager/Employee example seen everywhere except the idea is to create a
process to ensure that no manager reports to his employee.
A sample dataset is provide for your much appreciated assistance.
--DST = DownStreamType
--DID = DownStreamID
--UST = UpstreamType
--UID = UpstreamID
DROP TABLE #Conn
CREATE TABLE #Conn (DST int, DSID int, UST int, UPID int)
--Dataset connects with no loop
INSERT INTO #Conn VALUES (1,1,1,2)
INSERT INTO #Conn VALUES (1,2,1,3)
INSERT INTO #Conn VALUES (1,3,2,1)
INSERT INTO #Conn VALUES (2,1,2,2)
INSERT INTO #Conn VALUES (2,2,2,3)
INSERT INTO #Conn VALUES (2,3,3,1)
--Dataset loops (each record is connected to each other, last
--record connects back to first record.
INSERT INTO #Conn VALUES (1,10,1,20)
INSERT INTO #Conn VALUES (1,20,1,30)
INSERT INTO #Conn VALUES (1,30,2,10)
INSERT INTO #Conn VALUES (2,10,2,20)
INSERT INTO #Conn VALUES (2,20,2,30)
INSERT INTO #Conn VALUES (2,30,1,10)
TIAIf you search the web for sql+cycle+detection+graphs, you'll find some
things
that should help.
Your hierarchy representation is a bit different than most, since you
don't store the nodes anywhere, just the edges, so a "dangling" edge
is harder to spot (usually it would have NULL as the parent or child).
Here's one easy-to-implement solution that might work, though it will
probably be less efficient than other solutions for large data sets.
I also haven't taken the time to verify that it's sound, but I think it is:
repeatedly remove dead-end links from the network/table. If you end
up with nothing, there were no loops. If you end up with a sub-network
having no dead ends, there must be a loop involving the remaining
links.
-- start with all non-dead-end links
select DST, DSID, UST, UPID
into #remnants
from #Conn as C1
where exists (
select * from #Conn C2
where C2.UST = C1.DST
and C2.UPID = C1.DSID
)
while @.@.rowcount <> 0
delete from #remnants
where not exists (
select * from #remnants as R2
where R2.UST = #remnants.DST
and R2.UPID = #remnants.DSID
)
select * from #remnants
go
-- Steve Kass
-- Drew University
-- 0D2B1B93-2E53-448C-B441-61B1C5EF1F40
carmaboy@.gmail.com wrote:

>I've spent hours trying to find the answer to this with no luck. One
>of our systems tracks connections between objects. If a user enters an
>object that is connected to to another object that is connect to its
>parent object, we have problems.
>We have many diffent type of objects, so I will try to give a simple
>example. If we have a meter "Meter A" that is connected to a tank
>"Tank A" and that tank is connected to another meter "Meter B" which is
>connected to "Tank B". The problem arises when "Tank B" is connected
>to "Meter A"
>Meter A <-> Tank A <-> MeterB <--> Tank B <-//-> Meter A
>As you can see, there is a "loop" in connectivity. The data is store
>as hierarchial data as upstream and downstream attributes. Using
>hierarchial queries, I've been able to retrieve the data in a
>hierarachial representation, but my goal is to determine if there is a
>loop in the data. Just to make it clear, its the same as the
>Manager/Employee example seen everywhere except the idea is to create a
>process to ensure that no manager reports to his employee.
>A sample dataset is provide for your much appreciated assistance.
>--DST = DownStreamType
>--DID = DownStreamID
>--UST = UpstreamType
>--UID = UpstreamID
>DROP TABLE #Conn
>CREATE TABLE #Conn (DST int, DSID int, UST int, UPID int)
>--Dataset connects with no loop
>INSERT INTO #Conn VALUES (1,1,1,2)
>INSERT INTO #Conn VALUES (1,2,1,3)
>INSERT INTO #Conn VALUES (1,3,2,1)
>INSERT INTO #Conn VALUES (2,1,2,2)
>INSERT INTO #Conn VALUES (2,2,2,3)
>INSERT INTO #Conn VALUES (2,3,3,1)
>--Dataset loops (each record is connected to each other, last
>--record connects back to first record.
>INSERT INTO #Conn VALUES (1,10,1,20)
>INSERT INTO #Conn VALUES (1,20,1,30)
>INSERT INTO #Conn VALUES (1,30,2,10)
>INSERT INTO #Conn VALUES (2,10,2,20)
>INSERT INTO #Conn VALUES (2,20,2,30)
>INSERT INTO #Conn VALUES (2,30,1,10)
>TIA
>
>|||Hi there,
Right now I have to leave for home. Will post u a solution by tomorrow
evening GMT. I have worked a lot with hierarchical tables, u surely will
enjoy the solution. bye
"carmaboy@.gmail.com" wrote:

> I've spent hours trying to find the answer to this with no luck. One
> of our systems tracks connections between objects. If a user enters an
> object that is connected to to another object that is connect to its
> parent object, we have problems.
> We have many diffent type of objects, so I will try to give a simple
> example. If we have a meter "Meter A" that is connected to a tank
> "Tank A" and that tank is connected to another meter "Meter B" which is
> connected to "Tank B". The problem arises when "Tank B" is connected
> to "Meter A"
> Meter A <-> Tank A <-> MeterB <--> Tank B <-//-> Meter A
> As you can see, there is a "loop" in connectivity. The data is store
> as hierarchial data as upstream and downstream attributes. Using
> hierarchial queries, I've been able to retrieve the data in a
> hierarachial representation, but my goal is to determine if there is a
> loop in the data. Just to make it clear, its the same as the
> Manager/Employee example seen everywhere except the idea is to create a
> process to ensure that no manager reports to his employee.
> A sample dataset is provide for your much appreciated assistance.
> --DST = DownStreamType
> --DID = DownStreamID
> --UST = UpstreamType
> --UID = UpstreamID
> DROP TABLE #Conn
> CREATE TABLE #Conn (DST int, DSID int, UST int, UPID int)
> --Dataset connects with no loop
> INSERT INTO #Conn VALUES (1,1,1,2)
> INSERT INTO #Conn VALUES (1,2,1,3)
> INSERT INTO #Conn VALUES (1,3,2,1)
> INSERT INTO #Conn VALUES (2,1,2,2)
> INSERT INTO #Conn VALUES (2,2,2,3)
> INSERT INTO #Conn VALUES (2,3,3,1)
> --Dataset loops (each record is connected to each other, last
> --record connects back to first record.
> INSERT INTO #Conn VALUES (1,10,1,20)
> INSERT INTO #Conn VALUES (1,20,1,30)
> INSERT INTO #Conn VALUES (1,30,2,10)
> INSERT INTO #Conn VALUES (2,10,2,20)
> INSERT INTO #Conn VALUES (2,20,2,30)
> INSERT INTO #Conn VALUES (2,30,1,10)
> TIA
>|||Thanks GetGoing, I look forward to your answer.
Steve, Thanks for the prompt response. Howerver, I think I did not
make myself clear. I tried your solution and the results were not what
I was looking for. The first dataset sample shows that there is a set
of object connected to each other linearly (if you will). The second
set is circular with object (1,10 - first record) as the downstream
object and again (1,10 - last record) as the upstream object. My goal
is to identify either that (1,10) is causing a loop or simply identify
that there is a loop and where. If I can state this in a different way
using the Emp/Man example, hopefully it would help. If you have 5
employees, each one reporting to the next. (Mary reports to Greg who
reports to Tom who reports to Frank who reports to Sam). I'm looking
for errors that would show (Mary reports to Greg who reports to Tom who
reports to Frank who reports to Sam who reports to Greg). Since Sam is
Greg boss 4 levels down, it incorrect/impossible that Sam should report
to Greg. The reason I need this is that some processing occurs based
on this "linear" flow. If there is a looping of the data, the process
gets stuck rather then ends. Thanks again.|||Based on the illustrations in your post I think I see the source of the
problems. The hierarchical chain is missing one vital element: context.
You say that "Meter A" is connected to "Tank A" which in turn is connected
to "MeterB" which connects to "Tank B" which then connects to "Meter A". All
these connections cannot be in the same context. If "Meter A" and "Meter B"
are gauges, then the context could be described as "Pressure", and if "Tank
A" and "Tank B" are two interconnected tanks (containers), then this is a
different context - let's call it "Containment".
If context is respected, then - logically - the situation really is:
1) the "Pressure" context:
MeterA
<-- Tank A
<-- Tank B
Meter B
<-- Tank A
<-- Tank B
2) the "Containment" context:
Tank A
<-- Tank B
or
Tank B
<-- Tank A
or even
CombinedTanks
<-- Tank A
<-- Tank B
"Mixing apples and pears makes cocktails not a trees."
-- Confucius
Maybe you should do some more reading on trees and hierarchies.
ML|||I appreciate the critique ML. Actually, what your describing is one
aspect of its possible context. What these meters and tanks are doing
is measuring liquids, specifically oil and gas. As oil/gas is pumped
from the ground, its piped to a tank. Howerver, the meter is used to
measure the amount of o&g from a specific point in the ground at
specific time intervals where as the tanks are used to measure the
amount of o&g from the ground from many points (i.e meters). My
example is a simple one, but to illustrate its complexity, here is a
real sincerio. From a well, oil and/or gas is produced which flows
though a pipepine to a tank. Meters are attached to each well to
measure the amount of product is retreived. Many wells can be attached
to a single meter as well as a meter can be attached to other meters.
The pipeline continues to the tank where it stores the o&g and is used
to measure how much has been retreived over all. Many tanks can be
attached to each other as well as the tank can be attached to another
meter before going to another tank. The direction of the production
demonstrates the use of downstream and upstream as an object (Meter,
Well, Tank, Equipment). Each item can be a downstream object or
upstream object depending on how they are connected. I hope this
clears things up a little. My appologies if you are in the O&G
industry and already know this. My intention is to make my request
clear. Thanks.|||I'll assume that the graph you represent is a digraph where the relationship
points from the downstream item to the upstream one (d-->u).
The function I provided (not thoroughly tested with this particular
implementation) traverses the graph starting with an input root, and
constructs a path for each node made of all nodes leading to the current
node.
For example, if you start traversing the graph from node 1-1, when you get
to node 3-1, the path will be:
'.1-1.1-2.1-3.2-1.2-2.2-3.3-1.'
Once you have this path available, detecting a cycle is simple--if the
source node's path already contains the target node id, you have a cycle.
Here's an implementation with a UDF with a couple of tests against your
sample data. I really didn't test it thoroughly, so I hope I didn't confuse
the references to dst, dsid, ust, usid.
-- ddl and sample data
create table conn (dst int, dsid int, ust int, usid int);
-- no cycle
insert into conn values (1,1,1,2);
insert into conn values (1,2,1,3);
insert into conn values (1,3,2,1);
insert into conn values (2,1,2,2);
insert into conn values (2,2,2,3);
insert into conn values (2,3,3,1);
-- cycle
insert into conn values (1,10,1,20);
insert into conn values (1,20,1,30);
insert into conn values (1,30,2,10);
insert into conn values (2,10,2,20);
insert into conn values (2,20,2,30);
insert into conn values (2,30,1,10);
go
create function fn_getsubgraph(@.rootdst as int, @.rootdsid as int)
returns @.subgraph table
(
dst int not null,
dsid int not null,
ust int not null,
usid int not null,
lvl int not null,
path varchar(900) not null,
cycle int not null
)
as
begin
declare @.lvl as int;
set @.lvl = 1;
insert into @.subgraph
select dst, dsid, ust, usid, @.lvl,
-- constract path of nodes leading to current node
-- in the form '.t1-id1.t2-id2. ... .tn-idn.'
'.' + cast(dst as varchar(10)) + '-'
+ cast(dsid as varchar(10)) + '.'
+ cast(ust as varchar(10)) + '-'
+ cast(usid as varchar(10)) + '.' as path,
case when dst = ust and dsid = usid then 1 else 0 end as cycle
from conn
where dst = @.rootdst and dsid = @.rootdsid;
while @.@.rowcount > 0
begin
set @.lvl = @.lvl + 1;
-- insert into @.subgraph next level
insert into @.subgraph
select u.dst, u.dsid, u.ust, u.usid, @.lvl,
d.path
+ cast(u.ust as varchar(10)) + '-'
+ cast(u.usid as varchar(10)) + '.' as path,
-- cycle is detected when source path contains target node
case when d.path like '%.' +
cast(u.ust as varchar(10)) + '-'
+ cast(u.usid as varchar(10)) + '.%'
then 1 else 0 end
from @.subgraph as d
join conn as u
on d.lvl = @.lvl - 1 -- filter prev level only
and u.dst = d.ust and u.dsid = d.usid
and d.cycle = 0; -- don't pursue cyclic paths
end
return;
end
go
select * from fn_getsubgraph(1, 1);
dst dsid ust usid lvl path cycle
-- -- -- -- -- -- --
1 1 1 2 1 .1-1.1-2. 0
1 2 1 3 2 .1-1.1-2.1-3. 0
1 3 2 1 3 .1-1.1-2.1-3.2-1. 0
2 1 2 2 4 .1-1.1-2.1-3.2-1.2-2. 0
2 2 2 3 5 .1-1.1-2.1-3.2-1.2-2.2-3. 0
2 3 3 1 6 .1-1.1-2.1-3.2-1.2-2.2-3.3-1. 0
select * from fn_getsubgraph(1, 10);
dst dsid ust usid lvl path cycle
-- -- -- -- -- -- --
1 10 1 20 1 .1-10.1-20. 0
1 20 1 30 2 .1-10.1-20.1-30. 0
1 30 2 10 3 .1-10.1-20.1-30.2-10. 0
2 10 2 20 4 .1-10.1-20.1-30.2-10.2-20. 0
2 20 2 30 5 .1-10.1-20.1-30.2-10.2-20.2-30. 0
2 30 1 10 6 .1-10.1-20.1-30.2-10.2-20.2-30.1-10. 1
BG, SQL Server MVP
www.SolidQualityLearning.com
<carmaboy@.gmail.com> wrote in message
news:1123166940.315534.214460@.g44g2000cwa.googlegroups.com...
> I've spent hours trying to find the answer to this with no luck. One
> of our systems tracks connections between objects. If a user enters an
> object that is connected to to another object that is connect to its
> parent object, we have problems.
> We have many diffent type of objects, so I will try to give a simple
> example. If we have a meter "Meter A" that is connected to a tank
> "Tank A" and that tank is connected to another meter "Meter B" which is
> connected to "Tank B". The problem arises when "Tank B" is connected
> to "Meter A"
> Meter A <-> Tank A <-> MeterB <--> Tank B <-//-> Meter A
> As you can see, there is a "loop" in connectivity. The data is store
> as hierarchial data as upstream and downstream attributes. Using
> hierarchial queries, I've been able to retrieve the data in a
> hierarachial representation, but my goal is to determine if there is a
> loop in the data. Just to make it clear, its the same as the
> Manager/Employee example seen everywhere except the idea is to create a
> process to ensure that no manager reports to his employee.
> A sample dataset is provide for your much appreciated assistance.
> --DST = DownStreamType
> --DID = DownStreamID
> --UST = UpstreamType
> --UID = UpstreamID
> DROP TABLE #Conn
> CREATE TABLE #Conn (DST int, DSID int, UST int, UPID int)
> --Dataset connects with no loop
> INSERT INTO #Conn VALUES (1,1,1,2)
> INSERT INTO #Conn VALUES (1,2,1,3)
> INSERT INTO #Conn VALUES (1,3,2,1)
> INSERT INTO #Conn VALUES (2,1,2,2)
> INSERT INTO #Conn VALUES (2,2,2,3)
> INSERT INTO #Conn VALUES (2,3,3,1)
> --Dataset loops (each record is connected to each other, last
> --record connects back to first record.
> INSERT INTO #Conn VALUES (1,10,1,20)
> INSERT INTO #Conn VALUES (1,20,1,30)
> INSERT INTO #Conn VALUES (1,30,2,10)
> INSERT INTO #Conn VALUES (2,10,2,20)
> INSERT INTO #Conn VALUES (2,20,2,30)
> INSERT INTO #Conn VALUES (2,30,1,10)
> TIA
>|||There are many different ways to present the results of
loop detection. If what I provided is not "what [you were]
looking for", can you post the result set you want?
My code gives this result:
DST DSID UST UPID
-- -- -- --
1 10 1 20
1 20 1 30
1 30 2 10
2 10 2 20
2 20 2 30
2 30 1 10
You say your goal is "is to identify either that (1,10)
is causing a loop or simply identify that there is a loop
and where."
I've given you a list of connections that participate in a loop. It
doesn't make sense to say that (1,10) is the specific cause, since
nothing distinguishes (1,10) from any of the other nodes in the
loop. Loops have no beginning or end, and one could just as well
say that there is a loop that starts at (2,20) and ends at (2,20),
where the loop is
(2, 20); (2,30); (1,10); (1,20); (1,30); (2,10); (2,20)
If you want to know "where" the loop is, how would such a
question be answered, if not by listing the parts of the loop,
since loops have no distinguished location beyond their being
a set of nodes and edges.
You can list the nodes instead of the edges using my example
with this final query:
select DST as T, DSID as ID
from #remnants
union
select UST, UPID
from #remnants
The result would then be
T ID
-- --
1 10
1 20
1 30
2 10
2 20
2 30
Is that any better? It doesn't give as much information about
how the loop connects these nodes, but it is a list of nodes instead
of a list of connections.
SK
carmaboy@.gmail.com wrote:

>Thanks GetGoing, I look forward to your answer.
>Steve, Thanks for the prompt response. Howerver, I think I did not
>make myself clear. I tried your solution and the results were not what
>I was looking for. The first dataset sample shows that there is a set
>of object connected to each other linearly (if you will). The second
>set is circular with object (1,10 - first record) as the downstream
>object and again (1,10 - last record) as the upstream object. My goal
>is to identify either that (1,10) is causing a loop or simply identify
>that there is a loop and where. If I can state this in a different way
>using the Emp/Man example, hopefully it would help. If you have 5
>employees, each one reporting to the next. (Mary reports to Greg who
>reports to Tom who reports to Frank who reports to Sam). I'm looking
>for errors that would show (Mary reports to Greg who reports to Tom who
>reports to Frank who reports to Sam who reports to Greg). Since Sam is
>Greg boss 4 levels down, it incorrect/impossible that Sam should report
>to Greg. The reason I need this is that some processing occurs based
>on this "linear" flow. If there is a looping of the data, the process
>gets stuck rather then ends. Thanks again.
>
>|||Thanks very much for everyones posts. I think I am well on my way to
getting my results.
SK, thanks again. You make a great point about what creates the loop
and how to identify it. I didn't not include that sincerio in my
thought, but have now. Your help is greatly appeciated.|||As far as I can see your model is based on a doubly-linked list. The positiv
e
thing about it is the fact that it's fairly easy to find connected items, ye
t
it can lead to circular references.
I'd suggest designing the model based on a singly-linked list. This way
circular references can be avoided quite easily.
Maybe something like this:
EquipmentPiece : EquipmentInstance : ConnectedToInstance : Direction :
ConnectionType
If redesigning is out of the question, I believe Itzik has a solution that
will help you detect circular references.
ML

Hierarchy

Hi to All!

Is there a perfect method to implement a hierarchy structure with different types as a table in Sql server 2005? Currently I am thinking of this way:

[Node | ParentId | ParentType | ChildId | ChildType]

But there is this nagging little voice saying it can be better

Cheers!

Nele

? Can you share some additional information? What do the "types" represent? Most of the time when I see the need for typing, it means that a table for that type should be created, and through key relationships this forms a natural hierarchy. In your system can the type hierarchy change? -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Eburon@.discussions.microsoft.com> wrote in message news:f4c76476-0e56-4c53-939b-ffb7750ab228@.discussions.microsoft.com... Hi to All! Is there a perfect method to implement a hierarchy structure with different types as a table in Sql server 2005? Currently I am thinking of this way: [Node | ParentId | ParentType | ChildId | ChildType] But there is this nagging little voice saying it can be better Cheers! Nele|||

[same author as Eburon]

It is indeed a natural hierarchy. I assume that the type hierarchy cannot change, but a type can be absent. More in detail there are three types, t1, t2, t3:

t1 is always the root [level 1],|||? I would model this as three tables. T2 and T3 would both have self-referencing keys (i.e., "parent" keys)... CREATE TABLE T1 ( T1Id INT NOT NULL UNIQUE, ... //other attributes, including natural PK ) GO CREATE TABLE T2 ( T1id INT NULL REFERENCES T1 (T1id), T2id INT NOT NULL UNIQUE, parentT2id INT NULL REFERENCES T2 (T2id), ...//other attributes, PK, etc CONSTRAINT CheckIDsNOTNULL CHECK (COALESCE(T1id, T2id) IS NOT NULL) ) GO CREATE TABLE T3 ( T2id INT NULL REFERENCES T3 (T13id), T3id INT NOT NULL UNIQUE, parentT3id INT NULL REFERENCES T2 (T3id), ...//other attributes, PK, etc CONSTRAINT CheckIDsNOTNULL CHECK (COALESCE(T2id, T3id) IS NOT NULL) ) GO -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Nele@.discussions.microsoft..com> wrote in message news:27029b28-63cc-419f-8340-5be221c78682@.discussions.microsoft.com... [same author as Eburon] It is indeed a natural hierarchy. I assume that the type hierarchy cannot change, but a type can be absent. More in detail there are three types, t1, t2, t3: t1 is always the root [level 1], t2 may be added to go lower down the hierarchy [level 1.1 -> level 1 ... 1], and t3 may be added as a child [level 1 ... 1.1]. Is that sufficient additional information?|||I had not thought of that solution. Thank you!

HIERARCHY

i have a problem.. which is the procedure to establish the hierarchies in my dimension.. the dimension wizard sends a message that the relationships were checked but did not create any hierarchies?

is there any propertie i should checke before running the wizard?

The cube and dimension wizards will attempt to create hierarchies based on relationships between tables and relationships found when sampling data. However, it may not discover many relationships, especially when the amount of data in the table is small. You can manually create hierarchies in the dimension editor by dragging attributes onto the "Hierarchies and Levels" pane.

Friday, March 9, 2012

hierarchy structure

Hierarchy Problem after processing - Help!

Hierarchy Problem

Hierarchy Parent-Child

Hierarchy Parent-Child

Hierarchy ORBER BY

Hierarchy Members

Hierarchy Member MDX Name

Hierarchy Member MDX Name

Hierarchy MDX

Hierarchy is used more than once in the Crossjoin function

hierarchy in SSRS 2005?

Hierarchy Equivalent of a Crossjoin

Hierarchy Data in XML

Hierarchy - Measure question

Wednesday, March 7, 2012

Hierarchy - Measure question

Hierarchy - Measure question

Hierarchy - Finding loops in the data

Hierarchy

HIERARCHY

Hiding a Subreport

Blog Archive

About Me