design question - parent child tables & identity columns

I don't know if this is the right forum but...

In a parent/child table structure (order/orderdetail) I have used identity columns for the orderdetail or compund primary keys. I find a single identity column on the detail table easier to manage (with a fk to the parent) but what ends up bieng easiest for the user is to have an order (say #3456) and detail items listed sequentially from 1 to n. This reflects a compound key structure but generating the 2nd field is a pain. Is there any way to tie an identity field to the parent key so that it will generate this number for me automatically?

Nope. But, that also brings up a very interesting point. Why is your user directly querying the data within the table in such a way that they would even see this value? It's an internal structural element and should not have any business meaning at all. Put an identity on the orderdetail table, link it to it's parent OrderID in the orders table. Now, in the application which your users should be using to work with data, yank the order detail rows out, display them in ascending order of the OrderDetailID, and then on the application tier just number the lines from 1 - N. The user now gets the display that they want and you aren't tying a structural element to a business meaning.

Why wouldn't you want to give this key a business meaning? Very simple. User enters an order with 10 line items. They then decide to delete lines items 3, 6, and 7. If you are physically storing this data, you would then have to update the foreign keys and then the primary key (parent) upon which they depend which is something that should never be done.

|||

The users are not directly querying the data. The might see it in a report. Users see keys all the time and they are very useful. An invoice number is a key, a UPS tracking number is a key, a Bloomberg trouble ticket is a key. They are very useful in tracking down problems. When these objects have detail items users do communicate using the codes ("I have a problem with order #2356, item #5"). I was trying to find a way to make this easy by keeping the 2nd number low ("I have a problem with order #2356, item #58694937").

I wouldn't need to delete the rows, I could mark them as deleted and strike through them on a report or just eliminate them from the report and have gaps in the numbering.

|||So the answer to that is you have to custom code that yourself which is also going to mean performance, scalability, and concurrency issues.

Design of Scheduled Task data schema

I am designing a table structure for scheduled tasks. Tasks must be
scheduled either on a fixed interval or at specific times. Those specific
times can be either every day or specific days of every w.
I have come up with the plan I'll paste in here, but the NULL's and one
field telling how to interpret data from another table leave me suspecting
there's a better way. I'm designing this from scratch so am free to change
as needed.
Suggestions?
Use Pubs
Create Table SchedTask(
TaskCode nvarchar (6) not null Primary Key,
TaskDescription nvarchar (30) ,
ScheduleType char(1) not null Check ( ScheduleType in ('I', 'D',
'W')), --for Interval, Daily, Wly
ScheduleInterval SmallInt NULL
)
go
Create Table SchedDay(
TaskCode nvarchar (6) Foreign Key References SchedTask (TaskCode),
DayOfW TinyInt not null Check ( DayOfW Between 0 and 7), --0
indicates it doesn't matter, 1-7 map to Sunday-Saturday
TimeOfDay SmallDateTime not null
)
go
--Add a task to repeat every 10 minutes
Insert Into SchedTask (TaskCode, TaskDescription, ScheduleType,
ScheduleInterval)
Values ('10M', 'Repeat every 10 minutes', 'I', 10)
--Add a task to occur at 9:00 AM every day
Insert Into SchedTask (TaskCode, TaskDescription, ScheduleType,
ScheduleInterval)
Values ('9D', 'occur at 9:00 AM every day', 'D', NULL)
Insert Into SchedDay (TaskCode, DayOfW, TimeOfDay)
Values ('9D', 0, '9:00')
--Add a task to occur at 9:00 AM every Monday and 11:45 every Friday
Insert Into SchedTask (TaskCode, TaskDescription, ScheduleType,
ScheduleInterval)
Values ('2W', 'Do this twice a w', 'W', NULL)
Insert Into SchedDay (TaskCode, DayOfW, TimeOfDay)
Values ('2W', 2, '9:00')
Insert Into SchedDay (TaskCode, DayOfW, TimeOfDay)
Values ('2W', 6, '11:45')
--Show the data
Select * From SchedTask
Select * From SchedDay
--Now clean up the DB
drop table SchedDay
go
drop table SchedTask
go
Thanks.
Daniel Wilson
http://Embtrak.com Development TeamSee if you can get some inspiration from the sysjobschedules database in the
msdb database. It's the table that SQL Server Agent uses to schedule jobs
and it is documented in SQL Server Books Online.
Jacco Schalkwijk
SQL Server MVP
"Daniel Wilson" <d.wilson@.Embtrak.com> wrote in message
news:edmiKCcyFHA.1252@.TK2MSFTNGP09.phx.gbl...
>I am designing a table structure for scheduled tasks. Tasks must be
> scheduled either on a fixed interval or at specific times. Those specific
> times can be either every day or specific days of every w.
> I have come up with the plan I'll paste in here, but the NULL's and one
> field telling how to interpret data from another table leave me suspecting
> there's a better way. I'm designing this from scratch so am free to change
> as needed.
> Suggestions?
> Use Pubs
> Create Table SchedTask(
> TaskCode nvarchar (6) not null Primary Key,
> TaskDescription nvarchar (30) ,
> ScheduleType char(1) not null Check ( ScheduleType in ('I', 'D',
> 'W')), --for Interval, Daily, Wly
> ScheduleInterval SmallInt NULL
> )
> go
> Create Table SchedDay(
> TaskCode nvarchar (6) Foreign Key References SchedTask (TaskCode),
> DayOfW TinyInt not null Check ( DayOfW Between 0 and 7), --0
> indicates it doesn't matter, 1-7 map to Sunday-Saturday
> TimeOfDay SmallDateTime not null
> )
> go
> --Add a task to repeat every 10 minutes
> Insert Into SchedTask (TaskCode, TaskDescription, ScheduleType,
> ScheduleInterval)
> Values ('10M', 'Repeat every 10 minutes', 'I', 10)
> --Add a task to occur at 9:00 AM every day
> Insert Into SchedTask (TaskCode, TaskDescription, ScheduleType,
> ScheduleInterval)
> Values ('9D', 'occur at 9:00 AM every day', 'D', NULL)
> Insert Into SchedDay (TaskCode, DayOfW, TimeOfDay)
> Values ('9D', 0, '9:00')
> --Add a task to occur at 9:00 AM every Monday and 11:45 every Friday
> Insert Into SchedTask (TaskCode, TaskDescription, ScheduleType,
> ScheduleInterval)
> Values ('2W', 'Do this twice a w', 'W', NULL)
> Insert Into SchedDay (TaskCode, DayOfW, TimeOfDay)
> Values ('2W', 2, '9:00')
> Insert Into SchedDay (TaskCode, DayOfW, TimeOfDay)
> Values ('2W', 6, '11:45')
> --Show the data
> Select * From SchedTask
> Select * From SchedDay
> --Now clean up the DB
> drop table SchedDay
> go
> drop table SchedTask
> go
> Thanks.
> Daniel Wilson
> http://Embtrak.com Development Team
>
>|||But whatever you do, don't use ints for date and time values. :-)
A
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23gJJ2VcyFHA.2932@.TK2MSFTNGP10.phx.gbl...
> See if you can get some inspiration from the sysjobschedules database in
> the msdb database. It's the table that SQL Server Agent uses to schedule
> jobs and it is documented in SQL Server Books Online.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Daniel Wilson" <d.wilson@.Embtrak.com> wrote in message
> news:edmiKCcyFHA.1252@.TK2MSFTNGP09.phx.gbl...
>|||Thanks. I'll think that over.
And I'll definitely NOT use integers for times!
dwilson
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23gJJ2VcyFHA.2932@.TK2MSFTNGP10.phx.gbl...
> See if you can get some inspiration from the sysjobschedules database in
the
> msdb database. It's the table that SQL Server Agent uses to schedule jobs
> and it is documented in SQL Server Books Online.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Daniel Wilson" <d.wilson@.Embtrak.com> wrote in message
> news:edmiKCcyFHA.1252@.TK2MSFTNGP09.phx.gbl...
specific
suspecting
change
>

Design advice for an education data warehouse

I’m not sure whether this ought to be in the Architecture or

SQL Analysis Services forum.I am after

some initial advice about a suitable structure for a data warehouse which will

be used to build OLAP cubes AS2005.

I work in a county education department.Each year we create/update a set of reports

and procedures which provide schools with online access to many of the

statistics for their school compared to district wide and to national targets,

etc.

After this year’s, which is my second at creating these

report/data, I can’t help but think that the whole thing is screaming out for a

Data Warehouse and Cube data to report on.

I may be wrong on that, as I’m still only doing the learning regarding

AS2005 and haven’t touched a previous version of it.I’m currently working through this book:

Microsoft SQL Server 2005 Analysis Services Step by Step - ISBN

0735621993.But I’m willing to bet that

this should be the way forward.Not only

for the actual schools viewing the data, but also for all those staff who work

at the county level and have to inspect/report on these schools and the

county’s performance.The only thing is

that most examples are stated within companies which have sales as a primary

measure, however I’m not sure education grades, targets, etc fall into the same

scheme.So I can’t use an example

structure to help get me started.

It isn’t something that I’ve got management backing for yet,

as it’s still pretty much just my own and a colleagues’ thoughts at the

moment.Also until I know more about it

myself then I wouldn’t feel comfortable taking my case to management.I feel I will be better to create a simple

version to demonstrate and show the benefit and power of such a system, to

those who will give the backing to the project.

The main central application, which is used in the county,

contains most of the information needed.

And several other systems also contain additional data which would be

useful.All based around a

Unique_Pupil_ID.

Current transactional database

The main transaction database I would be taking data from

has a main student table, then it has a student_results table.The student table also has many lookup type

tables related to ethnic origins, deprivation codes relating to income/student

address, etc.The kind of things that

would be useful when doing analysis on students.As well as at a more broad level, being able

to compare schools to each other at various subjects/overall/etc.

So the student table contains the information about the

student, but not results data.

The results table data is similar to below, but obviously

with more to it than just these fields:

Student_ID

Subject_ID

Grade

GradePointsEquivalent

ExamYear

Then there is obviously a lookup for the subject name.

There are also things like average/points/score per student/year

which aren’t a subject score, but more of a calculated field.So where ought that data to be located table-wise?

There would be a lot more to a total DW solution for this

environment, but I wanted to start with the basics so I have somethign to demonstrate.

Can anyone offer my any advice regarding this? and a possible design structure for the warehouse with regards to which ought to be fact or dimension tables.

Or do you think I'm barking up completely the wrong tree thinking that this would be a good solution?

Thanks for taking the time to read this less than short post.

Kind regards,
David

Hello. I have searched on the subject "education data warehouse" and found some suppliers and solutions in this area.

You design is a good start. A student table, with facts about the student, and a fact table with each students results, by each test or whatever. You will also need a tiem dimension, a test dimension(if these tests are general). Perhaps a school and a teacher dimension can enhance the analytic value of your solution. Students change schools and teachers and teachers change schools.

In the fact table you will normally only put base measures that can be used for calculations. You do the calculations in a cube.

Your problem have a lot in common with analysis when you try to see customer profiles and their buying patters, so you can have some help with sales examples.

HTH

Thomas Ivarsson

|||Thanks for your reply Thomas.

This was my initial thought on the design (although there are a fair few columns that I will not need in the final version, which are included in that diagram at the moment). See link below.

>>Structure<<

What do you think? Anything that jumps out at you regarding it?|||

Hello. The structure is fine for a data warehouse. If you build a datamart on top of this, by using views or new tables I recommend to reduce the number of joins in your snowflake.

Have a look here for more design tips(www.kimballgroup.com)

Regards

Thomas Ivarsson

|||I have made them into more of a star schema that a Snowflake and that has certainly helped.

However, I have a new issue. My understanding of OLAP & AS2005 is still growing, so it's possible I've got the wrong idea here, but...

The student grades are not always numeric, in many of the subjects they are graded A-G etc. They all have points equivalent, but it would be good to look at the grades too.

Common requests about the data are how many students achieved 5 A-C grades or 5 A-G etc. And other bits of analysis that are done around grade.

So some of the data needs to be analysed as stated here and some by numeric points. Can anyone help me as to how I could structure this part of it or achieve such a result?

I'm at a bit of a loss as to how.

Thanks,
David|||

You can used named members/sets for A-C and A-G type grades. For numeric data, you can either create a band dimension or use discretization methods in SSAS. You can search for these techniques in Books online.

One other point in your structure, for languages, you can create one physical dimension and use 'role playing dimensions'.

|||

Hello again. Build a separate dimension table for the grades(A-G), make a foreign key to the fact table and add a measure, called NumberOfGrades to the fact tables.

In this way you should be able to use sum as the aggregation method. This measure can then tell you how many (A-G) grades a student, teacher, school (and so on) that exist in the fact table.

HTH

Thomas Ivarsson

Design a Tree in a blank Report

I'm creating a report like a Tree structure.

Area1 Total Modifications in Area1
Type1 Total Modifications in Type1 in area1
Sub Type1 Total Modifications in Sub Type1 in Type1 in area1
Sub Type2 Total Modifications in Sub Type2 in Type1 in area1

Type2 Total Modifications in Type2 in area1
Sub Typex Total Modifications in Sub Typex in Type2 in area1
Sub Typex2 Total Modifications in Sub Typex2 in Type2 in area1

Area2
Type1 Total Modifications in Type1 in area2
Sub Type1 Total Modifications in Sub Type1 in Type1 in area2
Sub Type2 Total Modifications in Sub Type2 in Type1 in area2

Type2 Total Modifications in Type2 in area2
Sub Typex Total Modifications in Sub Typex in Type2 in area2
Sub Typex2 Total Modifications in Sub Typex2 in Type2 in area2

I used the Report Wizard, created a Tabular report, did the grouping, and choose the Table Layout as Stepped and enabled the Drilldown and subtotal and it is works perfectly. When clicked on Area1, it shows the Type1 and Type2, and when clicked on Type shows the Sub type..

Now the problem is i have all the complex SQL code for this in a Stored Procedure.

When using Stored Procedure, Report Wizard cannot be used. So, How do i achieve similar Stepped Tabular report with drilldown feature when i create a blank report with Stored Procedure in Dataset ?

I used Table control and added two groups for Area and Type. I made the Area as Parent for Type and tried Hide and visibility Toggle option but i got error
"Toggle items must be text boxes that share the same scope as the hidden item or are in a scope that contains the hidden item, and cannot be contained within the current report item unless current grouping scope has a Parent."

Is this the way to do it or am i missing somthing in this?

Any help with steps appreciated.

Thanks.

Got it.
I created a report in Report Wiz using some query, and then i created a Dataset with Stored Proc and changed the dataset name for Table in Properties and it worked.