MIMSVu and Project Controls 3660 and Work Orders 36620 1

[DSKwaj]

Having trouble acquiring all cost data using MIMSVu/CORVu.

Projects Data resides in Database 3660 and Work Order Data resides in 3620. Actual Costs appears in tables 665, 666, and 668 under 3660, and table 620 in 3620. However, these two databases cannot be joined by the end user and the detail cost in the 3620/620 Work Order table is needed also. I do not see Work Order being accessible in the 3660 tables.

Has anyone else had this type of problem? Why is Work Order data, that are children of a project, not part of the 3660 data nor accessible for an all inclusive query access capability?

Thanks

 

[Calator]

Here are some ideas that might help:

1. MSF668 is the project actuals by finacial period table, that includes both project-direct costs/revenue as well as linked work orders' costs. Therefore ALL costs are on this table, you do not need msf620 in order to retrieve costs. Were you to retrive costs from msf620, those are whole-of-life costs while msf668 has costs by period. It may be obvious that you cannot safely link the two tables!

2. You may need msf620 if you wish to report the work order description or other w/o detail. I would suggest that could be a simple database administrator task to modify the MimsVu knowledge library so that a virtual table for msf620 is added to the projects database. Test if msf667 links ok to msf620. You would need to start with msf667 and then build an "outer join" on msf620 because not all msf667 records have a work order id.

3. Please note there also is a CorVu forum where you can ask specific Corvu questions!

 

[DSKwaj]

Calator, thanks for your response!! Will check out the Corvu site.

 

[markvn]

I found that with project reports we ended up going down to the transaction level to get the best results.

To retrieve a report with a Projects (and related Work Orders) Commitments, Payments and Expenditure i have had to go to the two Project MSFX tables and the WO MSFX table then get details from there. There were several reasons why we went to this level but it typically is because what my clients perceived as Commitments etc where different to Mincom's view of things. Plus also they typically want to drilldown to PO's, Journals etc.

I am not near a MIMS system at the moment but i'd be happy to provide you with some details if you wish.

mn

 

[Calator]

markvn,

note that last contributions on this topics were made under the Corvu forum!

I agree there are instances when users need to drill-down to journal transaction level, however I recommend that if that level of detail is not required to be reported, we use as the source for the data the figures cumulated by period from msf668 (projects) and msf972 (work orders). This is for 3 reasons:
- msfx and msf900 tend to be huge files as they store detail transaction and may cause performance issues
- msf900 transactions eventually get shifted to msf901/902 and then archived, so are no longer available, or you need to read 901 902 as well.
- it may be not that easy to report on cost categories or revenue, based on journal transactions.

I always found as a good idea to read the source code for the on-line screens that offer drill-down using action codes and mimic that data acess path in the reports.

 

[markvn]

I would suggest that maybe instead of evangelising a CorVu forum a Reporting/Query forum would be better as i have many clients who use Business Objects, Cognos and others as apposed to CorVu / MIMSVu.

I agree completely on the issue of MSF900 + 901 and 902. But what i have found is most clients end up creating datawarehouses before touching the data for these more complex types of queires, you need to deal with it there not in the query. I found by making denormalised tables of Project Transactions with all the information at hand then ad-hoc tools become useful. (But of course by adding a Datawarehouse you've added quite a lot of costs

I have found in the past that the MIMS summary tables are great for the first version of reports/queries but the user will often ask for just one more column that blows all your work away.

Last year I had an example with equipment costing. We provided all the information via the Equipment Costing summary tables and everything was nice and fast and accurate. They could see things by WO, Cost Center etc. But then the customer asked to see the costs by Supplier (i think), this wasn't held in the summary tables so we either had to make a new report or modify the original to search a bit deaper.

I have seen this happen many times and often the answer is "we need to go down to Transactions"

Definatly not the right way to do things, but combined with a decent Datawarehouse environment it's the most accurate and maintainable.

mn

 

[Calator]

Markvn,

I find your contribution here very interesting and valuable.
There was no evangelising of Corvu on my part, just a pointer for you so that you do not miss some of the discussion that already took place around this question. Personnally I am not a huge fan of Corvu. The discussion might have as well moved to the "datawarehousing" forum. As long as the topic revolves around the MIMS data model I am happy to keep it in this forum. However most of the original quuestion had to do with the setup of the Corvu Knowledge library.

 

[markvn]

Calator, I think maybe evangelising was a bit too strong a word (wasn't an accusation just a suggestion).

Have a nice weekend!

mn