quick question on data warehouse analysis and design?

ocean2005 · #1 (**permalink**) 01-21-05, 10:57

Hi

Sorry for this very newbie question but:
I want to analyse a warehouse system for a factory. I want to keep the info about the items that get into the warehouse and leave it every day. The structures I need to use must be optimal for storage space and time for different searches and report making. The reports are to be made for the items entering and leaving the warehouse at random dates( random access) and weekly/monthly(range query).
Well the solution seems to be using a B*Tree. But I'm still in doubt if I'm taking the right approach. I googled but I mostly came across stuff about data warehousing (which wasn't very useful to me at first look) and some stuff about database design (RDMS, ODMS) which I'm very new to.

Any pointers and directions or direct links(I'm on a slow connection) on what to use (what indexing?) and that help me with the analysis would be mostly appreciated.

Vmusic · #2 (**permalink**) 01-22-05, 00:54

Hi,
There are TONS of sources for data warehousing. The concept behind a data warehouse is to gather data from systems used to run your day to day operations. Combine this data together from these different systems in a manner that lets you see a picture of your business across multiple systems over time.

That's a very vague concept I know(Inmon would be proud).

There's a good chance you might have a separate inventory and purchase system. So you might use your data warehouse to relate these together and provide snapshots of this data for executive decions.
A snapshot is a picture or information at a certain point in time, like what is the status of our inventory for each product or product line 'monthly' (one snapshot), 'weekly' (a different snapshot) or 'daily' (yet another point in time).

OK.. It's midnight in Columbus and I'm drunk so that's all the info you're getting.

Good Luck
Vmusic

Pat Phelan · #3 (**permalink**) 01-22-05, 10:13

Quote:

Originally Posted by Vmusic

There are TONS of sources for data warehousing.

That's a great point, but unfortunately ocean2005 is looking for information about data for warehouses, not data-warehouses! That is a very different kind of question!

ocean2005: Check Rudy's post about cat food in the SQL forum. He's got a few references that might help you there.

-PatP

ocean2005 · #4 (**permalink**) 01-24-05, 09:34

I'm sorry that I made my question in a vague way but what I exactly want is as follows:
I have a warehouse (not a data warehouse I think!) to which some items enter and leave everyday.
I want to design an optimal index for it so that I can run different queries on it including random and range queries(snapshotting)
Here's an example table: (Let me know if you have a better design for the table too!)

date name no_in no_out
5/25/2005 item1 10 5
5/25/2005 item2 0 3
5/25/2005 item3 0 0
5/26/2005 item1 1 1
5/26/2005 item2 10 2
.
.
7/10/2005 item1 2 2
7/10/2005 item2 3 0

Sorry if it doesn't look ok!

(Random access)
(5 sample queries)
On (1)Day X which items have (2,3)left/entered the warehouse (4) how many
(5)On Day X which item has left/entered the warehouse MOST/least?

(Range Query)
In Week/Month X which item has MOST/least left/entered the warehouse?

Well again after a lot of googling I came across this as the solution:
index intersection with B-trees (B*Trees)(Which in turn seems relevant to Composite B*Trees/ Multiple secondary B*Trees).
But how?
Might a B+Tree be of help ( I doubt!)

BTW I'm not after its implementation (SQL, C++ and such) but just the Analysis on paper.

Thanks beforehand

r937 · #5 (**permalink**) 01-24-05, 12:01

sounds like homework

okay, let's try questions 1 though 4 all in one query

On (1)Day X which items have (2,3)left/entered the warehouse (4) how many

Code:

select iname
     , sum(no_in) as how_many_in
     , sum(no_out) as how_many_out
  from inventory     
 where idate = '2005-01-24'
group
    by iname

note i changed date and name to idate and iname because the former were too close to being actual reserved words

see? no mention of bees or trees!!

ocean2005 · #6 (**permalink**) 01-24-05, 13:15

Yes, right it's a homework but I'm not asking for the complete solution but just pointers and after all I have done my best so far.

As for your answer:

It is very reasonable but what is beyond this piece of SQL code, matters to me. As I mentioned no implementation of any sort is required and I think for a course called Data Storage and Retrieval with Michael J. Folk's book on File Structures as the reference which hasn't mentioned a word on the Composite B*Tree it's not very easy.

BTW I found this idea (index intersection with B*Trees) on a lecture on data warehousing from stanford.edu:
http://www.stanford.edu/class/cs345/slides/Lecture8.ppt

Anyway thanks for your time.

Pat Phelan · #7 (**permalink**) 01-24-05, 13:41

I'm still not clear on what you want.

Getting to the B+ tree level is far beyond the scope that would normally be used by a data modeler or developer. This is actually at a lower level than even a true DBA would be likely to operate, more on the level of a database engine support engineer. I'd be very surprised if an assignment really wanted to know that kind of information.

Are you looking for a proposed schema? Do you want an overview of the queries needed to answer specific questions (which would depend completely on the schema)? Did I miss your point completely?

-PatP

Vmusic · #8 (**permalink**) 01-24-05, 23:37

Wellllll
It could be a data warehouse - see attached -

Just the Facts Maam, Just the Facts
Vmusic

ocean2005 · #9 (**permalink**) 01-25-05, 08:24

Can you elaborate?

certus · #10 (**permalink**) 01-26-05, 02:27

Look, go to your instructor and determine whether you are creating an online transaction processing database or a online analytical processing database (data warehouse).

Once you know that you can determine the most efficient structure for your queries. Otherwise we are speculating.

Internet Services

Web Development

Digital Marketing

Consumer Tech