Dimensional Modelling when the core concept has Many to Many relationships (or am I trying to fit a square peg into a round hole)

View previous topic View next topic Go down

Dimensional Modelling when the core concept has Many to Many relationships (or am I trying to fit a square peg into a round hole)

Post  robdawson on Tue Apr 06, 2010 8:25 pm

I am currently building a product to help do analysis of content management systems. The product is feeling very much like a DataWarehouse/datamart system, and I'm trying to apply dimensional modelling techniques to the model. After reading the first few chapters of "The Data Warehouse Toolkit", I'm beginning to fear that I'm trying to fit a square peg into a round hole. In order to explain better my concerns, let me give a brief overview of some of the data that I'm modelling. I'm perhaps hoping that I'm either going to understand things as I outline the problem.

The key initial requirements are to be able to search for information around content. The searches/reports are going to be answering some initial questions such as, "Show me content authored by Joe Smith", "I need to find content that is going to expire in the next month", "Show me content that has not been updated for the past 6 months", "Show me content in the products category that has not been modified in the past 6 months"

As a one time DBA - I've got a strong tendancy to want to normalise the model, but I understand the value in dimensional modelling, I'm just having trouble actually applying it.

The core business object is Content, which then has a set of data that can be queried on it.

A simplified data set is:


  • Content
  • Authors
  • Approvers
  • Categories
  • Keywords
  • Creation Date
  • Last Modification Date


In traditional modelling I would create

  • Content
  • Content People (with a type)
  • Content Categories
  • Content Keywords


This doesn't really map back to dimensions right, but I'm not sure how to do it. Also, as I'm getting my head back into the DataWarehousing space, I'm not sure that this is a typical data warehouse problem, as there aren't really the summarisation fields that seem more typical.

So the bits that are messy that I'm trying to resolve, are how to handle the fact that there might be multiple authors, categories, and keywords.

In addition a category is a hierarchy that has an arbritary depth. I've noticed the suggestion of flattening hierarchies, but I'm not sure of how to do it in the general case.

Any ideas or suggestions would be greatly appreciated.

robdawson

Posts : 1
Join date : 2010-04-06

View user profile

Back to top Go down

Re: Dimensional Modelling when the core concept has Many to Many relationships (or am I trying to fit a square peg into a round hole)

Post  Mj1978 on Wed Apr 07, 2010 12:20 pm

Hi Rob,

Considering the number of entities and the kind of information you want to seek, in my opinion it would really not matter what kind of model
you want to adopt (Fully normalized or Dimensional).
If I have to do this, I will definitely go ahead with a normalized data model and see if it solves the purpose and then over a period of time,
depending upon the frequency of the updates and volume of data I'll start denormalizing it to create a dimensional model. That will
only need a handful of SQL Stored Procedures / SQL queries to load those tables. That way you can see the merits/de-merits of both the approaches
parallelly and decide what to go with.

It was just an opinion. There are lot many far more experienced people in this group. Even I would like to hear from them on this.

Thanks
Manik
avatar
Mj1978

Posts : 8
Join date : 2010-03-10

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum