what is a data warehouse

[tizwaz]

I posted this question in Access - other topics but was referred to this forum - hope no-one minds the double posting.

I am thinking of applying for a job as an Access database developer and one of the desirable skills listed is knowledge of data warehouses. Does anyone know what this means I have not come across this term before

 

[RiverGuy]

A data warehouse consolidates a company's information into a central database system for business intelligence and reporting. Most of the information in it comes from various systems the company uses. The data isn't simply copied over, but it's cleansed and transformed to fit into the model of the business as exists in the data warehouse. Quite often, the data warehouse is a denormalized database schema. The data warehouse will almost always reside in a client/server database. I could see them listing data warehouse skills if they use Access as a front end tool for querying the data warehouse.

 

[SQLSister]

Rather than using the normal relational model of an OLTP (online transaction processing), a data warehouse often uses what is a called a star schema. It is designed specifically for fast access to data instead of adding and modifying data. Often the data is not real-time. The data warehouse usually has a mightly or weekly job that populates the new data. Data warehouses are usually used for high level management analysis of data and reporting.

"NOTHING is more important in a database than integrity." ESquared

 

[tizwaz]

Thanks for this. Now I don't know whether to apply or not. This doesn't sound like anything I've done before. I've only had experience of creating standalone databases. Do you think it would be difficult to pick up?

 

[lespaul]

I would say that if you are comfortable writing queries (not using the Query Grid) and extracting aggregates (count, sum, avg) from data that you should be able to pick up on it. The reason to be for a data warehouse is usually reporting...if you can write queries and extract the data, then you should be ok.

Leslie

Have you met Hardy Heron?

 

[RiverGuy]

If it says desirable and not a must have, then you should be good.

As far as picking it up easily, it depends.

If they are looking for those who have created queries and reports against data warehouses, then your Access skills could translate over fairly easily. Many times the queries written against star schema data warehouses are much easier to write than queries against 3NF databases.

If they are looking for someone who has been involved with the data modeling, architecture, requirements gathering, ETL or administration of a data warehouse, then those skills could take a while to obtain.

 

[Dollie]

Having just spent an entire semester on this subect, I found the easiest way to describe a data warehouse toward the end of the semester when working on my research paper.

MS-SQL is a data warehouse. It contains databases for other pieces of software that are not related to each other even though they can be relational. It's a way of keeping all of your data in one place so it can be utilized by other report-writing software that can be disguised as CRM, SCM, or ERP.

There are many other types of data warehouses and methods of data warehousing, I'm just trying to help give simplest explanation of what one is.

 

[SQLSister]

I would also question how much it really is a true data warehouse as Access would not be the interface of choice for that since it is hard to optimize the queries without the query optimization tools of SQl Server or Oracle. Plus usually data warehouses are involved with very large amounts of data (otherwise, no need to separate data out from OLTP system to improve query performance) which again makes Access an unlikely choice. If however, they have data currently in Access and are finding it doesn't meet their needs, you may luck into a job where you get to upgrade your skills to a true Enterprise level database.

As to whether you should apply, I think in this case, you shoudl really evelauate your query writing skills and your report creation skills. Unless they are requiring you to design the warehouse from scratch, you should be fine with just these. Even then a company using Access for this function isn't going to find a lot of people who have true data warehousing experience (most true BI(Business Intelligence) or data warehousing specialists use SQl Server or Oracle or some other very large Enterprise type database), so I'd still go for it and learn something new that you can maybe leverage into something even better.

"NOTHING is more important in a database than integrity." ESquared

 

[SQLSister]

About Data Warehouses

A data warehouse is often used as the basis for a decision support system. Data warehouses are designed to overcome problems encountered when an organization attempts to perform strategic analysis using the same database that is used for online transaction processing (OLTP).

OLTP systems typically:

Support large numbers of concurrent users who are actively adding and modifying data.

Represent the constantly changing state of an organization but don't save its history.

Contain large amounts of data, including extensive data used to verify transactions.

Have complex structures.

Are tuned to be responsive to transaction activity.

Provide the technology infrastructure to support the day-to-day operations of an organization.

Difficulties often encountered when OLTP databases are used for online analysis include the following:

Analysts do not have the technical expertise required to create ad hoc queries against the complex data structure.

Analytical queries that summarize large volumes of data adversely affect the ability of the system to respond to online transactions.

System performance when responding to complex analysis queries can be slow or unpredictable, providing inadequate support to online analytical users.

Constantly changing data interferes with the consistency of analytical information.

Security becomes more complicated when online analysis is combined with online transaction processing.

Data warehousing provides one of the keys to solving these problems, by organizing data for the purpose of analysis. Data warehouses:

Can combine data from heterogeneous data sources into a single homogenous structure.

Organize data in simplified structures for efficiency of analytical queries rather than for transaction processing.

Contain transformed data that is valid, consistent, consolidated, and formatted for analysis.

Provide stable data that represents business history.

Are updated periodically with additional data rather than frequent transactions.

Simplify security requirements.

Provide a database organized for OLAP rather than OLTP.
A data mart is a special form of data warehouse, typically containing a topic-oriented subset of enterprise data appropriate to a specific business function.

Thought this info from BOL might help you understand some of the basic issues of Data Warehousing.

"NOTHING is more important in a database than integrity." ESquared

 

[tizwaz]

Thanks to all of you - its given me a lot more of an insight than I had before.

 

[JCreamerII]

And all this time I thought it was a place you could go and get good data cheap.

Jim C.

 

[Stella740pl]

You also might be interested to know that Tek-Tips has forum353.