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 :-)
LVL 15
dbbishopAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 McCauleyEnterprise 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.