Datamart

Hi,
I need some advise. We are in he process of designing our first data
mart. This Datamart will consists of sales orders activity. Sales
orders have lines created, deleted and inserted on a daily basis as
long as the sales order is open.
It has been suggested that we do triggers the these tables and then
populate the Datamart. Which the DBA is not thrilled with. is there
another way to do this wihtout triggers.?
The data needs to get updated daily and the volume is about 500K rows.
The data is being sourced from an ERP and the DB is oracle 9i. We also
have the option of using SQL Server if need be. Our BI tool is Cognos
and ETL tool is not an option since our data tranformations are very
simple.
Please share your experience.
Regards
Ather
Ather
I only put complete / closed / posted items into my DWH unless the business
can justify why they want incomplete / incorrect data in the DWH, so i would
only transfer closed orders
Use MS- SQL as your DWH server as you get DTS (Data Transformation
Services) with it, which can be used as a lightweight ETL tool
Create some DTS packages to update your DWH at the end of each day ( or any
other viable interval) with the new closed orders
Unless you have paid for / are forced to use COGNOS look carefully at
reporting services and analysis services as your BI tools
Regards
Reg Besseling
"Ather Mian" <ather@.nowwhere.com> wrote in message
news:eIdrqSV4EHA.2180@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I need some advise. We are in he process of designing our first data
> mart. This Datamart will consists of sales orders activity. Sales
> orders have lines created, deleted and inserted on a daily basis as
> long as the sales order is open.
> It has been suggested that we do triggers the these tables and then
> populate the Datamart. Which the DBA is not thrilled with. is there
> another way to do this wihtout triggers.?
> The data needs to get updated daily and the volume is about 500K rows.
> The data is being sourced from an ERP and the DB is oracle 9i. We also
> have the option of using SQL Server if need be. Our BI tool is Cognos
> and ETL tool is not an option since our data tranformations are very
> simple.
> Please share your experience.
> Regards
> Ather
>
|||Identify all the OPEN orders (order status or some other combination of
field(s) ) which will narrow the list of orders that will get affected in
your datamart. For these open orders:
(1) Delete the orders from your datamart.
(2) Re-insert the data about the open orders.
I am making an assumption that you do not have track the order status
history itself. I am also making an assumption that data about "closed"
orders does not change and/or you do not want to change them in the datamart
in any way.
Hope this helps.
Rangarajan Suresh
www.picarossolutions.com
*********************
"Ather Mian" wrote:

> Hi,
> I need some advise. We are in he process of designing our first data
> mart. This Datamart will consists of sales orders activity. Sales
> orders have lines created, deleted and inserted on a daily basis as
> long as the sales order is open.
> It has been suggested that we do triggers the these tables and then
> populate the Datamart. Which the DBA is not thrilled with. is there
> another way to do this wihtout triggers.?
> The data needs to get updated daily and the volume is about 500K rows.
> The data is being sourced from an ERP and the DB is oracle 9i. We also
> have the option of using SQL Server if need be. Our BI tool is Cognos
> and ETL tool is not an option since our data tranformations are very
> simple.
> Please share your experience.
> Regards
> Ather
>
>
|||What Will happen to orders that change from open to closed ? it seems to me
that they will get "Lost" until the next full refresh
Regards
Reg Besseling
"Rangarajan Suresh" <RangarajanSuresh@.discussions.microsoft.com> wrote in
message news:DC96DE91-8994-41BD-A84F-5FED65FBA3D6@.microsoft.com...[vbcol=seagreen]
> Identify all the OPEN orders (order status or some other combination of
> field(s) ) which will narrow the list of orders that will get affected in
> your datamart. For these open orders:
> (1) Delete the orders from your datamart.
> (2) Re-insert the data about the open orders.
> I am making an assumption that you do not have track the order status
> history itself. I am also making an assumption that data about "closed"
> orders does not change and/or you do not want to change them in the
> datamart
> in any way.
> Hope this helps.
> Rangarajan Suresh
> www.picarossolutions.com
> *********************
>
> "Ather Mian" wrote:
|||I guess I should have also addressed the issue of "closed orders" along with
"open orders" -- The closed orders are to be loaded as inserts into the
datamart. They do not necessarily fall under the issue of "replacing"
existing open orders that may have changed in some fashion. Since we are
deleting existing open orders from the datamart, the same order will not go
into the datamart as it is no longer open.
Sorry for the confusion.
Rangarajan Suresh
www.picarossolutions.com
*********************
"Reg Besseling" wrote:

> What Will happen to orders that change from open to closed ? it seems to me
> that they will get "Lost" until the next full refresh
> Regards
> Reg Besseling
> "Rangarajan Suresh" <RangarajanSuresh@.discussions.microsoft.com> wrote in
> message news:DC96DE91-8994-41BD-A84F-5FED65FBA3D6@.microsoft.com...
>

datafile usage

If a database consists of more than one datafile, how does SQL Server use the space in these datafiles ?, does it fill up the first one then move to the next and so forth, or does it use up pages across all the files evenly ?.In a group, the files are all filled proportionnally to their size.

For example : a group with file1 = 100 MB and file2 = 200 MB
If you add 3 MB, 1 MB is added to file1, and 2 MB to file2.|||Thanks for the information|||You're welcome ! :)

Database work

Hello!I'm new here on the forum!
I have a work to do on a class which consists on creating a database and this's a work by checkpoints,which means,I have a limit date to give away part of the work I've done until the next checkpoint.The prob lem is,I don't know where to start!The first thing I'm asked to do is to propose a theme(Managing a bar was what I had in mind) and have its preliminary specification.What do they want to say with 'preliminary specification'?Next I'm supposed to give away the inicial specification and the applicational funcionalities that uses this database...Again I'm not sure what they mean by that...Any help is extremely appreciated!Which school is it? Full name and address, please.

Why? Perhaps someone (among Forum members) goes to that school, or even works there. Then it will be easier to answer such a question. Because, if YOU, who attend those classes (or, at least, you are supposed to) don't have a clue what your teacher wants, how do you expect US to read his mind?

Whatever we think "preliminary specificatons" or "functionalities" are, it is your teacher who EXACTLY knows what he wants you to do. Therefore, I'd suggest you to take a walk, knock on his door and get all required information there.

Then return back and show what you've already done, explain problems you have and someone will surely help you. But, it is a little bit impolite to expect Forum members to do your school assignment from the beginning. Gosh, you don't even know how to start!

"Managing a bar" could be OK if you know to manage a bar (it is not just "a barman pours a drink into my glass"), i.e. know business rules. If not, you'll have to spend some time to get into the business - you can't design something you don't know at all. Therefore, choose a theme you are familiar with.|||To make things easier for you, select a data model here (http://www.databaseanswers.org/). ;)

PS: You may need to mention the site/author in the 'bibliography' or 'references' section of your paper.|||At least you can explain all the time you spend in the bar as "research".

It could have been worse. The assignment could have been for a sewage treatment plant.|||Well we are freely to choose a theme we like...|||After giving some more thought to the "Bar management" theme I've decided to change themes,not only because I'm on a tight schedule and I need to send the theme by mail to the teacher until Monday, but also because it would involve lots of research(and field research) and it's not one of the most easiest subjects to deal with.Also the teacher values originality and a database about a bar is something overused this days(at least I see it that way).
After someone suggested to speak with the teacher about what he really wanted to say with "preliminar specification" ,I mailed my teacher with the question and got a response:

"A preliminar specification could be,for example,

'Caf Management Application' will allow:

- do the stock management of all products(inserting shops and sales,deliveries,invoices,etc.)
- staff management(personal data,contracts,prizes,remunerations)

'Caf Management Application' should have:

- an application of POS(cash register) to be used by the waiter
- a backoffice application to be used by the cafe manager"

My new theme deals with 'Dog's house management'.This's what I've got so far concerning my preliminar specification:

'Management application of a dog's house' will allow:

- when the owners are away the dog's house would be their new home.
- register the dogs' breed and type
- manage the the type of treatments and medical care needed
- register information about the dogs(name,age,color,gender,etc.)
- manage the dogs' family details(name,adress,city,state,contact,etc.)
- manage the vacancies and reservations

Does this preliminar specification looks ok?Should I include something more?Also, what does the term "backoffice" means?
Any help on this is greatly appreciated!Thanks in advance!:)|||Any help,please?