6

Pardon me if this has already been asked (I know very little about Data Warehouse/BI and have yet to master the keywords).

I have a table that grow by more then 100 000 rows per day, each row having a timestamp and multiple information about an item (dimensions, weight,color,etc). Individual data can be useful for roughly a month after this period we are only interested in aggregations. I have a dedicated software that allow a more detailed visualisation of individual rows and mainly use PowerPivot for my reporting needs.

I could come up with an SQL query that would fill a new table daily: In which I would have a row for each hour/item/batch and I would summarize the information (sum/average/stddev/etc.)

Within a day my script would be up and running and I could use powerpivot against this new table. All this while staying where I'm comfortable: plain old SQL.

From the few information I gathered reading about DataWarehouse and BI, what I'm about to do sounds a lot like creating dimensions and facts. My question therefore: is it worthwhile to investigate further in that direction (BI) or since my problem is relatively simple I would do better staying in a relational database.

N.B. Reports that are being produced are usually linked against another database to produce more meaningful informations. Task that is very well accomplished by Powerpivot.

Benoittr
  • 4,091
  • 4
  • 27
  • 38

3 Answers3

3

Datawarehouses are normally implemented in relational databases, so your existing skills will still be usable.

Given that you have expressed an interest in the dimension/fact table approach to datawarehousing, the canonical books on this approach are usually considered to be:

  • The Date Warehouse Toolkit (Kimball, Ross)
  • The Date Warehouse Lifecycle Toolkit (Kimball, Ross, Thornthwaite, Mundy, Becker)

(The former has more of a technical focus, while the latter approaches the subject from a wider lifecycle management viewpoint.)

Implementing DWHs can be time-consuming, so it may be worth continuing with your existing approach even if you decide to build a DWH.

  • If I could accept all the answers, I would, since they all brought up different aspects that helped me take the decision (lets keep it simple for now). But since this one pointed me toward some more reading I'll go ahead and accept this one. Thanks – Benoittr Mar 26 '12 at 19:07
2

Good news: it sounds like you already have a data warehouse. "Data warehouse" is a very generic term, with no real formal definition - it pretty much means whatever you want it to.

Commonly accepted characteristics are:

  • Data warehouses do not run on the operational databases
  • Data warehouses schemas are optimized for querying, not for "normal form" compliance
  • Data warehouses are populated by "Extract, Transform, Load" proceses (ETL).

It sounds like you're already doing all of that. If there are no business requirements to change, I'd leave it as it is. If your business users are asking to create their own queries, using different levels of aggregation, filtering, or granularit, a star schema may be the way to go.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
1

The most effective solutions are those which are simple, adequate to meet existing needsand stay within available skillsets.

I agree that this approach works well for your situation an if it provides the reports and information you need then its worth starting this way. If you need more complex functionality later then you can go for more complex BI

Stephen Senkomago Musoke
  • 3,528
  • 2
  • 29
  • 27