Solved

Building a Data Mart

Posted on 2012-04-04
3
1,068 Views
Last Modified: 2016-02-15
Are there any tools that will allow you to select relevant tables in an OLTP database and analyze the data and suggest a layout for a data warehouse/data mart?

How about some recommended reading for a "pulled myself up by the bootstraps" database developer (just learned as I went) on designing and implementing a data warehouse/data mart. I think what we are going for is actually a data warehouse, but the customer wants it called a data mart :-)
0
Comment
Question by:dbbishop
3 Comments
 
LVL 22

Assisted Solution

by:PedroCGD
PedroCGD earned 250 total points
ID: 37810170
Take a look at the main guru on this field here:
http://www.kimballgroup.com/html/booksDWLT2tools.html
Data Mart is a subset of a Data Warehouse
Regards,
Pedro
0
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 250 total points
ID: 37812548
I don't know of any tools that will analyze data and tell you what's relevant - setting up a datamart requires a basic understanding of the data you're working with, including at least an idea of what people want to know (facts/metrics) and what they'll want to compare it by (dimensions).

Before you start putting together a datamart, what are you trying to accomplish? Who is hoping to get insight into your data and what are they hoping it will help them do? Answering those questions can put you on the path to a data warehouse that's going to be used by the audience - even the best-designed datamart/warehouse isn't worth anything is nobody uses it to learn about the customers or the business. Knowing your audience is key to designing something that has what they'll need, and knowing your audience well can help you design something that has things in it they don't even know yet that they want.

Though it may not be the best place to start, you could try taking a look at your largest table - which table in your database has the most records in it (that's not a log table). In my experience, databases tend to have a couple of very large tables and a large number of tables that support them, meaning that your data is all aggregated into a single (or few) locations. Start there - see what numbers are in it or what you could aggregate by, and that becomes your first fact table.

Is the target audience using any pivot tables to do analysis? Though not an exact comparison, they can be a good place to start as well - since the basic data-viewing functionality of SSAS is using a pivot, it can be a big insight into what they want to know. From there, you can find the facts (the data measures inside their pivot tables) and the dimensions (the details along either axis) that they're most interested in.
0
 
LVL 15

Author Closing Comment

by:dbbishop
ID: 37814652
Well, wish we knew for sure what they want to do :-) Basically, they want a repository for historical data. Not a huge amount of financial data involved, more service related (government program administered by the states). Most reporting will be on participation but also some caseload based reporting.

I was hoping that if you had a properly indexed database with FK relationships, there would be a utility that would analyze the data and give a good starting-point for the data warehouse schema that you could then fine-tune.

Guess I'll have to jump in and get my feet wet.

Thanks for the feedback.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now