How to do a Dimensional Data Model for this scenario?


   I'm new to the world of data modeling.
   I have a relational database in DB2, which we are attempting to create a Dimensional Data Mart upon.
   I have these tables in my relational database :
   referral (referral_id) is joined to referral_clients(referral_client_id) on referral_id.
   client_roles(client_role_id) is joined to referral_clients on referral_client_id
   referral_allegation(allegation_id) is joined to client_roles on client_role_id.

 All these joins are a One-to-Many from the referral table through the referral_Allegation.
If i have to denormalize these tables and create a dimensional schema, what would be the best approach?

Please advise.
Who is Participating?
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
Ok.  :)

My guess is that you don't really have enough data to warrant a data warehouse, but here goes.

You PROBABLY want to start with the payment table.  That is your fact table.  You dimension tables for THIS fact table (every fact table has its own dimensioning) will be things like dates, counties, districts, providers, recipients (parents, children), etc.

There will be a lot of applicable dates for each entry in the fact table.  Effective date, Posting date, effective fiscal year, school year, etc.  The are all columns in the same dimension table.  Similar related fields will make up the other dimension tables.  (counties, districts, etc ), recipients (parents, children, etc).  Eligibility may be it's own fact table.  It MAY share some of the same dimension tables (like dates).  Registration could be another fact table.  So could attendance (biggie!).

There are certainly things that don't fit well into a fact/dimension model.  You may copy some of these table directly from the OLTP into this model.

Kent OlsenData Warehouse Architect / DBACommented:
Hi pvsbandi,

The first question that needs to be asked is, "what question does the model need to answer?"

If you have a transaction table, you'll probably want to show events over time.  

A modified form of the transaction table becomes your Fact table, the other tables are used to generate the dimension tables.  The dimension tables are generated primarily from the other table in the existing database.

pvsbandiAuthor Commented:
Thanks Kent!
   My reporting needs gather information from all these tables for various purposes.
   But if i want to flatten them, then can i create a table to store all the primary keys from the referral,referral_clients,client_roles and referral_allegation into one single table and then join the referral table,referral_clients table etc to this fact table?
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.

Kent OlsenData Warehouse Architect / DBACommented:
You can create the kind of structure, but that doesn't sound like a true fact / dimension structure.
pvsbandiAuthor Commented:
Any advise, based on the structure i have?
Kent OlsenData Warehouse Architect / DBACommented:
Without knowing something about the data, there's just no good advise to give.  The data model will be dependent on two things:  1)  What is the nature of the data; and 2)  What are you trying to answer.

Neither of those things have been part of the discussion so far.  :(
pvsbandiAuthor Commented:
OK.... Nature of the data is about Children's care in different counties in the State.
  The referral table has the referrals that were received about a child in need. That's the starting point.
  The referral table has the referral_ID, which is the primary key for that table. This table has Referral_Dt, based on which we say when a referral has started.
  The Referral_Clients table has all the clients, including children and elders in the household.
   Referral_Clients table has referral_id and referral_client_id and client_ID.
  Referral table and Referral_clients table are joined on the referral_id. For each referral, we can have multiple clients in the referral_clients table.
  The client roles table has all the roles for the clients in the referral_clients table.
  Client_roles table has client_role_id,referral_client_id and role_code. A client can have multiple roles.
 Example : a client can be a Biological Child in the family. He can also be a Alleged Victim.
       Similarly, an elder in the family can have roles like Biological father and also an alleged Maltreater.
  Referral_Client table and Client_roles table are joined on the referral_client_id.
 The Referral_Allegation table has the allegations on the Alleged Maltreater.
  This table has Allegation_ID,client_role_id and Allegation_Code.
 The Client_Roles table and referral_allegations table are joined on the Client_role_ID.

I hope i'm not confusing you :)
Kent OlsenData Warehouse Architect / DBACommented:
Not at all.  If I said, "BG8" what would come to mind?

Yours is a pretty standard relational database, not necessarily a fact/dimension model.  However, there should probably be referential integrity (foreign keys) between the tables.

A fact/dimension model is typically an OLAP (reporting) model capable of very efficiently selecting a small subset of rows from the very large fact table.  If you're constantly adding rows to the table, you probably have an OLTP (transaction) database.

pvsbandiAuthor Commented:

   This is the existing model, which is transactional.
   There is referential integrity between referral and referral_clients.
      Then between referral_clients and client_roles
      and then between client_Roles and referral_allegation.  

   But we have to make it into a Data Mart and this is when i want to flatten some/all of these tables. I need advise on how to approach..
pvsbandiAuthor Commented:
OK..Thanks Kent!
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.

All Courses

From novice to tech pro — start learning today.