Hide key

Hi!
Very Greatful for some help as a newbie.. working on the dimension structure.
I would like to hide the key, so not visible when using the dimensions.. How would I do that?

If you are using Analysis Services 2005 you can mark the key attribute in the attributes window(left) in the dimension editor.

In the properties window, down to the right, you will see a AttributeHierarchyVisible property that you set to false.

HTH

Thomas Ivarsson

|||Thank you very much! :-)sql

Heterogeneous tree and foreign key relationship

Hi,
I need to represent a data structure in sql server which I think is
called a "heterogeneous tree", and I don't know how to efficiently do
this in sql.
I have a Container table :
ContainerName
C1
C2
C3
and each Container can have any amount of compartments in an
ContainerCompartment Table:
ContainerName CompartmentName
C1 C1P1
C1 C1P2
C2 C2P1
C2 C2P2
Each container can be sticked into a compartment of another container.
Now I am trying to represent this tree in SQL:
C1
/ \
C1P1 C1P2
/
C2
/ \
C2P1 C2P2
/ \
C3 C3
I came up with this table "Tree":
ContainerName ParentContainer ParentContainerCompartment
C1 NULL NULL
C2 C1 C1P1
C3 C2 C2P1
C3 C2 C2P2
But this is bad... I can set up a foreign key relationship between
ParentContainer and ContainerName in "Tree" but I cannot set up a
relationship between ParentContainerCompartment and the Compartment of
the ParentContainer. I think I got the whole ER design wrong.
TIA for any hint on this!
Regards
DCGet a copy of TREES & HIERARCHIES IN SQL. Since you want to do math on
the containers, uses the Nested sets model.|||"DC" <dc@.upsize.de> wrote in message
news:1126886220.616226.132560@.g44g2000cwa.googlegroups.com...
> Hi,
> I need to represent a data structure in sql server which I think is
> called a "heterogeneous tree", and I don't know how to efficiently do
> this in sql.
> I have a Container table :
> ContainerName
> C1
> C2
> C3
> and each Container can have any amount of compartments in an
> ContainerCompartment Table:
> ContainerName CompartmentName
> C1 C1P1
> C1 C1P2
> C2 C2P1
> C2 C2P2
> Each container can be sticked into a compartment of another container.
> Now I am trying to represent this tree in SQL:
> C1
> / \
> C1P1 C1P2
> /
> C2
> / \
> C2P1 C2P2
> / \
> C3 C3
> I came up with this table "Tree":
> ContainerName ParentContainer ParentContainerCompartment
> C1 NULL NULL
> C2 C1 C1P1
> C3 C2 C2P1
> C3 C2 C2P2
> But this is bad... I can set up a foreign key relationship between
> ParentContainer and ContainerName in "Tree" but I cannot set up a
> relationship between ParentContainerCompartment and the Compartment of
> the ParentContainer. I think I got the whole ER design wrong.
>
You're pretty close: you can use a foreignn key between (ParentContainer,
ParentContainerCompartment) referencing the container table.
Try something like this:
create table Container
(
Container varchar(20) not null primary key
)
create table Compartment
(
Container varchar(20) not null
references Container on delete cascade,
Compartment varchar(20) not null,
constraint pk_Compartment primary key (Container,Compartment)
)
create table ContainerCompartment
(
Container varchar(20) not null primary key
references Container on delete cascade,
ParentContainer varchar(20) not null,
ParentCompartment varchar(20) not null,
constraint fk_ContainerCompartment foreign key
(ParentContainer,ParentCompartment) references
Compartment(Container,Compartment)
)
GO
insert into Container(Container) values ('C1')
insert into Container(Container) values ('C2')
insert into Container(Container) values ('C3')
insert into Compartment(Container,Compartment) values ('C1','C1P1')
insert into Compartment(Container,Compartment) values ('C1','C1P2')
insert into Compartment(Container,Compartment) values ('C2','C2P1')
insert into Compartment(Container,Compartment) values ('C2','C2P2')
insert into
ContainerCompartment(Container,ParentCon
tainer,ParentCompartment)
values('C2','C1','C1P1')
insert into
ContainerCompartment(Container,ParentCon
tainer,ParentCompartment)
values('C3','C2','C2P1')
David|||Hi David,
thanks a lot for the suggestion. I also wanted to make sure that a
compartment is only added to the tree if the parent is a container that
actually owns such a compartment, I can implement that with a trigger.
What do you think about a modification, where the Tree table contains
references to either a container or a compartment, example:
Node ParentNodeId ContainerId CompartmentId
1 null 1 null
2 1 null 1
3 1 null 2
This way I could use the coalesce expression to either pick a
containerid or a compartmentid and add other parts (like a
CompartmentDrawer) to the hierarchy by adding a CompartmentDrawerId
column the the Tree table.
Regards
DC|||I got SQL for smarties, excellent book but it says "it all depends..."
about the tree choice to make. I would like to try out the nested set
model as a kind of optimization, but as you can see from my post my
current problem is not so much about performance but about data
integrity. Will your book about trees & hierachies answer this question
(optimal data structure for representing a tree with nodes of different
types)?
Regards
DC|||>> my current problem is not so much about performance but about data integr
ity.<<
A basic principle is that the nodes are separate from the tree
structure. With a varying, mixed tree is little messy but the
complexity can be hidden in VIEWs. It is a bit of work, but soooo much
easier than procedural code in a few hundred front end apps.
Buy the TREES book, look at the various models. If that does not help,
hire me so I will make my house note next month :)

Here is a question I cant seem to find the answer to

Hi all. I'm kind of stuck. I may be having a general conceptual error here.

Suppose I have a table with two columns in the primary key, ID and VERSION.

Suppose I have a child table that I want a relationship on ID only and not VERSION.

Is there a way to do this besides creating a check constraint? (ie When I print and ERD it gives the lines, so the "non" db folks can "see" the relationships)

not sure if this is possible or not but any clues you all can give would be most helpful.

Thanks,

bill
s e g fa h l t @. l o n g b o y s . n e tThe conceptual error you have made is mixing up the parent and child. The table with id and version should be the child, and then it can have a foreign key relationship with the parent.

CREATE TABLE parent (
id int NOT NULL ,
string char (10) NULL
)
GO

ALTER TABLE parent ADD CONSTRAINT
PK_parent PRIMARY KEY CLUSTERED
(id)
GO

CREATE TABLE child (
id int NOT NULL ,
version int NOT NULL ,
string char (10) NULL
)
GO

ALTER TABLE child ADD CONSTRAINT
PK_child PRIMARY KEY CLUSTERED
(id, version)
GO

ALTER TABLE child ADD CONSTRAINT
FK_child_parent FOREIGN KEY
(id) REFERENCES parent (id)
GO

Hope this helps.|||Thanks for the feedback. My apologies for not being more clear on the subject. I opted for brevity instead of details.

Here is my situation.
I have a very large table which I want to split up into smaller tables. The tables [should] ideally have a 1-1 relationship between them all.

I also need to implement a transaction tracking solution such that I can determine if any column of any of the tables was changed, when it was changed, and who changed it, and be able to rollback if necessary to any particular point in time.

Finally, I also need to implement a modification approval system, such that any proposed changes must be captured, run through the red-tape corporate pipeline, then when approved, put into the table as actual data. While the changes are in the approval process, I need to be able to show the entire record with the proposed changes to one set of individual, and the entire record with only the approved changes/data to another set of people.

There is non-trivial amount of tables I have to do this with.

I came up with 2 feasible solutions.

solution 1 is : Each table which I need to track will have a copy table(same structure). It will store the proposed changes and any change history.

solution 2 is a bit more abstract. For each of my tables which I need to have transaction auditing and modification approval on, I will add two columns. a version column and a stage column. The version column will be used mainly for reporting and an easy way to see changelog history for any particular item ID. The stage column will be of "proposed", "review", "approved", "rejected", "obsolete". All the data will be contained in one table. so for each unique record(identified by ID) I will actually have multiple records with the same ID. But each successive record will have a higher version number. As new Version Numbers are added, the stage will be moved to "obsolete" when the changes are approved. then that record's stage will be set to "approved" and it will become the official record.

I just came across this yesterday, so i'm still fleshing out my "solution". I like the idea of solution 2 as its compact and fairly easy to code for. However, the more I think about it, the more I feel I will break anykind of RI I can put and still use the DB to enforce it. I don't think I even need a primary key of (ID, Version), but you need a primary key, to have a foreign key. If I could get the relationship, without having a primary key(just have a clustered, non-unique index on ID) then that would be sufficient. However, I'm pretty new to Sql Server, so I don't know if there is a way to do that. Any Ideas?

here is a visual of what my solution 2 would be like.

I have a table
create table table1 (
id int not null,
version int not null,
stageId int not null,
more columns,
primary key (id, version)
)

I have a child table, which I need a RI check on ID only of the parent table. It too, will have a version column, but does not relate to the parent table. It will however, be part of the primary key of the child table.

create table child1ToTable1 (
id int not null,
version int not null,
stageId int not null,
more columns,
primary key(id, version)
)

create table child2ToTable1 (
id int not null,
version int not null,
stageId int not null,
more columns,
primary key(id, version)
)

I need the relationship from table1 to child[12]ToTable1 to be a many to many on ID, but I don't care about the version.

Clear as mud? I know. it kind of gives me a headache too.

:)

Thanks for you help.

bill

Help: trouble dropping master key

It could be trivial question.

I have trouble dropping master key. when i execute the following query..

open master key decryption by password = 'pass1234$'

alter master key drop encryption by password = 'pass1234$'

close master key

drop master key

go

I get the following result..

Msg 15558, Level 16, State 1, Line 3

Cannot drop encryption by password '********'.

Msg 15580, Level 16, State 1, Line 5

Cannot drop master key because certificate 'BackofficeServiceAccount' is encrypted by it.

But 'BackofficeServiceAccount' was already dropped.

Can any one help me how to drop master key?

I appreciate any input..

regards...

I realized the script was creating login and certificate with the same name.

Dropping the certificate first, helped dropping the master key...