Solved

Building a Data Mart

Posted on 2012-04-04
3
1,093 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Count with a subquery showing details 10 57
ms sql + get number in list out of total 7 44
tempdb log keep growing 7 43
Database Owner 3 12
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

685 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