Better Modeling Methods ?

Want to model the following two items: Can anyone think of better solutions
*****************
* Address
*****************
Here we have an Address which could relate to a Company or an Individual, but the same address will never relate to both company and indvidual. (this is theory to keep the proble simple). In this senerio, a Company and an Individual will have different addresses sometimes, and the same address sometimes, and two or more Individuals could also have the same address
Table
--
Compan
Individua
Addres
--
- Possible Solution A:
--
Make Company and Individual Primary Keys Globally Unique and map them both to the same foreign key, in one Address table
Compan
--
CompanyId (GUID) --
|
|
Individual
--
IndividualId (GUID) --
| Addres
| --
+--> EntityI
--
- Possible Solution B:
--
Make two address tables - store the exact same kind of data in two different locations - ;
Compan
--
CompanyId (GUID) --
| CompanyAddres
| --
+--> CompanyId
Individual
--
IndividualId (GUID) --
| IndividualAddres
| --
+--> IndividualI
********************
* Drug Allergys
********************
Here we will be modling drugs and their associated allergys, based upon both Ingredient and Route (method of drug administration i.e. Oral,IV,Suppository). In this case, I will have to check both Ingredient and Route OR Ingredient ONLY OR Route ONLY to see if these has been a drug Allergy (in the real world many many items and combinations there of)
Table
--
Dru
Ingredien
Rout
Allerg
--
- Possible Solution A:
--
Place all drug descriptors (Ingredient, Route etc) in the Allergy table as foreign keys
Dru
--
DrugId --
| IngredientMap
| -- Ingredien
| IngredientMapId --
| IngredientId <-- IngredientId --
+->DrugId Ingredient
| | Allerg
| RouteMap | --
| -- Route | AllergyI
| RouteMapId -- +-> IngredientI
| RouteId <-- RouteId --> RouteI
+->DrugId Rout
This problem differs from the Address problem, in that I can not create unique keys, because I need to look at all "drug descriptors" (Ingredient,Route etc.) at the same time, and therefore mappling all "drug descriptors to one foreign key does not seem to be convienient
In this senerio, I have to check all columns in Allergy (WHERE clause), ALL THE TIME, in order to get the correct results. For Example
SELECT AllergyI
FROM Allerg
WHERE IngredientId IN
SELECT IngredientI
FROM IngredientMa
WHERE DrugId = ) AN
RouteId IN
SELECT Rout
FROM RouteMa
WHERE DrugId =Hi
For the first scenario you can have a superset that contains individuals and
companies and allocates the keys. This will be the FK from the Address
table, although I would have throught that your relationships is the wrong
way round and address is a FK from companies or individual.
As far as drugs goes you may want to look and talk to snomed. I would be
surprised if you can be allergic to a route only (although understandable if
1KG suppository is one!). I think that you will need to directly link route
and ingredient if your ingredient includes a quantity. i.e. an
Drug-Ingredient-Route table which is then the FK in an interim
Allergy-Drug-Ingredient-Route. Anything you do come up with should be gone
over with a clinician to make sure all possible scenarios are covered.
John
"sorengi" <anonymous@.discussions.microsoft.com> wrote in message
news:C594FB72-0B24-4EED-9670-CF0316A39415@.microsoft.com...
> Want to model the following two items: Can anyone think of better
solutions ?
> ******************
> * Address *
> ******************
> Here we have an Address which could relate to a Company or an Individual,
but the same address will never relate to both company and indvidual. (this
is theory to keep the proble simple). In this senerio, a Company and an
Individual will have different addresses sometimes, and the same address
sometimes, and two or more Individuals could also have the same address.
> Tables
> --
> Company
> Individual
> Address
> --
> - Possible Solution A:-
> --
> Make Company and Individual Primary Keys Globally Unique and map them both
to the same foreign key, in one Address table.
>
> Company
> --
> CompanyId (GUID) --|
> |
> |
> |
> |
> Individual |
> -- |
> IndividualId (GUID) --|
> | Address
> | --
> +--> EntityId
> --
> - Possible Solution B:-
> --
> Make two address tables - store the exact same kind of data in two
different locations - ;(
> Company
> --
> CompanyId (GUID) --|
> |
> | CompanyAddress
> | --
> +--> CompanyId
> Individual |
> -- |
> IndividualId (GUID) --|
> | IndividualAddress
> | --
> +--> IndividualId
>
> *********************
> * Drug Allergys *
> *********************
> Here we will be modling drugs and their associated allergys, based upon
both Ingredient and Route (method of drug administration i.e.
Oral,IV,Suppository). In this case, I will have to check both Ingredient
and Route OR Ingredient ONLY OR Route ONLY to see if these has been a drug
Allergy (in the real world many many items and combinations there of).
> Tables
> --
> Drug
> Ingredient
> Route
> Allergy
>
> --
> - Possible Solution A:-
> --
> Place all drug descriptors (Ingredient, Route etc) in the Allergy table as
foreign keys.
> Drug
> --
> DrugId --+
> | IngredientMap
> | -- Ingredient
> | IngredientMapId --
> | IngredientId <-- IngredientId --+
> +->DrugId Ingredient |
> | | Allergy
> | RouteMap | --
> | -- Route | AllergyId
> | RouteMapId -- +-> IngredientId
> | RouteId <-- RouteId --> RouteId
> +->DrugId Route
>
> This problem differs from the Address problem, in that I can not create
unique keys, because I need to look at all "drug descriptors"
(Ingredient,Route etc.) at the same time, and therefore mappling all "drug
descriptors to one foreign key does not seem to be convienient.
>
> In this senerio, I have to check all columns in Allergy (WHERE clause),
ALL THE TIME, in order to get the correct results. For Example:
> SELECT AllergyId
> FROM Allergy
> WHERE IngredientId IN (
> SELECT IngredientId
> FROM IngredientMap
> WHERE DrugId = X
> ) AND
> RouteId IN (
> SELECT Route
> FROM RouteMap
> WHERE DrugId = X
> )
>|||Hi
What I am not clear about is if you have really need to have say someone is
allergic to 250mg of Drug A but someone is not allergic to 500mg of Drug A
(and vice-versa)!!!
I think in general your drug dictionary is going to be reasonably static
therefore you can build your complex relationships upon upload and index
them appropriately. Additional design thought would be needed when/if a
Pharmacist is allowed to produce their own incantation!
John
"Sorengi" <anonymous@.discussions.microsoft.com> wrote in message
news:3F34819A-ACF2-463A-8461-A50C15EFD2A2@.microsoft.com...
> Great idea for Company/Individual/Address - Thanks John.
> The problem that I am having with the Drug Interactions is that there can
be any combination of items which could result in an allergy. I will look
at what it takes to design per your suggestion - thanks for the ideas.
> Michael D.|||The patient specific items are located in client databases - (Hospitals). If a patient is or is not allergic to drug "X" comes form another system..
Thanks for your help, I was able to get the Super Set to work great for both issues
Michael D.

Better Modeling Methods ?

Want to model the following two items: Can anyone think of better solutions ?
******************
* Address *
******************
Here we have an Address which could relate to a Company or an Individual, but the same address will never relate to both company and indvidual. (this is theory to keep the proble simple). In this senerio, a Company and an Individual will have different ad
dresses sometimes, and the same address sometimes, and two or more Individuals could also have the same address.
Tables
Company
Individual
Address
- Possible Solution A:-
Make Company and Individual Primary Keys Globally Unique and map them both to the same foreign key, in one Address table.
Company
CompanyId (GUID) --|
|
|
|
|
Individual |
-- |
IndividualId (GUID) --|
| Address
| --
+--> EntityId
- Possible Solution B:-
Make two address tables - store the exact same kind of data in two different locations - ;(
Company
CompanyId (GUID) --|
|
| CompanyAddress
| --
+--> CompanyId
Individual |
-- |
IndividualId (GUID) --|
| IndividualAddress
| --
+--> IndividualId
*********************
* Drug Allergys *
*********************
Here we will be modling drugs and their associated allergys, based upon both Ingredient and Route (method of drug administration i.e. Oral,IV,Suppository). In this case, I will have to check both Ingredient and Route OR Ingredient ONLY OR Route ONLY to s
ee if these has been a drug Allergy (in the real world many many items and combinations there of).
Tables
Drug
Ingredient
Route
Allergy
- Possible Solution A:-
Place all drug descriptors (Ingredient, Route etc) in the Allergy table as foreign keys.
Drug
DrugId --+
| IngredientMap
| -- Ingredient
| IngredientMapId --
| IngredientId <-- IngredientId --+
+->DrugId Ingredient |
| | Allergy
| RouteMap | --
| -- Route | AllergyId
| RouteMapId -- +-> IngredientId
| RouteId <-- RouteId --> RouteId
+->DrugId Route
This problem differs from the Address problem, in that I can not create unique keys, because I need to look at all "drug descriptors" (Ingredient,Route etc.) at the same time, and therefore mappling all "drug descriptors to one foreign key does not seem t
o be convienient.
In this senerio, I have to check all columns in Allergy (WHERE clause), ALL THE TIME, in order to get the correct results. For Example:
SELECT AllergyId
FROM Allergy
WHERE IngredientId IN (
SELECT IngredientId
FROM IngredientMap
WHERE DrugId = X
) AND
RouteId IN (
SELECT Route
FROM RouteMap
WHERE DrugId = X
)
Hi
For the first scenario you can have a superset that contains individuals and
companies and allocates the keys. This will be the FK from the Address
table, although I would have throught that your relationships is the wrong
way round and address is a FK from companies or individual.
As far as drugs goes you may want to look and talk to snomed. I would be
surprised if you can be allergic to a route only (although understandable if
1KG suppository is one!). I think that you will need to directly link route
and ingredient if your ingredient includes a quantity. i.e. an
Drug-Ingredient-Route table which is then the FK in an interim
Allergy-Drug-Ingredient-Route. Anything you do come up with should be gone
over with a clinician to make sure all possible scenarios are covered.
John
"sorengi" <anonymous@.discussions.microsoft.com> wrote in message
news:C594FB72-0B24-4EED-9670-CF0316A39415@.microsoft.com...
> Want to model the following two items: Can anyone think of better
solutions ?
> ******************
> * Address *
> ******************
> Here we have an Address which could relate to a Company or an Individual,
but the same address will never relate to both company and indvidual. (this
is theory to keep the proble simple). In this senerio, a Company and an
Individual will have different addresses sometimes, and the same address
sometimes, and two or more Individuals could also have the same address.
> Tables
> --
> Company
> Individual
> Address
> --
> - Possible Solution A:-
> --
> Make Company and Individual Primary Keys Globally Unique and map them both
to the same foreign key, in one Address table.
>
> Company
> --
> CompanyId (GUID) --|
> |
> |
> |
> |
> Individual |
> -- |
> IndividualId (GUID) --|
> | Address
> | --
> +--> EntityId
> --
> - Possible Solution B:-
> --
> Make two address tables - store the exact same kind of data in two
different locations - ;(
> Company
> --
> CompanyId (GUID) --|
> |
> | CompanyAddress
> | --
> +--> CompanyId
> Individual |
> -- |
> IndividualId (GUID) --|
> | IndividualAddress
> | --
> +--> IndividualId
>
> *********************
> * Drug Allergys *
> *********************
> Here we will be modling drugs and their associated allergys, based upon
both Ingredient and Route (method of drug administration i.e.
Oral,IV,Suppository). In this case, I will have to check both Ingredient
and Route OR Ingredient ONLY OR Route ONLY to see if these has been a drug
Allergy (in the real world many many items and combinations there of).
> Tables
> --
> Drug
> Ingredient
> Route
> Allergy
>
> --
> - Possible Solution A:-
> --
> Place all drug descriptors (Ingredient, Route etc) in the Allergy table as
foreign keys.
> Drug
> --
> DrugId --+
> | IngredientMap
> | -- Ingredient
> | IngredientMapId --
> | IngredientId <-- IngredientId --+
> +->DrugId Ingredient |
> | | Allergy
> | RouteMap | --
> | -- Route | AllergyId
> | RouteMapId -- +-> IngredientId
> | RouteId <-- RouteId --> RouteId
> +->DrugId Route
>
> This problem differs from the Address problem, in that I can not create
unique keys, because I need to look at all "drug descriptors"
(Ingredient,Route etc.) at the same time, and therefore mappling all "drug
descriptors to one foreign key does not seem to be convienient.
>
> In this senerio, I have to check all columns in Allergy (WHERE clause),
ALL THE TIME, in order to get the correct results. For Example:
> SELECT AllergyId
> FROM Allergy
> WHERE IngredientId IN (
> SELECT IngredientId
> FROM IngredientMap
> WHERE DrugId = X
> ) AND
> RouteId IN (
> SELECT Route
> FROM RouteMap
> WHERE DrugId = X
> )
>
|||Great idea for Company/Individual/Address - Thanks John.
The problem that I am having with the Drug Interactions is that there can be any combination of items which could result in an allergy. I will look at what it takes to design per your suggestion - thanks for the ideas.
Michael D.
|||Hi
What I am not clear about is if you have really need to have say someone is
allergic to 250mg of Drug A but someone is not allergic to 500mg of Drug A
(and vice-versa)!!!
I think in general your drug dictionary is going to be reasonably static
therefore you can build your complex relationships upon upload and index
them appropriately. Additional design thought would be needed when/if a
Pharmacist is allowed to produce their own incantation!
John
"Sorengi" <anonymous@.discussions.microsoft.com> wrote in message
news:3F34819A-ACF2-463A-8461-A50C15EFD2A2@.microsoft.com...
> Great idea for Company/Individual/Address - Thanks John.
> The problem that I am having with the Drug Interactions is that there can
be any combination of items which could result in an allergy. I will look
at what it takes to design per your suggestion - thanks for the ideas.
> Michael D.
|||The patient specific items are located in client databases - (Hospitals). If a patient is or is not allergic to drug "X" comes form another system...
Thanks for your help, I was able to get the Super Set to work great for both issues.
Michael D.

Better Modeling Methods ?

Want to model the following two items: Can anyone think of better solutions
?
******************
* Address *
******************
Here we have an Address which could relate to a Company or an Individual, bu
t the same address will never relate to both company and indvidual. (this is
theory to keep the proble simple). In this senerio, a Company and an Indivi
dual will have different ad
dresses sometimes, and the same address sometimes, and two or more Individua
ls could also have the same address.
Tables
--
Company
Individual
Address
- Possible Solution A:-
--
Make Company and Individual Primary Keys Globally Unique and map them both t
o the same foreign key, in one Address table.
Company
--
CompanyId (GUID) --|
|
|
|
|
Individual |
-- |
IndividualId (GUID) --|
| Address
| --
+--> EntityId
- Possible Solution B:-
--
Make two address tables - store the exact same kind of data in two different
locations - ;(
Company
--
CompanyId (GUID) --|
|
| CompanyAddress
| --
+--> CompanyId
Individual |
-- |
IndividualId (GUID) --|
| IndividualAddress
| --
+--> IndividualId
*********************
* Drug Allergys *
*********************
Here we will be modling drugs and their associated allergys, based upon both
Ingredient and Route (method of drug administration i.e. Oral,IV,Suppositor
y). In this case, I will have to check both Ingredient and Route OR Ingredi
ent ONLY OR Route ONLY to s
ee if these has been a drug Allergy (in the real world many many items and c
ombinations there of).
Tables
--
Drug
Ingredient
Route
Allergy
- Possible Solution A:-
--
Place all drug descriptors (Ingredient, Route etc) in the Allergy table as f
oreign keys.
Drug
--
DrugId --+
| IngredientMap
| -- Ingredient
| IngredientMapId --
| IngredientId <-- IngredientId --+
+->DrugId Ingredient |
| | Allergy
| RouteMap | --
| -- Route | AllergyId
| RouteMapId -- +-> IngredientId
| RouteId <-- RouteId --> RouteId
+->DrugId Route
This problem differs from the Address problem, in that I can not create uniq
ue keys, because I need to look at all "drug descriptors" (Ingredient,Route
etc.) at the same time, and therefore mappling all "drug descriptors to one
foreign key does not seem t
o be convienient.
In this senerio, I have to check all columns in Allergy (WHERE clause), ALL
THE TIME, in order to get the correct results. For Example:
SELECT AllergyId
FROM Allergy
WHERE IngredientId IN (
SELECT IngredientId
FROM IngredientMap
WHERE DrugId = X
) AND
RouteId IN (
SELECT Route
FROM RouteMap
WHERE DrugId = X
)Hi
For the first scenario you can have a superset that contains individuals and
companies and allocates the keys. This will be the FK from the Address
table, although I would have throught that your relationships is the wrong
way round and address is a FK from companies or individual.
As far as drugs goes you may want to look and talk to snomed. I would be
surprised if you can be allergic to a route only (although understandable if
1KG suppository is one!). I think that you will need to directly link route
and ingredient if your ingredient includes a quantity. i.e. an
Drug-Ingredient-Route table which is then the FK in an interim
Allergy-Drug-Ingredient-Route. Anything you do come up with should be gone
over with a clinician to make sure all possible scenarios are covered.
John
"sorengi" <anonymous@.discussions.microsoft.com> wrote in message
news:C594FB72-0B24-4EED-9670-CF0316A39415@.microsoft.com...
> Want to model the following two items: Can anyone think of better
solutions ?
> ******************
> * Address *
> ******************
> Here we have an Address which could relate to a Company or an Individual,
but the same address will never relate to both company and indvidual. (this
is theory to keep the proble simple). In this senerio, a Company and an
Individual will have different addresses sometimes, and the same address
sometimes, and two or more Individuals could also have the same address.
> Tables
> --
> Company
> Individual
> Address
> --
> - Possible Solution A:-
> --
> Make Company and Individual Primary Keys Globally Unique and map them both
to the same foreign key, in one Address table.
>
> Company
> --
> CompanyId (GUID) --|
> |
> |
> |
> |
> Individual |
> -- |
> IndividualId (GUID) --|
> | Address
> | --
> +--> EntityId
> --
> - Possible Solution B:-
> --
> Make two address tables - store the exact same kind of data in two
different locations - ;(
> Company
> --
> CompanyId (GUID) --|
> |
> | CompanyAddress
> | --
> +--> CompanyId
> Individual |
> -- |
> IndividualId (GUID) --|
> | IndividualAddress
> | --
> +--> IndividualId
>
> *********************
> * Drug Allergys *
> *********************
> Here we will be modling drugs and their associated allergys, based upon
both Ingredient and Route (method of drug administration i.e.
Oral,IV,Suppository). In this case, I will have to check both Ingredient
and Route OR Ingredient ONLY OR Route ONLY to see if these has been a drug
Allergy (in the real world many many items and combinations there of).
> Tables
> --
> Drug
> Ingredient
> Route
> Allergy
>
> --
> - Possible Solution A:-
> --
> Place all drug descriptors (Ingredient, Route etc) in the Allergy table as
foreign keys.
> Drug
> --
> DrugId --+
> | IngredientMap
> | -- Ingredient
> | IngredientMapId --
> | IngredientId <-- IngredientId --+
> +->DrugId Ingredient |
> | | Allergy
> | RouteMap | --
> | -- Route | AllergyId
> | RouteMapId -- +-> IngredientId
> | RouteId <-- RouteId --> RouteId
> +->DrugId Route
>
> This problem differs from the Address problem, in that I can not create
unique keys, because I need to look at all "drug descriptors"
(Ingredient,Route etc.) at the same time, and therefore mappling all "drug
descriptors to one foreign key does not seem to be convienient.
>
> In this senerio, I have to check all columns in Allergy (WHERE clause),
ALL THE TIME, in order to get the correct results. For Example:
> SELECT AllergyId
> FROM Allergy
> WHERE IngredientId IN (
> SELECT IngredientId
> FROM IngredientMap
> WHERE DrugId = X
> ) AND
> RouteId IN (
> SELECT Route
> FROM RouteMap
> WHERE DrugId = X
> )
>|||Great idea for Company/Individual/Address - Thanks John.
The problem that I am having with the Drug Interactions is that there can be
any combination of items which could result in an allergy. I will look at
what it takes to design per your suggestion - thanks for the ideas.
Michael D.|||Hi
What I am not clear about is if you have really need to have say someone is
allergic to 250mg of Drug A but someone is not allergic to 500mg of Drug A
(and vice-versa)!!!
I think in general your drug dictionary is going to be reasonably static
therefore you can build your complex relationships upon upload and index
them appropriately. Additional design thought would be needed when/if a
Pharmacist is allowed to produce their own incantation!
John
"Sorengi" <anonymous@.discussions.microsoft.com> wrote in message
news:3F34819A-ACF2-463A-8461-A50C15EFD2A2@.microsoft.com...
> Great idea for Company/Individual/Address - Thanks John.
> The problem that I am having with the Drug Interactions is that there can
be any combination of items which could result in an allergy. I will look
at what it takes to design per your suggestion - thanks for the ideas.
> Michael D.|||The patient specific items are located in client databases - (Hospitals). I
f a patient is or is not allergic to drug "X" comes form another system...
Thanks for your help, I was able to get the Super Set to work great for both
issues.
Michael D.

Better MDX Query in 2005?

I've worked on several analysis services and reporting services solutions but this has me stumped. I have an X,Y scatter chart in RS that needs to hit a cube. So I need an MDX query that returns something like this:

DiseaseAbrev Pathway Feasibility
ADD 2.7 1.9
XS 4.0 1.0
YYY 1.4 2.0

The goal is to have two columns, Pathway and Feasibility, that contain a weighted measure that is calculated by summing a set of other measures times a weighting factor. The Disease Score Type dimension has a parent-child hierarchy set up so that Pathway and Feasibility are two members who roll up the weighted leaf members. When applied to the Score measure (see query below) this gives me what I want.

SELECT {[Disease Score Type].[Parent Id].&[2],[Disease Score Type].[Parent Id].&[1]} ON COLUMNS,
[Disease].[Disease Abbrev].[Disease Abbrev] ON ROWS
FROM [IVDB]
WHERE ([Measures].[Score], [Disease Selection].[Short List].&[Short List])

But, and this is a big problem, Reporting Services requires measures and ONLY measures in the first axis. So this does not work, which is really frustrating since this is really the most elegant way of modelling the problem domain. How can I write a better query that returns a Pathway and a Feasibility column containing weighted sums of the Score measure for just those two dimension members?

Can someone please help me?

There is nothing really wrong with your MDX, it is an unreasonable limitation of Reporting Services which has been raised by a few people, including fellow MVP Chris Webb here: http://cwebbbi.spaces.live.com/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!163.entry

A work around is to create a an OLE DB datasource in RS2005 then choose the "Microsoft OLE DB Provider for Analysis Services 9.0" provider, you don't get the fancy designer and have to enter your MDX by hand, but you can structure your query any way you like.

|||

Thank you! The work around is fine for this report (and the dozen or so left to do...). I don't need the Query Builder in Reporting Services since I usually wind up writing the MDX directly to get what I want. And if I want a fancy designer the one in SQL Management Studio works very well. The only thing you really give up this way is direct support for parameters etc - but I can use the same tricks as the 'bad-old-days' of Analysis Services 2000 where you could fake parameters by using string functions to build the MDX!

It's just too bad that 2005 has a set of restrictions that make it's new features useless for anything but the most trivial MDX.

Thank you again!

Micah

|||

One way of working around the Analysis Services Provider limitation in this case is to use calculated measures, like:

With

Member [Measures].[PathwayScore] as

([Measures].[Score], [Disease Score Type].[Parent Id].&[2])

Member [Measures].[FeasibilityScore] as

([Measures].[Score], [Disease Score Type].[Parent Id].&[1])

SELECT {[Measures].[PathwayScore], [Measures].[FeasibilityScore]} ON COLUMNS,
[Disease].[Disease Abbrev].[Disease Abbrev] ON ROWS
FROM [IVDB]
WHERE ([Disease Selection].[Short List].&[Short List])