makeing a stand for star schema design

[lconsalvo]

Hi,
I am new to a financial company and I was asked to model two new sources of data in the existing mortgage data mart (not too sure if you could actually call it a datamart after seeing the design), basically I was asked to create detail information about mortgage servicing data that is provided by two vendors, with different layouts. At this point the details of the requirements may be irrelevant. My impression of the organization is that it lacks true data warehouse knowledge, including myself. I've read plenty of books and took a kimball course last year, and build a dw/dm design for a pharma company for the past 5 years, so i have some hands on experience, but I am far from an expert. I tried to do my four part analysis (subject area, grain of fact table, dimensions and then metrics) ,unfortunately, my users did not understand what I meant and I probably didnt do a good job articulating, what I meant about the grain of the fact table. So I tried to come up with a high level conceptual design of my table layout specs. I presented my initial design pointing out what I thought the fact table would look like and identifying what i thought would be dimensions. neeedless to say, I was told that I needed to keep the design as simple as possible because there was no bi tool being used and the end user's were using TOAD to assess the database and they were very new to sql coding. Any complex joins would be too complicated for them to understand. I was asked to create stand alone tables (borrower dimension, mortgage transaction and a mortgage dimension) each table would contain the mortgage loan number so they link the table rows together, for instance there would be a row in the borrower table for every borrower associated with a mortgage. So if you were associated to more than one mortgage your borrower information would be stated more than one time on the table. When I tried to explain you can accomplish the same thing by relating the dimensioins in the fact table. I was told it was too complicated and i needed to keep it simple.
It gets better... I then went to the data architect that supports the dw group and asked his opinion.. He kind of agreed with me, but again i think there is lack of strong dw designers. His suggestion was to create a mortgage dimension, a borrower dimension and a mortgage transaction table. The mortgage transaction table would hold the metrics and be a detail/child table to the mortgage dimension. If you wanted to know the borrower associated with the mortgage he wanted to build an intersection table between mortgage dimension and borrower dimension.
when I said i didnt agree with that approach and said you can obtain the same results by designing a true star schema and associating the borrower dimension and the mortgage dimesion to the fact table, he first didnt understand what i was talking about saying he wouldnt mix the borrower information with the transaction data and then later giving up on me just said we dont do it that way.

I hope you can follow my explanation.. MY QUESTION IS HOW DO I CONVINCE THIS GROUP THE IN A DIMENSIONAL MODEL A STAR SCHEMA IS THE MOST EFFECIENT DESIGN OR AM I JUST TO INEXPERIENCED AND THEY ARE ALL CORRECT AND I AM WRONG, THAT THERE IS TRULY OTHER WAYS TO ACCOMPLISH WHAT I AM TRYING TO DO.

THANKS FOR ANY ADVICE

[VHF]

It sounds like you are on the right track and they have no understanding of Kimball-method dimentional DW design. I don't know what you can do other than try to educate them--maybe get them to read a Kimball book?

Regarding keeping things simple for the end users, you could create a star-schema dimentional DW and then provide views that join the fact & dimension tables for the users to query against. Because the view would handle the joins it would appear to be a single-table query as far as the user is concerned.

[ngalemmo]

As VHF pointed out, views are the way to go, particulalry with a non-technical user group that has to query the database directly. You may even find yourself creating a variety of different views exposing different collections of attribute to make things simpler. It's extra work, but if it helps the users be more comfortable using the database, it is well worth it.

As far as working with them, sure, its easier if you have a technical savvy audience, but most of the time you don't. You need to step back and understand where they are coming from and use terminology they are comfortable with. Don't try to 'educate' them... they are never going to be asked to design a database, and quite frankly, they couldn't care less about keys and columns and tables. Discuss solutions, not implementations. It's not easy, but goes a long way toward fostering understanding and support for your solution.

[Jeff Smith]

I worked at a couple of banks and insurance companies and my experience is that some people get it and others don't.

If the modelers come from the transaction side of IT, then you'll probably never convince them. 3NF is so ingrained in their brains that they freak out when they see a dimension table. And if you go off and do a "proof of concept", chances are you'll anger them even more because you've shown them up.

How are people who don't understand dimension tables supposed to lend their expertise to building cubes?

Oy. I'm a little bitter this week because it's a constant battle and sometimes I win and sometimes I lose.

It's the old story about leading horses to water but not being able to make them drink.

It is easier to have the Star Schema implemented if you have a line of business supporting you - after all, they're the ones paying the bills.

I had a situation where I was on the line of business side. I had a create modeler/architect who got it but his manager didn't. Sometimes his manager would shoot his ideas down, and he would call me up to explain the situation. I would insist that the project be done correctly. It didn't make the modeler/Architect popular with his boss, but 5 years later, the modeler/architect has risen in the company and his boss hasn't.

Fight the good fight and do it the right way.

It is amazing how many people insist on doing it the wrong way. DW has such a low success rate in large part because people don't want to use the tried and tested approach.

[Sponsored content]