FM modelling help

[tupac_rd]

Hi Gurus,

What are the things to consider while creating an FM model using a 3rd normal form data model.

Thanks
2pac

[MFGF]

Oooh - lots and lots of stuff to consider here.  I could write a "war and peace" reply and still not cover everything you might encounter.

Probably the best bet to start off with is to have a look for some of the traps you might encounter and how to solve them - Chasm Trap, Transitive Relationship Trap, Connection Trap, Fan Trap etc.  Do a search on the forum - I'm sure you will find a number of hits that discuss these and how to resolve them.

The next thing to bear in mind is that the C8 runtime query engine assumes all reporting structures are star schemas, and constructs queries around this premise.  To do this, it has to differentiate between "Fact" and "Dimension" query subjects, and it does this based on the relationships it finds.  With a 3NF database, the risk is that the query engine will misidentify query subjects and construct inappropriate queries, resulting in incorrect report results.  The best practice approach to prevent this is to model your data objects in FM (query subjects) to represent virtual star schemas.

Then there's the issue of determinants - these are necessary where you have multiple "Fact" query subjects linking to different logical levels of a conformed "Dimension" query subject - they allow correct aggregation of facts in this situation.

Overall, I'd suggest that you only bring in definitions of the data required by the business to fulfill the reporting requirements, then use the best-practice modelling techniques to create a solution that will give accurate, consistent results and present structures and terms that business users can identify with and understand.

Again, try searching on here for some of these terms - Virtual Star Schema, determinants, best practice guidelines etc - there is a wealth of good advice you can tap in to.

Once you've read up a bit, feel free to ask more about specifics if you're unclear.

Good luck! :)

MF.

[blom0344]

In my experience it is nigh impossible to remodel an OLTP schema to a virtual new model that will meet 100% of the business users expectations. In other words when you expect each and every drag-and-drop combo to give the right result, then you will be disappointed.
More complex reports may require fiddling with aggregate settings or working with multiple datasets that are joined from within the report itself (When Report Studio is used). With small result-sets this will offer a workable alternative, but it requires additional skills of an experienced report writer.

[Lynn]

I couldn't agree with you more blom! I find this especially true when there is no ability to do things on the database side that could minimize some challenges.

As another trick up the sleeve, cubes can provide a degree of analytics while overcoming performance issues.

[cognostechie]

I couldn't agree with you more blom! I find this especially true when there is no ability to do things on the database side that could minimize some challenges.

As another trick up the sleeve, cubes can provide a degree of analytics while overcoming performance issues.

This is exactly what I suggest to my clients when they want to model from OLTP schema. In addition to performance, cubes can also act as an alternate to Data Warehouse and provide the right data.

[bloggerman]

Hi

Are you suggesting Cubes against OLTP in this scenario. Would cube build time be huge if done so?

Thanks

[Lynn]

I think if you don't have a spiffy data warehouse at your disposal you have to pick your poison. Constant demand on a live operational system can be fraught with peril if users are let loose to drag and drop at will.

Cube build times can vary based on a lot of factors but if something targeted can be built in off hours it may offer the ability to analyze data in a manner that performs better and deflects demand away from the system whose purpose in life is to support the operations of the business.

Similarly, detailed operational type reports that are not suited for reporting off a cube can be run against the OLTP off hours with output delivered to recipients or placed on a network drive for access.

A solution involving materialized views is another strategy that can minimize impact to the OLTP and offer greater flexibility for configuration to meet reporting needs. This can provide greater ad-hoc or on demand requirements for information.

No matter what, it all begins with understanding user requirements and then balancing those needs with the big picture environment one is faced with. There are always a lot of factors to consider to design a workable solution.