• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1166
  • Last Modified:

Building a Data Mart

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
dbbishop
Asked:
dbbishop
2 Solutions
 
PedroCGDCommented:
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
 
Ryan McCauleyData and Analytics ManagerCommented:
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
 
dbbishopAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now