Parent-Child-Dimension in SQL Srv 2005 Analysis Services

Hi experts,

having a parent-child-table with the columns child_id, child_name, parent_id
in SQL Server 2005 I just cannot create a parent-child dimension in BI Dev
Studio. Can anyone give me some hints? The Dim Build wizard doesn't create
the hierarchies, manually setting "parent" property to parent_id and "key"
to child_id as well as dragging and dropping the stuff into the hierachy
field haven't just led to success. I also tried to right-click both
parent_id and child_id to create a member property. It just never worked
out.

Any help would be greatly appreciated.

Kind regards,

JoergI realized that there is a special NG for this subject:
http://communities.microsoft.com/ne...er2005&slcid=us

Regards,
Joerg

Parent-Child View without using Cursors

I would like to create a View (we'll call it FamilyView) using two tables, that I can then query against.
For example:

Parent
{
ID_PK,
Name,
PhoneNum,
Address
}

Child
{
ID_PK,
ParentID_FK,
Name
}

The view would return a dataset like this:

Parent.Name, Parent.PhoneNum, Parent.Address, Child.Name1, Child.Name2, Child.Name3... Child.NameN

William Smith, (555)555-5555, 123 Main Street, Susie, Peter, Bill Jr, Fred
Jason Jones, (666)666-6666, 54332 South Ave, Brian, Steven
Kay McPeak, (777)777-7777, 9876 Division NW, Kathy, Sally, Karen, Deb, Becky, Kendra, Ann, Edward

with an unknown number of children for each parent.

Then I would like to be able to query against this view with something like this:

SELECT * FROM FamilyView Where Child2 = 'Peter'

I have no idea how to write the SQL for this View. Is it possible?
Is this possible without using a cursor?

Thanks for any advice you all can give me.
BrianWhat Version of SQL Server?

If it's 2005, you can use CTE (Common Table Expreassions)

If it's 2000, you probably need to use a udf that returns a table|||It's SQL Server 2000.

Could you give me an example of how a UDF would be used to solve this please?|||So you want to find where the family tree for a child somewhere in the middle?|||I want to display each "family" in a single row in a result set and then be able to filter those families where the second child listed is 'Peter' (for example) and view only the families where Peter is the name of the second child. Does that make sense?

My real problem is a little more complex, but I thought that if I used this example it would eliminate a lot of explanation of the problem domain.|||Here you go, either a sproc, or a udf for set based stuff

CREATE TABLE Parent (
ID_PK int IDENTITY(1,1)
, [Name] varchar(20)
, PhoneNum varchar(20)
, Address varchar(30))

CREATE TABLE Child (
ID_PK int
, ParentID_FK int)
GO

INSERT INTO Parent([Name],PhoneNum, Address)
SELECT 'Annie', '111-111-1111', '1st Street' UNION ALL
SELECT 'Bob', '222-222-2222', '2nd Street' UNION ALL
SELECT 'Cathy', '333-333-3333', '3rd Street' UNION ALL
SELECT 'Don', '444-444-4444', '4th Street' UNION ALL
SELECT 'Emily', '555-555-5555', '5th Street' UNION ALL
SELECT 'Frank', '666-666-6666', '6th Street' UNION ALL
SELECT 'Georgette', '777-777-7777', '7th Street' UNION ALL
SELECT 'Harry', '888-888-8888', '8th Street'

INSERT INTO Child(ID_PK, ParentID_FK)
SELECT 1, null UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 2 UNION ALL
SELECT 4, 3 UNION ALL
SELECT 5, null UNION ALL
SELECT 6, 5 UNION ALL
SELECT 7, 6 UNION ALL
SELECT 8, 7
GO

SELECT * FROM Parent p LEFT JOIN Child c ON p.ID_PK = c.ID_PK
GO

CREATE FUNCTION udf_FindTree (@.Child varchar(20))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @.p int, @.p_save int, @.rs varchar(8000)
SELECT @.p = 0, @.p_save = 0
SELECT @.p = ParentID_FK FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
WHERE [Name] = @.Child
--Loop Until @.@.rowcount = 0
WHILE Exists (SELECT ParentID_FK FROM Child c WHERE ID_PK = @.p)
BEGIN
SELECT @.p_save = @.p
SELECT @.p = ParentID_FK FROM Child c WHERE ID_PK = @.p_save
-- The Last assignement is the top Parent
END
--Now Walk from the top Down until @.@.rowcount = 0
SELECT @.p = @.p_save
SELECT @.rs = [Name] + ' ' + PhoneNum + ' ' + Address FROM Parent WHERE ID_PK = @.p
WHILE EXISTS (SELECT ID_PK FROM Child WHERE ParentID_FK = @.p)
BEGIN
SELECT @.rs = @.rs + ' ' + COALESCE([Name],'') FROM Parent WHERE ID_PK = @.p
SELECT @.p = ID_PK FROM Child WHERE ParentID_FK = @.p
END
RETURN @.rs
END
GO

SELECT dbo.udf_FindTree('Cathy')
GO

SELECT * FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
WHERE [Name] = 'Cathy'
GO

CREATE PROC usp_FindTree @.Child varchar(20)
AS
SET NOCOUNT ON
DECLARE @.p int, @.p_save int, @.rs varchar(8000)
SELECT @.p = 0, @.p_save = 0
SELECT @.p = ParentID_FK FROM Child c JOIN Parent p ON c.ParentID_FK = p.ID_PK
WHERE [Name] = @.Child
--Loop Until @.@.rowcount = 0
WHILE Exists (SELECT ParentID_FK FROM Child c WHERE ID_PK = @.p)
BEGIN
SELECT @.p_save = @.p
SELECT @.p = ParentID_FK FROM Child c WHERE ID_PK = @.p_save
-- The Last assignement is the top Parent
END
--Now Walk from the top Down until @.@.rowcount = 0
SELECT @.p = @.p_save
SELECT @.rs = [Name] + ' ' + PhoneNum + ' ' + Address FROM Parent WHERE ID_PK = @.p
WHILE EXISTS (SELECT ID_PK FROM Child WHERE ParentID_FK = @.p)
BEGIN
SELECT @.rs = @.rs + ' ' + COALESCE([Name],'') FROM Parent WHERE ID_PK = @.p
SELECT @.p = ID_PK FROM Child WHERE ParentID_FK = @.p
END
SELECT @.rs AS rs
SET NOCOUNT OFF
GO

EXEC usp_FindTree 'Cathy'
GO

DROP PROC usp_FindTree
DROP Function udf_FindTree
DROP TABLE Parent, Child
GO|||You could even do

SELECT DISTINCT dbo.udf_FindTree([name]) FROM Parent
GO|||Your work here has actually taught me quite a bit about UDFs and I appreciate that very much, Thank you!

But what I'm looking for is something closer to what this SQL generates.

USE Northwind
GO

SELECT OrderID,
coalesce(MAX(CASE OD.rowno WHEN 1 THEN P.ProductName END), '') AS Product1,
coalesce(MAX(CASE OD.rowno WHEN 2 THEN P.ProductName END), '') AS Product2,
coalesce(MAX(CASE OD.rowno WHEN 3 THEN P.ProductName END), '') AS Product3,
coalesce(MAX(CASE OD.rowno WHEN 4 THEN P.ProductName END), '') AS Product4,
coalesce(MAX(CASE OD.rowno WHEN 5 THEN P.ProductName END), '') AS Product5,
coalesce(MAX(CASE OD.rowno WHEN 6 THEN P.ProductName END), '') AS Product6,
coalesce(MAX(CASE OD.rowno WHEN 7 THEN P.ProductName END), '') AS Product7
FROM (SELECT a.OrderID, a.ProductID,
rowno = (SELECT COUNT(*)
FROM [Order Details] b
WHERE b.OrderID = a.OrderID
AND b.ProductID <= a.ProductID)
FROM [Order Details] a) AS OD
JOIN Products P ON P.ProductID = OD.ProductID
GROUP BY OD.OrderID
ORDER BY OD.OrderID

Use Northwind database and assume [Order Details] as parent and [Products] as the child. See how all the data between the two tables are displayed in one row (but separate NAMED columns: Product1, Product2, ... etc.)? That's what I'm looking for. If I could write this code into a View (Which I can't) then I could query against the returned dataset like this.

SELECT * FROM OrderProductView WHERE Product1 = 'Chang'

and I would get all the same columns, but only including the rows with OrderIDs: {10255, 10258, 10264, etc}.

The problem with the above code is that I HAVE to know the number of "child" (Product) elements expected per order at design time. Also, the CASE construct is not valid in a View.|||So you don't care about a tree, just a key and all it's attributive rows?

Maybe something like

http://weblogs.sqlteam.com/brettk/archive/2005/02/23/4171.aspx|||Yes! This appears to be exactly what I've been looking for. Thank you, thank you, thank you.
I was beginning to think this could only be executed in code outside the SQL.
I need to play with this a little to fully understand it all, but I think this will give me the results I need.
Thank you Brett for your patience and all your help!|||Just cut and paste the code example to see how it works

Good Luck

...oh, and you can buy me a margarita and we'll call it even|||Next time I'm in the Jersey area I might do just that. I really appreciate it.
And if you're ever in Grand Rapids...|||One more question...
My query is now too big to store in a local variable... I've managed to write the generated query to a file. Is there anyway I can execute this query from a text file?

Parent-child variable issues that may impact deployment

We are using SSIS for the first time. My team is working on a project that involves putting a date time stamp into a series of tables. These tables are being assembled in a series of child packages being executed by the parent. When the parent runs, we evaluate our timestamp variable as a GETDATE() expression and pass it to the children to be included as a derived column. We don't want the actual runtime of each step to be the timestamp, just the start of the batch (parent).

In order to get the variable to pass over to the child, we needed to set the package location to "file system"instead of "SQL Server". It seems unusual that this would be so. Are we doing something wrong?

What implications does this have for deployment? Will we need to customize the packages for each instance we plan to run this on? Can you have a parent run a child package on a different instance? This would be a performance plus since we have really huge source databases and would like to distribute the processing.

Hmmm, my boss just told me to scratch the whole idea of parent-child and go with a control table to store the variable for all the packages to access. Oh well, I'm still interested in why this is so cumbersome when really its just passing a parameter from one procedure to another.

Oh, and I think you could use a spellchecker on this message box. At least I could use one.

In the child package, create a variable to hold the parent variable. Same name is fine. Then in the control flow, right click on the background and select "package configurations".

Enable package configurations. Then add a new one. Change the configuration type to "parent package variable." Then, in the specify configuration settings entry, enter the name of the variable in the parent package. Next, on the following screen, select the "value" property of the variable created in your child package. (Expand the child variable until you can select the value property.) Click next. Give the configuration a name and hit finish. Done.

Phil|||

Phil,

Thanks, but we already have that functionality. The problem is that we don't like it. We want something more robust, sort of like passing a parameter from one function to another. And we can't even pass a value parameter with this thingy. This parent-child functionality is just plain ugly. We are using a control table instead, but we don't like doing that either.

Parent-Child relationship

I have a field which is the primary key in table A, and in table B i have th
e
same field which is the foreign key.When it comes to deleting a record I
should kill the child first.If i try to delete the parent then i get an
error, which is expected. But if i turn the delete cascade option on, it let
s
me delete the parent first without giving an error.Why?
Thanks in advance.On Tue, 15 Feb 2005 08:21:11 -0800, PH wrote:

>I have a field which is the primary key in table A, and in table B i have t
he
>same field which is the foreign key.When it comes to deleting a record I
>should kill the child first.If i try to delete the parent then i get an
>error, which is expected. But if i turn the delete cascade option on, it le
ts
>me delete the parent first without giving an error.Why?
>Thanks in advance.
Hi PH,
Because the on delete cascade options tells the SQL Server engine that it
should automatically delete all "orphaned" childs when you delete a
parent. After SQL Server has done that, there are no more rows violating
the foreign key constraint, so there's no reason to give you an error.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||What did you think that cascade would do? The error is not there because
it deleted the child first, then deleted the parent.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"PH" <PH@.discussions.microsoft.com> wrote in message
news:AE79F794-3984-4DE6-949B-C395B817E144@.microsoft.com...
>I have a field which is the primary key in table A, and in table B i have
>the
> same field which is the foreign key.When it comes to deleting a record I
> should kill the child first.If i try to delete the parent then i get an
> error, which is expected. But if i turn the delete cascade option on, it
> lets
> me delete the parent first without giving an error.Why?
> Thanks in advance.|||Only use cascade delete when you are always sure that it never makes sense
to have a child when the parent is deleted. There are cases where it makes
sense for a child to have a null in the FK column, usually this is when
there are multiple Fk's in the child table, BUT, the is usually always ONE
Master table, as in Invoice and InvoiceItem when you must decide to either
cascade delete the InvoiceItem when and Invoice is deleted or to DENY
deleting and Invoice if it has InvoiceItems. This is a business rule that
you must decide on.
JIM
"PH" <PH@.discussions.microsoft.com> wrote in message
news:AE79F794-3984-4DE6-949B-C395B817E144@.microsoft.com...
>I have a field which is the primary key in table A, and in table B i have
>the
> same field which is the foreign key.When it comes to deleting a record I
> should kill the child first.If i try to delete the parent then i get an
> error, which is expected. But if i turn the delete cascade option on, it
> lets
> me delete the parent first without giving an error.Why?
> Thanks in advance.|||Thanks for your answers
"james" wrote:

> Only use cascade delete when you are always sure that it never makes sense
> to have a child when the parent is deleted. There are cases where it make
s
> sense for a child to have a null in the FK column, usually this is when
> there are multiple Fk's in the child table, BUT, the is usually always ONE
> Master table, as in Invoice and InvoiceItem when you must decide to either
> cascade delete the InvoiceItem when and Invoice is deleted or to DENY
> deleting and Invoice if it has InvoiceItems. This is a business rule that
> you must decide on.
> JIM
> "PH" <PH@.discussions.microsoft.com> wrote in message
> news:AE79F794-3984-4DE6-949B-C395B817E144@.microsoft.com...
>
>

parent-child question

Hi Folks
This one is driving me a little 'bonky'.
I have a (part - PRT_PRT) parent-child (part meta - PRT_MET) relationship
defined in the DB:
CREATE TABLE [dbo].[PRT_MET] (
[MetID] [decimal](10, 0) IDENTITY (1, 1) NOT NULL ,
[IsActive] [decimal](1, 0) NOT NULL ,
[MetaName] [varchar] (50) NULL ,
[MetaDesc] [varchar] (750) NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[PRT_PRT] (
[PrtID] [decimal](10, 0) IDENTITY (1, 1) NOT NULL ,
[RefID] [decimal](10, 0) NULL ,
[IsActive] [decimal](1, 0) NOT NULL ,
[MetID] [decimal](10, 0) NOT NULL ,
[PartName] [varchar] (50) NOT NULL ,
[PartDesc] [varchar] (750) NULL ,
[VersionNo] [smallint] NOT NULL
) ON [PRIMARY]
Here's my schema:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship
name="PRT_PRT2PRT_MET"
parent="PRT_PRT"
parent-key="MetID"
child="PRT_MET"
child-key="MetID"
inverse="true" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="PRT_PRT">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="PrtId" type="xsd:decimal" />
<xsd:element name="RefId" type="xsd:decimal" />
<xsd:element name="IsActive" type="xsd:decimal" />
<xsd:element name="MetID" type="xsd:decimal" />
<xsd:element name="PartName" type="xsd:string" />
<xsd:element name="PartDesc" type="xsd:string" />
<xsd:element name="VersionNo" type="xsd:short" />
<xsd:element sql:relation="PRT_MET" sql:relationship="PRT_PRT2PRT_MET"
name="PartMeta">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="MetID" type="xsd:decimal" />
<xsd:element name="IsActive" type="xsd:decimal" />
<xsd:element name="MetaName" type="xsd:string" />
<xsd:element name="MetaDesc" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Here's my XML test file:
<ROOT>
<PRT_PRT>
<PrtId></PrtId>
<RefID>0</RefID>
<IsActive>1</IsActive>
<MetID></MetID>
<PartName>A-B-C</PartName>
<PartDesc>(Eng Assy - 3.5L 4V)</PartDesc>
<VersionNo>0</VersionNo>
<PRT_MET>
<MetID></MetID>
<IsActive>1</IsActive>
<MetaName>PartB</MetaName>
<MetaDesc>B</MetaDesc>
</PRT_MET>
</PRT_PRT>
</ROOT>
My .vbs code:
dim today
today = right(year(today),4) & "_" & right(month(today)+ 100,2) & "_" &
right(day(today)+100,2) & "_" & Right(datepart("h",today)+100, 2) &
Right(datepart("n",today)+100, 2)& Right(datepart("s",today)+100, 2)
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
objBL.ConnectionString =
"Provider=sqloledb;server=Karatzas14\PS;database=T ICv1;User
Id=myid;Password=mypwd"
objBL.ErrorLogFile = "C:\psplm NEW\TIC\FileExists\XMLSQL\errors" & today &
".log"
objBL.KeepNulls=True
objBL.KeepIdentity=False
'objBL.IgnoreDuplicateKeys=True
objBL.Execute "PRT_PRT2PRT_METv2.xsd", "test.xml"
set objBL=Nothing
Problem is that I need to update the 'MetID' field in PRT_PRT from the
PRT_MET insert (which uses an identity). Right now it's failing because it
needs to have a nonnull value in MetID (for PRT_PRT).
Again, thanks
tried everything I could dream up...
Rob
Let me understand this correct..
PRT_MET is a child table which generates the MetID using a auto identity and
this has to be propagated to MetID field in PRT_PRT which is the parent?
If this is the case, bulkload does cannot do this.
it can be other way around, prt-prt to prt-met.
HTH,
Chandra
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"RobKaratzas" <RobKaratzas@.discussions.microsoft.com> wrote in message
news:CB51AAE3-4A50-44E0-AAF9-AB843CE2E319@.microsoft.com...
> Hi Folks
> This one is driving me a little 'bonky'.
> I have a (part - PRT_PRT) parent-child (part meta - PRT_MET) relationship
> defined in the DB:
> CREATE TABLE [dbo].[PRT_MET] (
> [MetID] [decimal](10, 0) IDENTITY (1, 1) NOT NULL ,
> [IsActive] [decimal](1, 0) NOT NULL ,
> [MetaName] [varchar] (50) NULL ,
> [MetaDesc] [varchar] (750) NULL
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[PRT_PRT] (
> [PrtID] [decimal](10, 0) IDENTITY (1, 1) NOT NULL ,
> [RefID] [decimal](10, 0) NULL ,
> [IsActive] [decimal](1, 0) NOT NULL ,
> [MetID] [decimal](10, 0) NOT NULL ,
> [PartName] [varchar] (50) NOT NULL ,
> [PartDesc] [varchar] (750) NULL ,
> [VersionNo] [smallint] NOT NULL
> ) ON [PRIMARY]
> Here's my schema:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship
> name="PRT_PRT2PRT_MET"
> parent="PRT_PRT"
> parent-key="MetID"
> child="PRT_MET"
> child-key="MetID"
> inverse="true" />
> </xsd:appinfo>
> </xsd:annotation>
> <xsd:element name="PRT_PRT">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="PrtId" type="xsd:decimal" />
> <xsd:element name="RefId" type="xsd:decimal" />
> <xsd:element name="IsActive" type="xsd:decimal" />
> <xsd:element name="MetID" type="xsd:decimal" />
> <xsd:element name="PartName" type="xsd:string" />
> <xsd:element name="PartDesc" type="xsd:string" />
> <xsd:element name="VersionNo" type="xsd:short" />
> <xsd:element sql:relation="PRT_MET" sql:relationship="PRT_PRT2PRT_MET"
> name="PartMeta">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="MetID" type="xsd:decimal" />
> <xsd:element name="IsActive" type="xsd:decimal" />
> <xsd:element name="MetaName" type="xsd:string" />
> <xsd:element name="MetaDesc" type="xsd:string" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> Here's my XML test file:
> <ROOT>
> <PRT_PRT>
> <PrtId></PrtId>
> <RefID>0</RefID>
> <IsActive>1</IsActive>
> <MetID></MetID>
> <PartName>A-B-C</PartName>
> <PartDesc>(Eng Assy - 3.5L 4V)</PartDesc>
> <VersionNo>0</VersionNo>
> <PRT_MET>
> <MetID></MetID>
> <IsActive>1</IsActive>
> <MetaName>PartB</MetaName>
> <MetaDesc>B</MetaDesc>
> </PRT_MET>
> </PRT_PRT>
> </ROOT>
> My .vbs code:
> dim today
> today = right(year(today),4) & "_" & right(month(today)+ 100,2) & "_" &
> right(day(today)+100,2) & "_" & Right(datepart("h",today)+100, 2) &
> Right(datepart("n",today)+100, 2)& Right(datepart("s",today)+100, 2)
> set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
> objBL.ConnectionString =
> "Provider=sqloledb;server=Karatzas14\PS;database=T ICv1;User
> Id=myid;Password=mypwd"
> objBL.ErrorLogFile = "C:\psplm NEW\TIC\FileExists\XMLSQL\errors" & today &
> ".log"
> objBL.KeepNulls=True
> objBL.KeepIdentity=False
> 'objBL.IgnoreDuplicateKeys=True
> objBL.Execute "PRT_PRT2PRT_METv2.xsd", "test.xml"
> set objBL=Nothing
> Problem is that I need to update the 'MetID' field in PRT_PRT from the
> PRT_MET insert (which uses an identity). Right now it's failing because it
> needs to have a nonnull value in MetID (for PRT_PRT).
> Again, thanks
> tried everything I could dream up...
> Rob
|||unfortunately, that is the case...
(except I need to go many more levels deep in other load scenarios)
Is there anyway to do a look-up?
Thanks Rob
"Chandra Kalyanaraman [MSFT]" wrote:

> Let me understand this correct..
> PRT_MET is a child table which generates the MetID using a auto identity and
> this has to be propagated to MetID field in PRT_PRT which is the parent?
> If this is the case, bulkload does cannot do this.
> it can be other way around, prt-prt to prt-met.
> HTH,
> Chandra
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "RobKaratzas" <RobKaratzas@.discussions.microsoft.com> wrote in message
> news:CB51AAE3-4A50-44E0-AAF9-AB843CE2E319@.microsoft.com...
>
>
|||maybe another approach might offer a solution...
is there any reason why I can't load from the 'bottom-up'? (having the
schema define that PRT_MET is the parent and that PRT_PRT is the child? I
have control of the XML stream generation.)
for my load case here, there's always only a 1-to-1 relationship being
created.
(this would be like 1 order detail row to 1 order master)
Rob
"Chandra Kalyanaraman [MSFT]" wrote:

> Let me understand this correct..
> PRT_MET is a child table which generates the MetID using a auto identity and
> this has to be propagated to MetID field in PRT_PRT which is the parent?
> If this is the case, bulkload does cannot do this.
> it can be other way around, prt-prt to prt-met.
> HTH,
> Chandra
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "RobKaratzas" <RobKaratzas@.discussions.microsoft.com> wrote in message
> news:CB51AAE3-4A50-44E0-AAF9-AB843CE2E319@.microsoft.com...
>
>
|||hmmm...
this approach, actually does work (except for MetID not getting updated in
PRT_PRT).
Here's the changes:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship
name="PRT_MET2PRT_PRT"
parent="PRT_MET"
parent-key="MetID"
child="PRT_PRT"
child-key="MetID"
inverse="true" />
</xsd:appinfo>
</xsd:annotation>
<xsd:element name="PRT_MET">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="MetID" type="xsd:decimal" />
<xsd:element name="IsActive" type="xsd:decimal" />
<xsd:element name="MetaName" type="xsd:string" />
<xsd:element name="MetaDesc" type="xsd:string" />
<xsd:element sql:relation="PRT_PRT" sql:relationship="PRT_MET2PRT_PRT"
name="PRT_PRT">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="PrtID" type="xsd:decimal" />
<xsd:element name="RefID" type="xsd:decimal" />
<xsd:element name="IsActive" type="xsd:decimal" />
<xsd:element name="MetID" type="xsd:decimal" />
<xsd:element name="PartName" type="xsd:string" />
<xsd:element name="PartDesc" type="xsd:string" />
<xsd:element name="VersionNo" type="xsd:short" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
XML:
<ROOT>
<PRT_MET>
<MetID></MetID>
<IsActive>1</IsActive>
<MetaName>PartBody</MetaName>
<MetaDesc>228</MetaDesc>
<PRT_PRT>
<PrtId></PrtId>
<RefID>0</RefID>
<IsActive>1</IsActive>
<MetID></MetID>
<PartName>7G-228-AA</PartName>
<PartDesc>(Eng Assy - 3.5L 4V)</PartDesc>
<VersionNo>0</VersionNo>
</PRT_PRT>
</PRT_MET>
</ROOT>
anyway known way to get MetID updated (in PRT_PRT). any such thing as a
lookup, variable in .xsd (similar to XSL), etc.?
keeping fingers crossed...
Rob
"RobKaratzas" wrote:
[vbcol=seagreen]
> maybe another approach might offer a solution...
> is there any reason why I can't load from the 'bottom-up'? (having the
> schema define that PRT_MET is the parent and that PRT_PRT is the child? I
> have control of the XML stream generation.)
> for my load case here, there's always only a 1-to-1 relationship being
> created.
> (this would be like 1 order detail row to 1 order master)
> Rob
> "Chandra Kalyanaraman [MSFT]" wrote:
|||it sure looks like the 'D. Bulk loading in identity type columns' sample in
the docs is doing something closely similar (but in my case, I need to
populate more than the key values into a table).
thanks, so much
rob
"Chandra Kalyanaraman [MSFT]" wrote:

> Let me understand this correct..
> PRT_MET is a child table which generates the MetID using a auto identity and
> this has to be propagated to MetID field in PRT_PRT which is the parent?
> If this is the case, bulkload does cannot do this.
> it can be other way around, prt-prt to prt-met.
> HTH,
> Chandra
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "RobKaratzas" <RobKaratzas@.discussions.microsoft.com> wrote in message
> news:CB51AAE3-4A50-44E0-AAF9-AB843CE2E319@.microsoft.com...
>
>

Parent-Child Package Configurations

Hi All,

May be I am doing something wrong over here, but I have been trying in vain to test out a simple scenario where I can use my Parent Package configurations in my Child package. I have two packages ready and can someone please walk me through this process. Appreciate all help

Thanks

Have your read the topic in Books Online?
Are you getting errors or it just seems to do nothing?
Basic flow to keep in mind is the Child 'pulls' variables\values from the parent. package. So the parent knows nothing, and the 'configuration' is done in the child package.
I know one tricky thing is you need to get the case correct when you type the name of the 'Parent Variable' in the configuration.

|||

Hi

I am having problems assigning connection value to my variable, I have defined a variable in the child package, defined another variable in the parent package with the same name, however I am having problem when I am assinging this variable the value for the connection, I am not sure if I am following the procedure properly on top of that there is no proper documentation on MSDN on how to assign value to this variable.

Thanks

|||

Hi ,

Can someone point me to some documentation or an article that explains the procedure?

Thanks

|||

So it sounds like your issue is using a variable with a connection manager and not really a parent-child configuration itself?

In general you would use the property expressions feature for something dynmic like this. To build a connection string or the fily or even say a dynamic subject of an email pushed out by the send mail task.

I suggest reading the topic

"Using Property Expressions in Packages " overall and in particular there is a subtopic of "Property Expression for the ConnectionString Property of a Flat File Connection Manager"

There have been several updates to Books Online since SQL 2005 shipped, I have the latest and I cannot recall how much these topics have changed since release.

http://www.microsoft.com/downloads/details.aspx?familyid=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en

Parent-Child Hierarchy but reversed(!)

Hi!

I have a report that uses a parent-child hierarchy in a table. It is hidden with the toggle set to itself, and that works perfectly.

However, instead of having it look like this(simple example):

+ Profit

After the + has been clicked:

-Profit
+Sales
+Cost

I would like it to look like this:

+Sales
+Cost
-Profit

Is this possible with when using a parent-child hierarchy?

http://ssasfreak.spaces.live.com/

Have you tried using the group footer row instead of the group header row to control visibility?|||

Yes. That only works when you have the diffrent levels in different fields. Like this:

Level1 Level2 Level2

I use the parentgroup property(for the grouping) with a recordset that looks like this:

LevelID LevelName ParentLevelID

Any ideas?

Parent-child hierarchy & fact data?

I'm new to SSAS & would really appreciate any help!

we have an Employee dimension that contains oranizational structure & has parent-child relationship (employee_id as primary surrogate key & parent_id self referencing employee_id) as Employees hierarchy.

there's a number of linked fact tables that link back to dimEmployee.

here's the problem - when I use MS excel & pivot table & link employee id or name or any attributte of the dimEmployee to my fact tables everything is good, but when I use Employees hierarchy (so that it displays the tree on the left etc) then the linking isn't happening & instead I see only global total for each line.

how can I fix this?!

Thanks!

I'd like to know it too...

I had similar case where I had to add additional column for an operator (+/-) & use accounting intelligence & it did the trick for me.

Parent-Child Hierarchies & User-defined hierarchies not appearing in Report Builder

Hi "Team SSAS",

I have this weird situation where Parent-Child hierarchies, and User-defined hierarchies show up in Visual Studio, but once I'vedeployed/processed the solution, they do not appear in Report Builder.

Is this a known product bug, or has someone else encountered this and have a solution, PLEASE?

Thanks in advance and in anticipation

Gregg Withers

gwithers atsymbol jhancock dotsymbol com

Hi Greg,

Report Builder can't handle parent/child hierarchies at all; with user-defined hierarchies it just shows the underlying attributes that make up each level if they themselves are visible. I know, it's a bit rubbish...

Take a look at this white paper for more information:

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

HTH,

Chris

|||

Chris,

Thanks for your hyperlink to the intro to report models.

I'm having a tough time swallowing that answer - It just doesn't make sense that parent-child hierarchies are not available for ad-hoc reporting. I can't think of any decent-sized organization that does not need an parent-child type org chart. Also, if your answer also pertains to user-defined hierarchies, I have the same response.

Anyone else run into this problem, please?

TIA

Gregg

Parent-child dimension related to fact table

is it possible to use a "regular relation" to connect fact table to a
parent-child dimension?
thanksOoops! I'm using Analysis Services 2005 :-)

Parent-Child Dimension in a Report

Hi,
I saw some threads about using parent-child dimensions in a reporting
services report on an OLAP cube in this newsgroup. I have the same problem
that I didn't get the nesting, I want, because reporting services didn't
render it. I tried with the UniqueName and ParentUniqueName properties, but
I only get a hierarchy of Level2 and no deeper. Does anyone knows a solution
in the meantime?
Thanks
HansI have managed to get round this problem by creating a virtual
dimension of level 3 and then doing a cross join on the 2 dimensions.
This will get you your nesting

Parent-child dimension and show empty lines option

I have an issue with a parent-child dimension and ability to show empty lines in browser/client.

Example:

We are putting a parent-child dimension on rows in browser/excel based client and any measures in the data section.

By default the dimension is filtered and only shows dimension members with at least one non-empty measure. However, in certain cases we need to see all dimension members, even when the measures are empty.

To achieve this, there is an option to show empty lines (in cube browser as well as in client we are using for ad hoc data access).

There is however a problem with this option, but only if we use parent-child dimension and we go below first level. Every subsequent level shows all the dimension members and the parent dimension member.

I'll try to illustrate:

before enabling show empty lines:

Lvl1 Lvl2 Measures

A A-A X

A A-B X

A A-C X

B B-A X

B B-C X

After enabling:

Lvl1 Lvl2 Measures

A A X

A A-A X

A A-B X

A A-C X

A A-D

B B

B B-A X

B B-B

B B-C X

In our case this means that every listing of sub-categories withing a category in our product hierarchy, shows all sub-categories and a parent category.

This basically destroys the reporting layout we used since AS2000 and so far I did not find anything that would allow me to change this behavior or anything that would make me believe this is planned behavior in AS2005.

Any help is appreciated

Try setting the nonleafdatahidden property on the parent attribute.

Parent-child dimension

I'm trying to connect a parent-child dimension to my fact table.
I would like to browse cube measure (sold items quantity) through the
dimension "Groups" representing items family (tree structure).
Here are the tables:
Fact table (items sold with quantity):
--
IdItem;Quantity
1;5
2;2
3;1
1;6
3;8
--
Dimension table (parent-child tree representing groups item):
--
IdGroup;GroupName;IdParent;IdItem
1;AAA;-1;-1
2;BBB;1;-1
3;CCC;1;-1
4;O1;2;1
5;O2;2;2
6;O3;3;3
--
If I connect dimension to fact table with a regular relation (on IdItem
attribute of each table) and browse cube, I obtain for each node the
same quantity (22)
AAA (22)
-BBB (22)
--O1 (22)
--O2 (22)
-CCC (22)
--O3 (22)
I would like to obtain this result:
AAA (22)
-BBB (13)
--O1 (11)
--O2 (2)
-CCC (9)
--O3 (9)
If I use a "reference relation" with a middle table it's ok...
but I don't understand the difference!!!!!!
Can someone help me?
thanksOoops! I'm using Analysis Services 2005 :-)

> I'm trying to connect a parent-child dimension to my fact table.
> I would like to browse cube measure (sold items quantity) through the
> dimension "Groups" representing items family (tree structure).
> Here are the tables:
> Fact table (items sold with quantity):
> --
> IdItem;Quantity
> 1;5
> 2;2
> 3;1
> 1;6
> 3;8
> --
> Dimension table (parent-child tree representing groups item):
> --
> IdGroup;GroupName;IdParent;IdItem
> 1;AAA;-1;-1
> 2;BBB;1;-1
> 3;CCC;1;-1
> 4;O1;2;1
> 5;O2;2;2
> 6;O3;3;3
> --
> If I connect dimension to fact table with a regular relation (on IdItem
> attribute of each table) and browse cube, I obtain for each node the
> same quantity (22)
> AAA (22)
> -BBB (22)
> --O1 (22)
> --O2 (22)
> -CCC (22)
> --O3 (22)
> I would like to obtain this result:
> AAA (22)
> -BBB (13)
> --O1 (11)
> --O2 (2)
> -CCC (9)
> --O3 (9)
> If I use a "reference relation" with a middle table it's ok...
> but I don't understand the difference!!!!!!
> Can someone help me?
> thanks

Parent-Child and use of Surrogate keys gives problems...

After reading Marco Russo post http://sqlblog.com/blogs/marco_russo/archive/2007/07/03/surrogate-key-issues-with-analysis-services.aspx i can see that i'm not the only one with a specific business problem, when using surrogate keys. I hope that someone might be able to post a solution. If not, it seems that there is no other option but to use the business key as the attribute key.

Let's say that you have an account dimension build on a table like this

PK_Account, BK_Account, PK_Account_Parent, BK_Account_Parent, Account_Description

1, 30000, Null, Null, Sum Level A

2, 31000, 1, 30000, Account A

3, 31001, 1, 30000, Account B

4, 31002, 1, 30000, Account C

5, 40000, Null, Null, Sum Level B

So when I create a Parent-Child hierachy i SSAS i'll do the following

Assign the Parent

Usage = Parent

KeyColumn = PK_Account_Parent

Assign the Child which has to be Key

Usage = Key

KeyColumn = PK_Account

NameColumn = Account_Description

Now you have the Parent-Child hierachy using surrogate keys but this gives the problem that Marco describes. Furthermore the problem also includes using MDX. Let's say that you have a calculated member

"CALC A" that is defined by

"Account B + Account C"

In MDX this would be:

CREATE MEMBER CURRENTCUBE.[MEASURES].[CALC A]

AS sum(

{[Account].[Account].&[3],

[Account].[Account].&[4]},

[Measures].[Amount]),

FORMAT_STRING = "Standard",

VISIBLE = 1;

Or by using the ColumName

CREATE MEMBER CURRENTCUBE.[MEASURES].[CALC A]

AS sum(

{[Account].[Account].[Account B],

[Account].[Account].[Account C]},

[Measures].[Amount]),

FORMAT_STRING = "Standard",

VISIBLE = 1;

My Question is here if there is a way to relate to the Business Key ? (Maybe by using a ValueColumn ?)

Alternatively it's maybe be best practise NOT to use Surrogate Keys when the business key is relevant in the Business scenario, Reports and MDX calculations.... ?

Hello! One reason for using surrogate keys is for supporting slowly changing dimensions typ two, by keeping versions of a dimension record. It is hard to see any use for that with an account dimension. In your case , use the source keys.

HTH

Thomas Ivarsson

Parent Attribute Hierarchy

Does anyone know how to create a parent-child hierarchy that can be expanded in reporting services as well as analysis services? Currently, I have a dimension with three attributes: parent, child, and key. Everything seems to work fine in Analysis Services. However, reporting services doesn't seem to recognize the parent hierarchy as being a true hierarchy.

Any help? Thank you.

Does anyone have an idea or just need some more clarification?

Thanks

|||

There is only sinlge way to create parent-child hierarchy in Analysis Services. You dont have any choice here.

Not sure about Reporting Services and how it is displaying parent-child hierachy. Moving thread to Reporting Services forum.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.