Help with Relational Division

I am trying to create a matrix of counts to help identify our data holdings.
Thanks to Joe Celko I now know I need to perform relational division to find
datasets that match the criteria set up in a secondary table.
I'd be happy to initially just get the counts for each category, as I could
pass the "DSType" parameter in on the fly.
Problem is I can't figure out how from the "Plane/Hanger" example how to
form the relational division query to come up with the counts. Or even a
view listing the datasets that match each "Category"s "Topic" criteria.
There are 4 main tables involved:
-Datasets that we provide information on.
-Types of data that they hold (Regional, Historical, Global, etc.)
-Topics that describe the datasets (Ocean, Carbon, Sea Level, etc.)
-GOOS Matrix that lists Categories that we want to correlate the datasets to
The SQL script to create the tables and insert data into them is at:
http://oceanic.cms.udel.edu/matrix/..._Tables.sql.txt
A PDF of the relationships between the tables is at:
http://oceanic.cms.udel.edu/matrix/..._GOOSMatrix.pdf
And a PDF of the desired matrix view is at:
http://oceanic.cms.udel.edu/matrix/...trix_Output.pdf
TIA,
D
--
"If Pro is the opposite of Con, does that mean that Congress is the opposite
of Progress?"
--GallegherDoes this help? (using Joes's example):
CREATE TABLE PilotSkills (pilot CHAR(15) NOT NULL, plane CHAR(15) NOT
NULL, PRIMARY KEY (pilot, plane));
CREATE TABLE Hangar (plane CHAR(15) NOT NULL PRIMARY KEY);
INSERT INTO PilotSkills (pilot, plane)
SELECT 'Celko', 'Piper Cub' UNION ALL
SELECT 'Higgins', 'B-52 Bomber' UNION ALL
SELECT 'Higgins', 'F-14 Fighter' UNION ALL
SELECT 'Higgins', 'Piper Cub' UNION ALL
SELECT 'Jones', 'B-52 Bomber' UNION ALL
SELECT 'Jones', 'F-14 Fighter' UNION ALL
SELECT 'Smith', 'B-1 Bomber' UNION ALL
SELECT 'Smith', 'B-52 Bomber' UNION ALL
SELECT 'Smith', 'F-14 Fighter' UNION ALL
SELECT 'Wilson', 'B-1 Bomber' UNION ALL
SELECT 'Wilson', 'B-52 Bomber' UNION ALL
SELECT 'Wilson', 'F-14 Fighter' UNION ALL
SELECT 'Wilson', 'F-17 Fighter' ;
INSERT INTO Hangar (plane)
SELECT 'B-1 Bomber' UNION ALL
SELECT 'B-52 Bomber' UNION ALL
SELECT 'F-14 Fighter' ;
SELECT PS1.pilot,
COUNT(PS1.plane) total_skills,
T.total_planes
FROM PilotSkills AS PS1, Hangar AS H1,
(SELECT COUNT(plane) FROM Hangar) AS T(total_planes)
WHERE PS1.plane = H1.plane
GROUP BY PS1.pilot, T.total_planes ;

> The SQL script to create the tables and insert data into them is at:
> http://oceanic.cms.udel.edu/matrix/..._Tables.sql.txt
That's not up to the task in my opinion, which is why I've not used it.
No alternate keys and mostly nullable columns. IDENTITY should not be
the only key of any table and it's very hard to write and verify
queries against such a schema.
David Portas
SQL Server MVP
--

Help with multiple counts..

I have a application table that I have turned into a cube. It includes attributes of a organizaiton name, month, and year. This all works fine.

I would like to add another table to the cube that counts a visits. It also has attributes of organization name, month and year.

I am confused where I make the associations so they can operate in the same cube.

Do I join the tables in the datasource and just add the measure in the cube? Do I add a new dimention in the cube?

Any help in this area would be great. I am basically looking for a cube like so..

Month Year

Organization Name ApplicationCount (From the application table)

Visit Count (From the visit table)

Thanks in advance,

Mardo

If it has a similar structure, just a different measure, then you could add this table as a separate measure group with a measure in it that maps to the Visit Count. If these two table are completely identical you might be able to join them with a view or a named query in the DSV so that Visit Count is just added as an extra column to the existing cube, this might be a more efficient approach.