Conceptual question about "dimension usage"

I have a 2 dimensions:

UNDERLYING 0-8 INSTRUMENT [one underlying has zero or many instruments]

then I have several fact tables, say FACT1, FACT2, FACT3, which reference instrument_id as a foreign key.

Is it the case that in order to aggregate the fact tables by underlying, I need to define a "Referenced' relationship using the dimension usage tab for each fact table separately?

Either I'm missing something (probable!) but this seems unnecessary to me. Why can't I just define the UNDERLYING dimension as the parent of the INSTRUMENT dimension, and then every time the instrument dimension gets joined to the fact table, it simply follows that the corresponding fact can be rolled up by <correction>underlying [was: instrument]</correction>.

Any clarifications gratefully received.

tx,

JG

Are the Underlying and Instrument dimensions always paired like this? If so, one option is to build a single dimension based on the two "tables" in your DSV.

SSAS gives you a lot of flexibility in how you structure your cube. The trade-off is you must be explicit about that structure. So, one rule is that for a dimension to be associated with a fact, there must be an explicit relationship ("path") defined betewen the measure group and the dimension. Think about the alternative. Given any dimension in a conformed data warehouse, you could probably find a path to any measure group using intermediary measure groups and their dimension relationships. You'd end up with a real mess (and inappropriate results).

One thing you can do to ease the burden of setting these up is to insure you have relationships defined in your DSV. The Cube Designer will detect these and take a first stab at setting up relationships in the cube. (Still, I don't believe it will detect referenced relationships per the reason above.)

Good luck,
Bryan

Conceptual ideas - 2 tables one changes other complete Cursors?

I think cursors might help me, but I'm not sure. I'm looking for ideas
on how to solve a problem I have.

Consider two tables, one table contains student information (very wide
100 fields) , the other historical changes of the student information,
(narrow, just fields that record changes).

As an example Table one has STUDENT_ID, STUDENT_MAJOR, STUDENT_NAME,
RECORD_DT and has one student in it.

Table two contains STUDENT_ID, STUDENT_MAJOR , CHANGE_DT and contains 2
records, since the student changed their major 2 times.

I want to end up with a table the contains 3 rows, the 2 changes to the
Major and the current student record. I want each row to be complete.
Everything that I have tried (joins, outer joins, union) I end up with
some field being null (in my example, the STUDENT_NAME would on be in
the original row, and null for the two changes)
I know this is pretty vague, but I am wondering if this is a place to
use CURSORS?
(Some of you may recognize this as a type 2 dimension or slowly
changing dimension as used in a data warehouse, which it is. I need to
build up my historical changes to I can feed it to my warehouse. I have
the current student record, and all the descreet changes made to the
student.)
TIA
RobHow about:

--represents current status
SELECT STUDENT_ID, STUDENT_MAJOR, RECORD_DT, STUDENT_NAME
FROM Table1
UNION ALL
SELECT t2.STUDENT_ID, t2.STUDENT_MAJOR, t2.CHANGE_DT, t1.STUDENT_NAME
FROM Table2 t2 JOIN Table1 t1 ON t2.STUDENT_ID = t1.STUDENT_ID

Or am I missing something?

Stu|||How about:

--represents current status
SELECT STUDENT_ID, STUDENT_MAJOR, RECORD_DT, STUDENT_NAME
FROM Table1
UNION ALL
SELECT t2.STUDENT_ID, t2.STUDENT_MAJOR, t2.CHANGE_DT, t1.STUDENT_NAME
FROM Table2 t2 JOIN Table1 t1 ON t2.STUDENT_ID = t1.STUDENT_ID

Or am I missing something?

Stu|||"rcamarda" <rcamarda@.cablespeed.com> wrote in message
news:1118684160.349709.100810@.z14g2000cwz.googlegr oups.com...
>I think cursors might help me, but I'm not sure. I'm looking for ideas
> on how to solve a problem I have.
> Consider two tables, one table contains student information (very wide
> 100 fields) , the other historical changes of the student information,
> (narrow, just fields that record changes).
> As an example Table one has STUDENT_ID, STUDENT_MAJOR, STUDENT_NAME,
> RECORD_DT and has one student in it.
> Table two contains STUDENT_ID, STUDENT_MAJOR , CHANGE_DT and contains 2
> records, since the student changed their major 2 times.
> I want to end up with a table the contains 3 rows, the 2 changes to the
> Major and the current student record. I want each row to be complete.
> Everything that I have tried (joins, outer joins, union) I end up with
> some field being null (in my example, the STUDENT_NAME would on be in
> the original row, and null for the two changes)
> I know this is pretty vague, but I am wondering if this is a place to
> use CURSORS?
> (Some of you may recognize this as a type 2 dimension or slowly
> changing dimension as used in a data warehouse, which it is. I need to
> build up my historical changes to I can feed it to my warehouse. I have
> the current student record, and all the descreet changes made to the
> student.)
> TIA
> Rob

Hi Rob,

Cursors are the devils toenails. There has to be a join that will do what
you want. Can you identify specifically what your primary key is? Once we
have this we might move forward.

regards

SYM.|||"rcamarda" <rcamarda@.cablespeed.com> wrote in message
news:1118684160.349709.100810@.z14g2000cwz.googlegr oups.com...
>I think cursors might help me, but I'm not sure. I'm looking for ideas
> on how to solve a problem I have.
> Consider two tables, one table contains student information (very wide
> 100 fields) , the other historical changes of the student information,
> (narrow, just fields that record changes).
> As an example Table one has STUDENT_ID, STUDENT_MAJOR, STUDENT_NAME,
> RECORD_DT and has one student in it.
> Table two contains STUDENT_ID, STUDENT_MAJOR , CHANGE_DT and contains 2
> records, since the student changed their major 2 times.
> I want to end up with a table the contains 3 rows, the 2 changes to the
> Major and the current student record. I want each row to be complete.
> Everything that I have tried (joins, outer joins, union) I end up with
> some field being null (in my example, the STUDENT_NAME would on be in
> the original row, and null for the two changes)
> I know this is pretty vague, but I am wondering if this is a place to
> use CURSORS?
> (Some of you may recognize this as a type 2 dimension or slowly
> changing dimension as used in a data warehouse, which it is. I need to
> build up my historical changes to I can feed it to my warehouse. I have
> the current student record, and all the descreet changes made to the
> student.)
> TIA
> Rob

Hi Rob,

Cursors are the devils toenails. There has to be a join that will do what
you want. Can you identify specifically what your primary key is? Once we
have this we might move forward.

regards

SYM.|||CREATE TABLE "dbo"."F_Student_Sample"
(
"STUDENT_ID" VARCHAR(20) NOT NULL,
"STUDENT_LEAD_ID" VARCHAR(10) NULL,
"RECORD_DT" DATETIME NULL,
"STUDENT_LASTNAME" VARCHAR(40) NULL,
"STUDENT_FIRSTNAME" VARCHAR(40) NULL,
"STUDENT_CAMPUS_ID" VARCHAR(10) NULL,
"STUDENT_ADMREP_ID" VARCHAR(10) NULL,
"STUDENT_MARKETCODE_ID" VARCHAR(10) NULL
)
;

insert into [F_Student_Sample] VALUES
('100','900','2005-05-01','CAMARDA','ROBERT','HOST*001','TLS*123','I20')

CREATE TABLE "dbo"."Student_Changes_Sample"
(
"STUDENT_ID" VARCHAR(20) NOT NULL,
"CHANGE_CODE" NUMERIC(19) NULL,
"CHANGE" VARCHAR(100) NULL,
"RECORD_DT" DATETIME NULL,
"STUDENT_CAMPUS_ID" VARCHAR(10) NULL,
"STUDENT_ADMREP_ID" VARCHAR(10) NULL
)
;
-- The addtion of the two columns my be redundant, (STUDENT_CAMPUS_ID
and STUDENT_ADMREP_ID)
-- CHANGE_CODE = 7, CHANGE will contain the new value for
STUDENT_CAMPUS_ID
-- CHANGE_CODE = 10, CHANGE will contain the new value for
STUDENT_ADMREP_ID
-- STUDENT_ID is my "primary key" but it is not unique in this case,
since I need all the rows.

INSERT INTO [Student_Changes_Sample] VALUES
('100',7,'HOST*002','2001-01-03','HOST*002',NULL)
INSERT INTO [Student_Changes_Sample] VALUES
('100',7,'HOST*003','2002-04-03','HOST*003',NULL)
INSERT INTO [Student_Changes_Sample] VALUES
('100',7,'HOST*004','2003-02-13','HOST*004',NULL)
INSERT INTO [Student_Changes_Sample] VALUES
('100',7,'DMI10','2003-02-13',NULL,'DMI10')

I need to end up with 5 rows of information, the current record found
in F_STUDENT_SAMPLE, and the 4 changes in the apporiate columns with
all the fields populated.
Thanks|||CREATE TABLE "dbo"."F_Student_Sample"
(
"STUDENT_ID" VARCHAR(20) NOT NULL,
"STUDENT_LEAD_ID" VARCHAR(10) NULL,
"RECORD_DT" DATETIME NULL,
"STUDENT_LASTNAME" VARCHAR(40) NULL,
"STUDENT_FIRSTNAME" VARCHAR(40) NULL,
"STUDENT_CAMPUS_ID" VARCHAR(10) NULL,
"STUDENT_ADMREP_ID" VARCHAR(10) NULL,
"STUDENT_MARKETCODE_ID" VARCHAR(10) NULL
)
;

insert into [F_Student_Sample] VALUES
('100','900','2005-05-01','CAMARDA','ROBERT','HOST*001','TLS*123','I20')

CREATE TABLE "dbo"."Student_Changes_Sample"
(
"STUDENT_ID" VARCHAR(20) NOT NULL,
"CHANGE_CODE" NUMERIC(19) NULL,
"CHANGE" VARCHAR(100) NULL,
"RECORD_DT" DATETIME NULL,
"STUDENT_CAMPUS_ID" VARCHAR(10) NULL,
"STUDENT_ADMREP_ID" VARCHAR(10) NULL
)
;
-- The addtion of the two columns my be redundant, (STUDENT_CAMPUS_ID
and STUDENT_ADMREP_ID)
-- CHANGE_CODE = 7, CHANGE will contain the new value for
STUDENT_CAMPUS_ID
-- CHANGE_CODE = 10, CHANGE will contain the new value for
STUDENT_ADMREP_ID
-- STUDENT_ID is my "primary key" but it is not unique in this case,
since I need all the rows.

INSERT INTO [Student_Changes_Sample] VALUES
('100',7,'HOST*002','2001-01-03','HOST*002',NULL)
INSERT INTO [Student_Changes_Sample] VALUES
('100',7,'HOST*003','2002-04-03','HOST*003',NULL)
INSERT INTO [Student_Changes_Sample] VALUES
('100',7,'HOST*004','2003-02-13','HOST*004',NULL)
INSERT INTO [Student_Changes_Sample] VALUES
('100',7,'DMI10','2003-02-13',NULL,'DMI10')

I need to end up with 5 rows of information, the current record found
in F_STUDENT_SAMPLE, and the 4 changes in the apporiate columns with
all the fields populated.
Thanks|||Thanks Stu,
I'm ending up with null data again.
Using you example, I created:
select
student_id,
student_campus_id,
'' as student_lastname
from student_changes where student_id = '1000139200'
union
select
t2.student_id,
t2.student_campus_id,
t2.student_lastname
from
student t2 join student_changes t1 on t2.student_id = t1.student_id
WHERE T2.STUDENT_ID = '1000139200'

I get:
1000139200NULL
1000139200003
1000139200006
1000139200016
1000139200HOST*006Iverson Iii

I need the last name (Iverson Iii) to be on all rows|||Thanks Stu,
I'm ending up with null data again.
Using you example, I created:
select
student_id,
student_campus_id,
'' as student_lastname
from student_changes where student_id = '1000139200'
union
select
t2.student_id,
t2.student_campus_id,
t2.student_lastname
from
student t2 join student_changes t1 on t2.student_id = t1.student_id
WHERE T2.STUDENT_ID = '1000139200'

I get:
1000139200NULL
1000139200003
1000139200006
1000139200016
1000139200HOST*006Iverson Iii

I need the last name (Iverson Iii) to be on all rows|||Try this:

SELECT S.student_id, S.student_lead_id, C.record_dt,
S.student_lastname, S.student_firstname,
COALESCE(C.student_campus_id,S.student_campus_id) AS student_campus_id,
COALESCE(C.student_admrep_id,S.student_admrep_id) AS student_admrep_id,
S.student_marketcode_id
FROM f_student_sample AS S,
student_changes_sample AS C

--
David Portas
SQL Server MVP
--|||Try this:

SELECT S.student_id, S.student_lead_id, C.record_dt,
S.student_lastname, S.student_firstname,
COALESCE(C.student_campus_id,S.student_campus_id) AS student_campus_id,
COALESCE(C.student_admrep_id,S.student_admrep_id) AS student_admrep_id,
S.student_marketcode_id
FROM f_student_sample AS S,
student_changes_sample AS C

--
David Portas
SQL Server MVP
--|||CORRECTION: Add the WHERE clause:

...
WHERE S.student_id = C.student_id

--
David Portas
SQL Server MVP
--

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:O-2dnduoiZPNdjDfRVn-oA@.giganews.com...
> Try this:
> SELECT S.student_id, S.student_lead_id, C.record_dt,
> S.student_lastname, S.student_firstname,
> COALESCE(C.student_campus_id,S.student_campus_id) AS student_campus_id,
> COALESCE(C.student_admrep_id,S.student_admrep_id) AS student_admrep_id,
> S.student_marketcode_id
> FROM f_student_sample AS S,
> student_changes_sample AS C
> --
> David Portas
> SQL Server MVP
> --|||CORRECTION: Add the WHERE clause:

...
WHERE S.student_id = C.student_id

--
David Portas
SQL Server MVP
--

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:O-2dnduoiZPNdjDfRVn-oA@.giganews.com...
> Try this:
> SELECT S.student_id, S.student_lead_id, C.record_dt,
> S.student_lastname, S.student_firstname,
> COALESCE(C.student_campus_id,S.student_campus_id) AS student_campus_id,
> COALESCE(C.student_admrep_id,S.student_admrep_id) AS student_admrep_id,
> S.student_marketcode_id
> FROM f_student_sample AS S,
> student_changes_sample AS C
> --
> David Portas
> SQL Server MVP
> --|||David, this is pretty cool (although I'm not sure whats going on...Ill
have to read up on coalesce).
It seems that eh coalesce is returning the first non-null field that
it's given in the argument list.

COALESCE(C.student_campus_id,S*.student_campus_id) AS
student_campus_id,
COALESCE(C.student_admrep_id,S*.student_admrep_id) AS
student_admrep_id,
COALESCE(C.student_market_id,s.student_market_id) as student_market_id,
COALESCE(c.changeN, s.Student_N) as Student_N
Now I just have to expand this into all the fields that I'm tracking.

Pretty cool, I don't think I would have thought of this before, but now
you've given me another tool in my arsenal.
Thanks|||David, this is pretty cool (although I'm not sure whats going on...Ill
have to read up on coalesce).
It seems that eh coalesce is returning the first non-null field that
it's given in the argument list.

COALESCE(C.student_campus_id,S*.student_campus_id) AS
student_campus_id,
COALESCE(C.student_admrep_id,S*.student_admrep_id) AS
student_admrep_id,
COALESCE(C.student_market_id,s.student_market_id) as student_market_id,
COALESCE(c.changeN, s.Student_N) as Student_N
Now I just have to expand this into all the fields that I'm tracking.

Pretty cool, I don't think I would have thought of this before, but now
you've given me another tool in my arsenal.
Thanks|||rcamarda (rcamarda@.cablespeed.com) writes:
> David, this is pretty cool (although I'm not sure whats going on...Ill
> have to read up on coalesce).
> It seems that eh coalesce is returning the first non-null field that
> it's given in the argument list.

That's it!

As for where to read about coalesce, CASE etc, see my signature.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||rcamarda (rcamarda@.cablespeed.com) writes:
> David, this is pretty cool (although I'm not sure whats going on...Ill
> have to read up on coalesce).
> It seems that eh coalesce is returning the first non-null field that
> it's given in the argument list.

That's it!

As for where to read about coalesce, CASE etc, see my signature.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Follow up:
This works like a champ! A generalize form:

SELECT
<< current student fields. >>
-- brings in all students records
FROM current_records
UNION
SELECT
-- bring in all the student changes
<< 'static' fields>>,
COALESCE(changed_records.<field>, current_records.<field>) AS <FIELD>
<<n fields>>
FROM changed_records
WHERE current_records.business_id=changed_records.busine ss_id

actual SQL I created: (I may need to look this up some day *grin*)

SELECT
"STUDENT_ID",
"STUDENT_APPLICATION_DT",
"STUDENT_ETHNIC_ID",
"STUDENT_VISA_TYPE",
"STUDENT_GENDER",
"STUDENT_MARITAL",
"STUDENT_BIRTH_DT",
"STUDENT_BIRTH_PLACE",
"STUDENT_LEAD_ID",
"STUDENT_INPUT_DT",
"STUDENT_FINAID_REQ",
"STUDENT_VA_STATUS",
"STUDENT_VA_DT",
"STUDENT_EMAIL",
"STUDENT_FAX",
"STUDENT_COUNTRY_ID",
"STUDENT_COUNTRY_CAPTION",
"RECORD_DT",
"STUDENT_LASTNAME",
"STUDENT_FIRSTNAME",
"STUDENT_MI",
"STUDENT_ADDRESS1",
"STUDENT_ADDRESS2",
"STUDENT_CITY",
"STUDENT_STATE",
"STUDENT_ZIP",
"STUDENT_HOME_PHONE",
"STUDENT_WORK_PHONE",
"STUDENT_HS_NAME",
"STUDENT_EXTERNAL_ID",
"STUDENT_HS_GRAD_DT",
"STUDENT_FINANCIAL_AID",
"STUDENT_COMPANY_ID",
"STUDENT_LPROGRAM_ID",
"STUDENT_OTHER_COMPANY_CAPTION",
"STUDENT_CAMPUS_ID",
"STUDENT_ADVISOR_ID",
"STUDENT_PIN_ID",
"STUDENT_WORK_EXTENSION",
"STUDENT_EXPECTED_START_DT",
"STUDENT_SPONSOR_ID",
"STUDENT_LOAD_DT",
"STUDENT_DO_NOT_CALL",
"STUDENT_DO_NOT_MAIL",
"STUDENT_DO_NOT_EMAIL",
"STUDENT_VISA_EXCPT_SESSION_ID",
"STUDENT_CREATE_DT",
"STUDENT_CREATE_TIME",
"STUDENT_TALISMA_ID",
"STUDENT_TALISMA_STATUS",
"STUDENT_TALISMA_SUBSTATUS",
"STUDENT_PEP_DT",
"STUDENT_VOC_REHAB",
"STUDENT_ADMREP_ID",
"STUDENT_MARKETCODE_ID"
FROM "dbo"."STUDENT"
UNION
SELECT
STUDENT."STUDENT_ID",
STUDENT."STUDENT_APPLICATION_DT",
STUDENT."STUDENT_ETHNIC_ID",
STUDENT."STUDENT_VISA_TYPE",
STUDENT."STUDENT_GENDER",
STUDENT."STUDENT_MARITAL",
STUDENT."STUDENT_BIRTH_DT",
STUDENT."STUDENT_BIRTH_PLACE",
STUDENT."STUDENT_LEAD_ID",
STUDENT."STUDENT_INPUT_DT",
STUDENT."STUDENT_FINAID_REQ",
STUDENT."STUDENT_VA_STATUS",
STUDENT."STUDENT_VA_DT",
STUDENT."STUDENT_EMAIL",
STUDENT."STUDENT_FAX",
STUDENT."STUDENT_COUNTRY_ID",
STUDENT."STUDENT_COUNTRY_CAPTION",
STUDENT_CHANGES."RECORD_DT",
STUDENT."STUDENT_LASTNAME",
STUDENT."STUDENT_FIRSTNAME",
STUDENT."STUDENT_MI",
STUDENT."STUDENT_ADDRESS1",
STUDENT."STUDENT_ADDRESS2",
STUDENT."STUDENT_CITY",
STUDENT."STUDENT_STATE",
STUDENT."STUDENT_ZIP",
STUDENT."STUDENT_HOME_PHONE",
STUDENT."STUDENT_WORK_PHONE",
STUDENT."STUDENT_HS_NAME",
STUDENT."STUDENT_EXTERNAL_ID",
STUDENT."STUDENT_HS_GRAD_DT",
STUDENT."STUDENT_FINANCIAL_AID",
STUDENT."STUDENT_COMPANY_ID",
STUDENT."STUDENT_LPROGRAM_ID",
STUDENT."STUDENT_OTHER_COMPANY_CAPTION",
COALESCE(student_changes.student_campus_id,student .student_campus_id)
AS STUDENT_CAMPUS_ID,
STUDENT."STUDENT_ADVISOR_ID",
STUDENT."STUDENT_PIN_ID",
STUDENT."STUDENT_WORK_EXTENSION",
STUDENT."STUDENT_EXPECTED_START_DT",
STUDENT."STUDENT_SPONSOR_ID",
STUDENT."STUDENT_LOAD_DT",
STUDENT."STUDENT_DO_NOT_CALL",
STUDENT."STUDENT_DO_NOT_MAIL",
STUDENT."STUDENT_DO_NOT_EMAIL",
STUDENT."STUDENT_VISA_EXCPT_SESSION_ID",
STUDENT."STUDENT_CREATE_DT",
STUDENT."STUDENT_CREATE_TIME",
STUDENT."STUDENT_TALISMA_ID",
STUDENT."STUDENT_TALISMA_STATUS",
STUDENT."STUDENT_TALISMA_SUBSTATUS",
STUDENT."STUDENT_PEP_DT",
STUDENT."STUDENT_VOC_REHAB",

COALESCE(student_changes.student_ADMREP_id,student .student_ADMREP_id)
AS STUDENT_ADMREP_ID,
STUDENT."STUDENT_MARKETCODE_ID"
FROM
"dbo"."STUDENT",
"dbo"."STUDENT_CHANGES"
WHERE
STUDENT.STUDENT_ID = STUDENT_CHANGES.STUDENT_ID

ref: DecisionStream Fact build Cognos SCD slowly changing dimensions|||Follow up:
This works like a champ! A generalize form:

SELECT
<< current student fields. >>
-- brings in all students records
FROM current_records
UNION
SELECT
-- bring in all the student changes
<< 'static' fields>>,
COALESCE(changed_records.<field>, current_records.<field>) AS <FIELD>
<<n fields>>
FROM changed_records
WHERE current_records.business_id=changed_records.busine ss_id

actual SQL I created: (I may need to look this up some day *grin*)

SELECT
"STUDENT_ID",
"STUDENT_APPLICATION_DT",
"STUDENT_ETHNIC_ID",
"STUDENT_VISA_TYPE",
"STUDENT_GENDER",
"STUDENT_MARITAL",
"STUDENT_BIRTH_DT",
"STUDENT_BIRTH_PLACE",
"STUDENT_LEAD_ID",
"STUDENT_INPUT_DT",
"STUDENT_FINAID_REQ",
"STUDENT_VA_STATUS",
"STUDENT_VA_DT",
"STUDENT_EMAIL",
"STUDENT_FAX",
"STUDENT_COUNTRY_ID",
"STUDENT_COUNTRY_CAPTION",
"RECORD_DT",
"STUDENT_LASTNAME",
"STUDENT_FIRSTNAME",
"STUDENT_MI",
"STUDENT_ADDRESS1",
"STUDENT_ADDRESS2",
"STUDENT_CITY",
"STUDENT_STATE",
"STUDENT_ZIP",
"STUDENT_HOME_PHONE",
"STUDENT_WORK_PHONE",
"STUDENT_HS_NAME",
"STUDENT_EXTERNAL_ID",
"STUDENT_HS_GRAD_DT",
"STUDENT_FINANCIAL_AID",
"STUDENT_COMPANY_ID",
"STUDENT_LPROGRAM_ID",
"STUDENT_OTHER_COMPANY_CAPTION",
"STUDENT_CAMPUS_ID",
"STUDENT_ADVISOR_ID",
"STUDENT_PIN_ID",
"STUDENT_WORK_EXTENSION",
"STUDENT_EXPECTED_START_DT",
"STUDENT_SPONSOR_ID",
"STUDENT_LOAD_DT",
"STUDENT_DO_NOT_CALL",
"STUDENT_DO_NOT_MAIL",
"STUDENT_DO_NOT_EMAIL",
"STUDENT_VISA_EXCPT_SESSION_ID",
"STUDENT_CREATE_DT",
"STUDENT_CREATE_TIME",
"STUDENT_TALISMA_ID",
"STUDENT_TALISMA_STATUS",
"STUDENT_TALISMA_SUBSTATUS",
"STUDENT_PEP_DT",
"STUDENT_VOC_REHAB",
"STUDENT_ADMREP_ID",
"STUDENT_MARKETCODE_ID"
FROM "dbo"."STUDENT"
UNION
SELECT
STUDENT."STUDENT_ID",
STUDENT."STUDENT_APPLICATION_DT",
STUDENT."STUDENT_ETHNIC_ID",
STUDENT."STUDENT_VISA_TYPE",
STUDENT."STUDENT_GENDER",
STUDENT."STUDENT_MARITAL",
STUDENT."STUDENT_BIRTH_DT",
STUDENT."STUDENT_BIRTH_PLACE",
STUDENT."STUDENT_LEAD_ID",
STUDENT."STUDENT_INPUT_DT",
STUDENT."STUDENT_FINAID_REQ",
STUDENT."STUDENT_VA_STATUS",
STUDENT."STUDENT_VA_DT",
STUDENT."STUDENT_EMAIL",
STUDENT."STUDENT_FAX",
STUDENT."STUDENT_COUNTRY_ID",
STUDENT."STUDENT_COUNTRY_CAPTION",
STUDENT_CHANGES."RECORD_DT",
STUDENT."STUDENT_LASTNAME",
STUDENT."STUDENT_FIRSTNAME",
STUDENT."STUDENT_MI",
STUDENT."STUDENT_ADDRESS1",
STUDENT."STUDENT_ADDRESS2",
STUDENT."STUDENT_CITY",
STUDENT."STUDENT_STATE",
STUDENT."STUDENT_ZIP",
STUDENT."STUDENT_HOME_PHONE",
STUDENT."STUDENT_WORK_PHONE",
STUDENT."STUDENT_HS_NAME",
STUDENT."STUDENT_EXTERNAL_ID",
STUDENT."STUDENT_HS_GRAD_DT",
STUDENT."STUDENT_FINANCIAL_AID",
STUDENT."STUDENT_COMPANY_ID",
STUDENT."STUDENT_LPROGRAM_ID",
STUDENT."STUDENT_OTHER_COMPANY_CAPTION",
COALESCE(student_changes.student_campus_id,student .student_campus_id)
AS STUDENT_CAMPUS_ID,
STUDENT."STUDENT_ADVISOR_ID",
STUDENT."STUDENT_PIN_ID",
STUDENT."STUDENT_WORK_EXTENSION",
STUDENT."STUDENT_EXPECTED_START_DT",
STUDENT."STUDENT_SPONSOR_ID",
STUDENT."STUDENT_LOAD_DT",
STUDENT."STUDENT_DO_NOT_CALL",
STUDENT."STUDENT_DO_NOT_MAIL",
STUDENT."STUDENT_DO_NOT_EMAIL",
STUDENT."STUDENT_VISA_EXCPT_SESSION_ID",
STUDENT."STUDENT_CREATE_DT",
STUDENT."STUDENT_CREATE_TIME",
STUDENT."STUDENT_TALISMA_ID",
STUDENT."STUDENT_TALISMA_STATUS",
STUDENT."STUDENT_TALISMA_SUBSTATUS",
STUDENT."STUDENT_PEP_DT",
STUDENT."STUDENT_VOC_REHAB",

COALESCE(student_changes.student_ADMREP_id,student .student_ADMREP_id)
AS STUDENT_ADMREP_ID,
STUDENT."STUDENT_MARKETCODE_ID"
FROM
"dbo"."STUDENT",
"dbo"."STUDENT_CHANGES"
WHERE
STUDENT.STUDENT_ID = STUDENT_CHANGES.STUDENT_ID

ref: DecisionStream Fact build Cognos SCD slowly changing dimensions

Conceptual and speed question

Conceptual and speed question
Hi,
My program seems to slow down drastically because as I fill my array and
table with many values, the program suffers tremendously. The first thing my
program does is to search the jagged array to try to find an element in that
array. If it does not find that element in that array, then it adds another
element and that is the problem. Once I have many elements in that array, it
takes a long time to do a search. Furthermore, not only do I add an element
to that array if it does not find it, but I also add it to my table and as
my table gets bigger, it also slows everything down drastically.
What I thought may be possible is to create many arrays instead of only 1
array and that would speed up the search. The problem is that I do not know
what the size of each array will be at the beginning as some of the arrays
might be much larger than others. Is there a way to declare arrays and then
to dynamically increase their size when needed, and if it is possible would
this hamper the performance because my arrays will be quite large. I also
thought of maybe sorting the array, but that would take too long. Is there a
way to index an array to speed up the search?
As for my table in the database, I was wandering if I created many tables
would that also help speed up the process or will it not make a difference
because it is in the same database. And if that is the case that it does not
make a difference in speed because it is in the same database, then can I
save it to a different database? Another possibility I thought of is to save
the rows to another database and delete the rows in original database after
a certain number of rows have been added.
TIA
Roy"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:qKrff.23031$AZ3.235633@.wagner.videotron.net...
> Conceptual and speed question
>
> Hi,
>
> My program seems to slow down drastically because as I fill my array and
> table with many values, the program suffers tremendously. The first thing
> my program does is to search the jagged array to try to find an element in
> that array. If it does not find that element in that array, then it adds
> another element and that is the problem. Once I have many elements in that
> array, it takes a long time to do a search. Furthermore, not only do I add
> an element to that array if it does not find it, but I also add it to my
> table and as my table gets bigger, it also slows everything down
> drastically.
>
> What I thought may be possible is to create many arrays instead of only 1
> array and that would speed up the search. The problem is that I do not
> know what the size of each array will be at the beginning as some of the
> arrays might be much larger than others. Is there a way to declare arrays
> and then to dynamically increase their size when needed, and if it is
> possible would this hamper the performance because my arrays will be quite
> large. I also thought of maybe sorting the array, but that would take too
> long. Is there a way to index an array to speed up the search?
>
> As for my table in the database, I was wandering if I created many tables
> would that also help speed up the process or will it not make a difference
> because it is in the same database. And if that is the case that it does
> not make a difference in speed because it is in the same database, then
> can I save it to a different database? Another possibility I thought of is
> to save the rows to another database and delete the rows in original
> database after a certain number of rows have been added.
>
> TIA
> Roy
>
I'll skip the question about arrays. There are no arrays in SQL Server and
you didn't mention what other product you might be using.
On the database side you should first be considering what indexes might
support your application better. Indexes are likely to have much more impact
on performance than splitting the table up.
David Portas
SQL Server MVP
--|||Array, what programming language are you using? This group is for SQL Server
T-SQL.
Loading a large resultset into an application array is very slow, especially
if you are using something like ReDim to increase the size of the array each
time you loop through a client side cursor. Whatever, you are doing with an
array, consider just using an ADO recordset.
"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:qKrff.23031$AZ3.235633@.wagner.videotron.net...
> Conceptual and speed question
>
> Hi,
>
> My program seems to slow down drastically because as I fill my array and
> table with many values, the program suffers tremendously. The first thing
> my program does is to search the jagged array to try to find an element in
> that array. If it does not find that element in that array, then it adds
> another element and that is the problem. Once I have many elements in that
> array, it takes a long time to do a search. Furthermore, not only do I add
> an element to that array if it does not find it, but I also add it to my
> table and as my table gets bigger, it also slows everything down
> drastically.
>
> What I thought may be possible is to create many arrays instead of only 1
> array and that would speed up the search. The problem is that I do not
> know what the size of each array will be at the beginning as some of the
> arrays might be much larger than others. Is there a way to declare arrays
> and then to dynamically increase their size when needed, and if it is
> possible would this hamper the performance because my arrays will be quite
> large. I also thought of maybe sorting the array, but that would take too
> long. Is there a way to index an array to speed up the search?
>
> As for my table in the database, I was wandering if I created many tables
> would that also help speed up the process or will it not make a difference
> because it is in the same database. And if that is the case that it does
> not make a difference in speed because it is in the same database, then
> can I save it to a different database? Another possibility I thought of is
> to save the rows to another database and delete the rows in original
> database after a certain number of rows have been added.
>
> TIA
> Roy
>|||Hi,
Sorry, I am using C# and SQL Server 2005 Express edition.

> On the database side you should first be considering what indexes might
> support your application better. Indexes are likely to have much more
> impact on performance than splitting the table up.
What do you mean by indexes. I am not searching the database, as I am only
adding a row sequentially. The only search that I am doing is in my jagged
array and if the search fails to find the element in the array, then it is
added to the array and then to the table. So you see, I am just using the
table as a permanent repository, the search is conducted in the database.
The problem is that I have millions of rows to fill and as the table gets
larger, it slows down drastically.
Any suggestions.
Thanks
Roy
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:xJadnQanRPrN0ePeRVnyjA@.giganews.com...
> "Roy Gourgi" <royng@.videotron.ca> wrote in message
> news:qKrff.23031$AZ3.235633@.wagner.videotron.net...
> I'll skip the question about arrays. There are no arrays in SQL Server and
> you didn't mention what other product you might be using.
> On the database side you should first be considering what indexes might
> support your application better. Indexes are likely to have much more
> impact on performance than splitting the table up.
> --
> David Portas
> SQL Server MVP
> --
>|||Sorry there is an error, as this:
So you see, I am just using the table as a permanent repository, the search
is conducted in the database.
should be:
So you see, I am just using the table as a permanent repository, the search
is conducted in the array.
Roy
"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:ufsff.23045$AZ3.243450@.wagner.videotron.net...
> Hi,
> Sorry, I am using C# and SQL Server 2005 Express edition.
>
> What do you mean by indexes. I am not searching the database, as I am only
> adding a row sequentially. The only search that I am doing is in my jagged
> array and if the search fails to find the element in the array, then it is
> added to the array and then to the table. So you see, I am just using the
> table as a permanent repository, the search is conducted in the database.
> The problem is that I have millions of rows to fill and as the table gets
> larger, it slows down drastically.
> Any suggestions.
> Thanks
> Roy
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:xJadnQanRPrN0ePeRVnyjA@.giganews.com...
>|||"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:ufsff.23045$AZ3.243450@.wagner.videotron.net...
> Hi,
> Sorry, I am using C# and SQL Server 2005 Express edition.
>
> What do you mean by indexes. I am not searching the database, as I am only
> adding a row sequentially. The only search that I am doing is in my jagged
> array and if the search fails to find the element in the array, then it is
> added to the array and then to the table. So you see, I am just using the
> table as a permanent repository, the search is conducted in the database.
> The problem is that I have millions of rows to fill and as the table gets
> larger, it slows down drastically.
> Any suggestions.
> Thanks
> Roy
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:xJadnQanRPrN0ePeRVnyjA@.giganews.com...
>
If you really don't know what indexes are then you have a LOT to learn. I
suggest you purchase a book or take a course in that case. Newsgroups are
not the place for a tutorial.
How do you explain: "I am not searching the database, as I am only adding a
row sequentially" followed by: "the search is conducted in the database"? So
are you searching or not? Common sense says that if you are generating
millions of rows then you will want to search them at some point. For that
indexes will be essential. However, generating large quantities of data on a
desktop spec machine (if that is in fact what you are doing with SQL
Express) is likely to result in a lot of RAM paging to disk. SQL Server
works best with lots of RAM to itself.
David Portas
SQL Server MVP
--|||"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:phsff.23046$AZ3.243820@.wagner.videotron.net...
> Sorry there is an error, as this:
> So you see, I am just using the table as a permanent repository, the
> search is conducted in the database.
> should be:
> So you see, I am just using the table as a permanent repository, the
> search is conducted in the array.
> Roy
> "Roy Gourgi" <royng@.videotron.ca> wrote in message
> news:ufsff.23045$AZ3.243450@.wagner.videotron.net...
>
You mean you persist the entire set of data in the array AND in the
database? That's going to be extremely inefficient. On a single box both
will compete for resources. Search in the DATABASE - that's what it's for.
If you just want a bit bucket then write to a file not to SQL Server.
David Portas
SQL Server MVP
--|||Hi,
If you saw my next post I said that I made an error and that it should have
been the search is conducted in the array and not database.
I will take your advice and try adding some more memory, as I think that may
be one of the problems.
Thanks
Roy

> If you really don't know what indexes are then you have a LOT to learn. I
> suggest you purchase a book or take a course in that case. Newsgroups are
> not the place for a tutorial.
> How do you explain: "I am not searching the database, as I am only adding
> a row sequentially" followed by: "the search is conducted in the
> database"? So are you searching or not? Common sense says that if you are
> generating millions of rows then you will want to search them at some
> point. For that indexes will be essential. However, generating large
> quantities of data on a desktop spec machine (if that is in fact what you
> are doing with SQL Express) is likely to result in a lot of RAM paging to
> disk. SQL Server works best with lots of RAM to itself.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:EdidnYWlGfGpzuPeRVnyrg@.giganews.com...
> "Roy Gourgi" <royng@.videotron.ca> wrote in message
> news:ufsff.23045$AZ3.243450@.wagner.videotron.net...
> If you really don't know what indexes are then you have a LOT to learn. I
> suggest you purchase a book or take a course in that case. Newsgroups are
> not the place for a tutorial.
> How do you explain: "I am not searching the database, as I am only adding
> a row sequentially" followed by: "the search is conducted in the
> database"? So are you searching or not? Common sense says that if you are
> generating millions of rows then you will want to search them at some
> point. For that indexes will be essential. However, generating large
> quantities of data on a desktop spec machine (if that is in fact what you
> are doing with SQL Express) is likely to result in a lot of RAM paging to
> disk. SQL Server works best with lots of RAM to itself.
> --
> David Portas
> SQL Server MVP
> --
>|||Hi,
I only search the value in the array first. If the value is not found, then
I added to the array and then to the table. So you see I need the search the
array to determine whether I have to add it to the table or not.
I thought that it would be much slower if I searched the database rather
than the array, correct me if I am wrong. Furthermore, you say that if I
only want to save it then I should use a normal text file. Would that make
it faster than saving it to a database.
Thanks
Roy

> You mean you persist the entire set of data in the array AND in the
> database? That's going to be extremely inefficient. On a single box both
> will compete for resources. Search in the DATABASE - that's what it's for.
> If you just want a bit bucket then write to a file not to SQL Server.
> --
> David Portas
> SQL Server MVP
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:D-2dnUa4HoOdyePeRVnyuA@.giganews.com...
> "Roy Gourgi" <royng@.videotron.ca> wrote in message
> news:phsff.23046$AZ3.243820@.wagner.videotron.net...
> You mean you persist the entire set of data in the array AND in the
> database? That's going to be extremely inefficient. On a single box both
> will compete for resources. Search in the DATABASE - that's what it's for.
> If you just want a bit bucket then write to a file not to SQL Server.
> --
> David Portas
> SQL Server MVP
> --
>|||"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:gDsff.23050$AZ3.249690@.wagner.videotron.net...
> Hi,
> I only search the value in the array first. If the value is not found,
> then I added to the array and then to the table. So you see I need the
> search the array to determine whether I have to add it to the table or
> not.
> I thought that it would be much slower if I searched the database rather
> than the array, correct me if I am wrong. Furthermore, you say that if I
> only want to save it then I should use a normal text file. Would that make
> it faster than saving it to a database.
> Thanks
> Roy
>
>
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:D-2dnUa4HoOdyePeRVnyuA@.giganews.com...
>

> I only search the value in the array first. If the value is not found,
> then I added to the array and then to the table. So you see I need the
> search the array to determine whether I have to add it to the table or
> not.
>
You can do that in one operation if you use the database:
INSERT INTO your_table (x, y, ...)
SELECT ?, ?, ...
WHERE NOT EXISTS
(SELECT *
FROM your_table
WHERE x = ?
AND y = ?) ;
If x and y are unique and indexed then this is very efficient.

> I thought that it would be much slower if I searched the database rather
> than the array, correct me if I am wrong. Furthermore, you say that if I
> only want to save it then I should use a normal text file. Would that make
> it faster than saving it to a database.
SQL Server is an extremely efficient way to search and process large
quantities of data. What is inefficient is to keep retrieving the data for
processing outside the database and then to persist large data sets outside
the database as well. Why can't you implement whatever your array is doing
in the database itself? Of course I can't be certain that it will be more
efficient that way but I am pretty certain that there must be a more
efficient method than what you are describing.
David Portas
SQL Server MVP
--