Modeling Relational data using FM

[ghostraider]

Hi All,

In the environment i currently work there is no warehouse. Data comes from relational databases, so should i dimensionally model the data by building dimensions and facts using Framework Manager or can i use existing relationships for the joins. We have around 2 millions records in our database and the data as of now is not dimensionally modeled. Users are complaining about the slowness in reports and i am assuming it is because of poor modeling rather than anything else on the server side. I would like to know if data should be modeled dimensionally in a situation where there is no warehouse. Please let me know.

Thanks!!

[biejorrun]

When there's no datawarehouse, and only a prod database to report on, maybe it's worth considering cubes. Cubes are fast to create reports on, they only need to be builded.

Regards,

Bjorn


edit: typo

[delight]

Hello

Sorry my english.

What are the primary intent of using Cognos 8 BI for your users?
There are a lot of dicisions to resolve your problems:

1. "DB SnapShots Reports"

      - Model Relational DataSource using FrameworkManager
      - Create various report's using Report Studio
      - Shedule (e.g. every night) report's using Cognos Connection.
      -------------------------------------------------------
      Result:
          - "Static" uninteractive fixed structure report's
          - High perfomance
          - Low time scale

N. "Interactivity"
      - Prepare your DB for dimensional modeling (flat dimensions, etc...)
      - Model Relational DataSource as DMR using FrameworkManager [rOLAP]
      - Tune your DB for best perfomance
      - Use:
          - Analysis Studio for dimensional analysis
          - Query Studio for realy simple reports (lists)
          - Report Studio for create reports, dashdoards, etc.
      - Use PPTransformer for creating cubes (high perfomance, ragged hierarchies, external aggregate)[mOLAP]
      -------------------------------------------------------
      Result:
          - BI functionality
          - Low-High perfomance  :)
          - Real time scale



Hope it'll usefull.

[blom0344]

Hi All,

In the environment i currently work there is no warehouse. Data comes from relational databases, so should i dimensionally model the data by building dimensions and facts using Framework Manager or can i use existing relationships for the joins. We have around 2 millions records in our database and the data as of now is not dimensionally modeled. Users are complaining about the slowness in reports and i am assuming it is because of poor modeling rather than anything else on the server side. I would like to know if data should be modeled dimensionally in a situation where there is no warehouse. Please let me know.

Thanks!!

DMR is not the way to go here. If you want better performance, than the way to go is a datawarehouse or the suggested cubes.
You CANNOT improve performance by using dimensional modelling on a truly relational source. That is just a sales talk.
If you stick to FM alone the best way to go is to use a single layer (avoid modelling as much as possible) that mimics the source database. Stay away from such things as determinants and skip the multi-fact options altogether.
Try to aim for the leanest SQL possible (which is generated with one single layer only)
The slowness of reports is partly inherent to the transactional source. You can partly fix this by scheduling slowly performing reports.

FM offers some pretty good tooling, but it works best with the neat type of database that is used as a demo. In real life there leaves a lot to be desired..