modeling complex datasource

[nfldbunker]

hi we have a failry complex datasource that was modeled by a consultant who built 2 cubes and a reporting package  from. The issue is even thou it is pretty good we have issues with bringing in items such as los(length os stay), age other fields etc. The reports and  analysis items contain issues and are not consistant. All itelsms that we require in our reporting package are not available - things are lumped in diffferent spots. Looking at this i find it will be difficult for some of our end users to report and do anaysis on.

My question is should i try and modify and  fix what is currently created or should i start and redevelop this environment.

if i redevelop should i create one giant reporting package or multiple small focused reporting packages ie) LOS reporting, diagnois reporting, procedural etc and tray and rebuilt the cubes based on these new packages.

please advise and let me know if you have any detailed documents that would  outline steps and preceures for creating this.

We have 2 business users who use report studio on this datasource which they find difficult to use and get the results required.

Thanks for your help in advance

bunker

[twlarsen]

Does your database match your package?  Or do you have a lot of things developed inside Framework Manager?

We model healthcare data, but patients, visits, transactions are stored in separate tables.  I've seen some places try and model the Visits and Patients inside Framework Manager off of transactions.

You might want to understand why your consultant set it up in a certain way.

Do you understand your business needs for reporting?  What are your facts?

[nfldbunker]

Hi

The datasource does not match the package as there are multiple items that are nto included in the package that is still in the data source which @ times we will need to use for reporting or analysis. the consultant tried to take the most used data elements and bring them into the frameowkr manager package and use the framework mnodel to feed the cubes. because of the complexity i currently see the reporting and cubes built as not user friendly to the end user who will be using this information for reporting and analysis.

do you have any suggestions on how i should setup a good model for reporting and analysis....should i do a similar concept and create and group all patient information together, visit information, diagnosis information, procedural information etc in ddifferent folders.

Some of this is done now but when you bring in registartion information, demographic is fine but as soon as you try and use diagnosis information also it goes out of wack.

bunker

 

[twlarsen]

So what are you trying to accomplish with Cognos?  What does your database look like?  What are the fact tables?

Your questions can be answered in a different matter depending on your business users.  If you have clinical users (Providers, Nurses, Aides, etc) you would be approaching different data then if you had administrative users.  For instance, both types might want visit information, but admin users could be looking at past visits in summary for analysis while clinical users would be using visit information by patient.

Nail down the who and what your data is for and you'll have an easier time.

To give you an example, I use different folders to separate out my facts and related information.  One folder might includes visits and patient demographics while another folder would include disease management registry with patient demographics.

[nfldbunker]

Hi Thanks for the feedback.

Our datasource was created to look like the SQL datasource but looking at the way it was setup it was not designed in the star schema setup. The consutant created 2 basic cubes and a reporting environment but i seeissues with trying to pull in data and get the proper values(lot of double counting), things not lining up properly.

The consulatant cerated our  framework manager package but did not create fact tables, dimamensional tables as suggested by cognos. I am trying to fuigure out the best whay to approach to getting this data to work properly. I can produce the reports requested but it takes a lot of tweaking, joins,filters etc.
The cubes were created from the frameowkr manager package and it is not producing the views and results we were hoping for.]

would you create different star schemas in your frameowrk manager model for different groups of data. In this datasource could you have multiple facts tables and multiple dimensional tables maybe not even related.

bunker
   

[nmcdermaid]

It sounds like this was a 'Transformer' FM model - a model especially built to feed data to Transformer.

In a perfect world this would also suit a 'user' model - one that is modelled so that users can use it for adhoc reports. Unfortunately I've never seen this work very well.

Try going in this direction:

1. Use the existing FM package (.cpf file)
2. Create a new namespace. Create a new package definition that contains only this namespace
3. Now create objects in your new namespace that will make it easier for your users to report. This is often (not always) a star schema representation of data. You should attempt to reuse base objects that have already been used to build the cube data feeds so that you get consistent data between the cube and user reports

It is often just not practical to use the same package for Transformer and for a user-centric model.

Was having a user-centric package part of the requirements for your consultant?