?
Solved

Building a Data Mart

Posted on 2012-04-04
3
Medium Priority
?
1,125 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 22

Assisted Solution

by:PedroCGD
PedroCGD earned 1000 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 1000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

718 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