Data Access Object for Data Warehouse?
Hi,
Does anyone know how the DAO pattern looks like when it is used for a data warehouse rather than a normal transactional database?
Normally we have something like CustomerDAO or ProductDAO in the DAO pattern, but for data warehouse applications, JOINs are used and multiple tables are queried, for example, a query may contains data from the Customer, Product and Time table, what should the DAO class be named? CustomerProductTimeDAO? Any difference in other parts of the pattern?
Thanks in advance.
SK
[537 byte] By [
sk9a] at [2007-11-26 13:47:11]
# 1
Isn't it where DAOs break? Is there a code generator for your specific product? Do I know what I'm saying?
# 2
DAOs would typically model the data model.
Do you have a data model for the warehouse? The data model would be driven by your requirements.
Note that my very limited knowledge with warehouses would normally suggest that they are used for analysis and custom reporting (which is often just driven by analysis as well.) Thus trying to drive to a data model is not appropriate. And in this case there are already existing tools that are more appropriate as well.
# 3
Hi,
Let say I have the following data model:
http://img187.imageshack.us/my.php?image=piciq7.jpg
And I have query like the following:
SELECT
PRODUCT.brand, -- dimentional attributes
PRODUCT.product,
SUM(SHIPMENT_FACTS.sales_dollars) AS Sum_of_sales_dollars,
SUM(SHIPMENT_FACTS.margin_dollars) AS Sum_of_margin_dollars,
SUM(SHIPMENT_FACTS.margin_dollars) / SUM(SHIPMENT_FACTS.sales_dollars) AS margin
FROM PRODUCT, DATE, SHIPMENT_FACTS
WHERE
PRODUCT.product_key = SHIPMENT_FACTS.product_key AND
DATE.date_key = SHIPMENT_FACTS.shipment_date_key AND
DATE.month_name = 'January' AND
DATE.year = 1998
GROUP BY
PRODUCT.brand,
PRODUCT.product;
How should my DAO look like?
Thanks,
SK
sk9a at 2007-7-8 1:22:38 >
# 4
This isn't how you design a DAO. You don't say "well, here's a query, what shall I put in my DAO?"
What you do is decide what functionality you need from your DAO, THEN you craft a suitable query to back that. And you only make public in your DAO the methods that provide that functionality.
So, what do you want your DAO to do?
# 5
Hi,
What I want is a DAO to handle a JOIN query.
As what I have said, normally a DAO only handles a single database table, for example, CustomerDAO to handle the Customer table.
I am not sure how should the DAO be for queries that handle multiple tables.
Or is there any web pages that I can refer to for DAO for Data Warehouse?
Thanks,
SK
sk9a at 2007-7-8 1:22:38 >
# 6
> As what I have said, normally a DAO only handles a
> single database table, for example, CustomerDAO to
> handle the Customer table.
Not my understanding of the matter. DAOs encapsulate logically coherent sets of data. It's not a one:one correspondence with a particular table.
D.
# 7
> Hi,
>
> What I want is a DAO to handle a JOIN query.
> As what I have said, normally a DAO only handles a
> single database table, for example, CustomerDAO to
> handle the Customer table.
Because normally there is one to one relationship between the data model and the tables.
That is the reason.
I have never worked on an app where that was the only relationship however. I have always had collections and sometimes just associations.
>
> I am not sure how should the DAO be for queries that
> handle multiple tables.
> Or is there any web pages that I can refer to for DAO
> for Data Warehouse?
You are still looking at it from the wrong end.
Your users need to use something. You define that something. Then you use that something to define how you access the data in the database. It could be one table, it could be 10 tables.
And once again I will note that this isn't going to work if there is supposed to be a lot of generality.
# 8
In my opinion, there are no differences in the Data Access Object design pattern which have any thing to do with any characteristic of its implementation or the storage format of the data the pattern is designed to function with.
The core pupose of the DAO design pattern is to encapsulate data access code and separate it from the business logic code of the application. A DAO implementation might vary from application to application. The design pattern does not specify any implementation details. A DAO implementation can be applied to group of XML data files, an Excel-based CSV file, a relational database, or an OS file system. The design is the same for all these, it is the implementation that varies.
The core difference between an operational database and a strategic data warehouse is the purpose of why and how the data is used. It is not so much a technical difference. The relational design may vary however, there may be more tables amd ternary relationships in a data warehouse to support more fine-tuned queries; there may be less tables in a operational database to support insert/add efficiencies.
The DAO implementation for a data warehouse would be based on the model of the databases. However the tables are set up, that is how the DAO is coded.
