MSI project estimation help needed 3

[MSIsam]

In our group, we have had a hard time providing estimates as to how much effort (hours) to create MSI projects of different complexities. What are the main drivers for providing an accurate estimate? Does anyone have any spreadsheets or documentation on this?

Any help would be much appreciated.

 

[dmcmunn]

This is just a starting point...

Main Drivers:

General Tso's Rule of Tumb...

How many attributes? X 0.25 days
How many facts? X 0.25 days
How many metrics? X 0.25 days
How many reports? X 2.00 days
How many prompts? X 0.25 days
How many drill paths? X 0.25 days
How many filters? X 0.25 days

See variance factors below...

If these are new reports, plan to code examples in SQL first, and perhaps do a pilot to make sure your interpretation of the requirements is what the business really meant...not what they asked for. Allow 3-6 weeks for requirements gathering documentation and analysis for most small to medium sized projects under 100 reports.

1. How many total reports will be developed ? As a rule of thumb, allow a minimum of 1-2 days per report for development and unit testing.
Good things to know are:
a. What report level filters will be needed ?
b. What report level prompts will be needed ?
c. Will report level filters / prompts deliver the required results across all metrics on the report, or will metric level filter, dimensionality or transformation be required ?
One clue that a report-level filter will suffice is that all dimensionality is the same for all measures on the report. If the report requires this year vs. last year and perhaps additional columns that show the difference between this vs. last, you will most likely need to allow for a set of filtered and/or prompted filtered measures for that report. Meaning a proportionately increased amount of project complexity to implement the report.
d. Will drill anywhere be available for this report ? If so, trot out an additional 1 day of time for developing a series of unit tests for all meaningful drill paths for the report. Also add an additional 1 day for QA testing, on average per iteration. One hint: if you can keep the database used for QA static from start to finish, this will make the QA person's turn time much faster.
e. Will a customized, local drill map be needed for this report ? If so, allow an additional 0.5 days for development and unit testing.
f. Will measures require custom formatting ? Add 1 hour for development.
g. Will this report's measures implement thresholds ? Add 1 hour for development.

2. Is the data warehouse PDM complete with respect to all the subject areas you will require for this project ? If the data architect says it ain't, don't even bother to start drafting a design for your project until they are.

3. Document all applicable warehouse tables to be accessed for each report. This can be automated with database metadata SQL queries to develop a cross reference between the database tables and the database columns associated with a project's facts and attributes. This also serves as a reasonableness check to make sure the architect can ask for clarification of any questionable cardinality or relationship issues up front. For an average size project, allow 1 day for this activity.

4. How many measures/metrics will be developed ? Allow a minimum of 2 hours per metric for development. This assumes all tables/views/functions from which this measure may be retrieved is well-defined. Along with the normal display characteristics.

5. How many attributes will be defined ? Allow a minimum of 0.5 hours per attribute for development. This assumes all tables/views from which this attribute may be retrieved is well-defined, documented, completed and accessible. Otherwise, allow 0.25 days.


6. Number of complex reports ?... more than one or two compound measures, or containing dimensional measures, percents to total, etc... report time X 1.5

7. How many documents will be required ?

8. Will the "out of the box" GUI be used ?

9. Will a custom user-interface be required ?

10. Never underestimate the power of a good, short but to the point education and training program in conjunction if this is the first deployment of MicroStrategy at this organization. Allow a minimum of 1 week for developing and administering hands-on overview. For a thorough training course allow 2 weeks for the project.

11. How will report / data authorization and access control be handled ? If MicroStrategy security will be required in addition to network and/or data warehouse security, add up to 2 hours per user.


12. Have test plans been written by the business side for all the reports ? If they can't test it, you will never be able to prove a report is complete. Moreover, if they can't tell you how to test it, they probably don't need it.

13. Are all the relationships between attributes and measures known, written into the specifications ? Develop a matrix of attributes down one column and measures across the top. Check off each intersection that makes sense to report. Summarize this document by measure with all the applicable attributes by which it is reasonable to filter or aggregate this measure. Review this document with the business SME's to make sure you haven't missed something important...even if it's only conceptual in nature. Test the results by writing SQL scripts against the data warehouse to verify all documented relationships are understood and attainable. Allow 2 days to develop a presentation and meet with the business to review.

14. Have drilling hierarchies been defined ? Make sure there are adequate tests of measures at various levels of meaningful aggregation along the drilling hierarchies. Again, report proofs may be coded in SQL.

15. Each fixed, reportable measure must be associated with one or more reasonable (business-defined) attribute. Make sure the business understands all visible attributes defined within the project in their terms. This one thing can save tons of time and avoid misunderstandings later.

16. Have attainable performance acceptance criteria been defined by and or negotiated with the business based upon physical constraints of the working resources ?

That's a start, and my opinion only, based on real-world experiences. I'm sure others can add much more than I.

Best,
dmcmunn

 

[DBADYN]

The response before mine gave an impressive checklist. The only thing I would add to it is a business-user focus on report definition. This basically includes business user (report user) signoff on 1) the metric definitions of the report (e.g. what cost data is used to derive "net sales"?) and 2) the layout/formatting of the report (subtotals in addition to cosmetics).

In my experience, these areas can really cause backtracking, even if you've done your homework with requirements gathering.

I would say to add a 30%-40% cushion if there is any doubt in the report definition characteristics, or if your user population for the reports in question are likely to have frequent changes. I always hope to avoid these situations, but sometimes life throws you these oddballs.

I will also add that my *quick* rule of thumb has been to start my project plan using one-week per report. This assumes that my physical data model is complete (including transformation tables and other MSTR source objects).

Hope this helps
Craig Rothe

 

[dmcmunn]

Excellent points DBADYN! I agree completely with the business user focus.

Signed-off (approved) report designs in business terms can lead to better buy-in from the business and provide more assurance the developer has adequately understood the business needs versus what the business says.

Many times there may be quite a large gap in "understanding" between what the business says and what is understood by the architect. It takes more than regurgitating what the business says in a static requirements document. One must do a thorough requirements analysis of what has been said always keeping an eye to what is reasonably deliverable given the delivery environment, project schedule, etc...

The architect must many times wear several hats at once by managing what is requested (deliverables), what is available (data quality/integrity/structure) and what is reasonable (project schedule).

Once the scope of the deliverables have been defined, the business should set the delivery priority of reports or report groups. This also assists with project management by making sure the business receives the most desired deliverables before the "nice-to-haves"...should an organization's resources and/or priorities be "refocused" by senior management, or should the project meet with an unforeseen catastrophe...such as the "bus" problem.

In a recent project which required conversion of 30 web reports from v6.5 to v7.2.2, concerns about the learning curve for getting the field user population (400+ users spread across the east coast) up to speed on the pure HTML interface of 7.2.2 vs. ActiveX / Java applet of v6.5 required the addition of a large block of time for development of CBT courses and customizing the stock UI to link in additional training materials attached to every report.

Best,
dmcmunn

 

[dmcmunn]

Part II - Physical data model gel and data quality

There are two items which can provide major impacts to a MicroStrategy project timeline.

1. A fluid physical data model;
2. Poor data quality

A Fluid Data Model
MicroStrategy works best with a PDM that is not in a constant state of flux. If the architect is not able to freeze all subject areas required for reporting for at least 30 days...DO NOT ATTEMPT THE PROJECT until its attributes are (to quote Undercover Brother, "Solid!") ...HERE BE DRAGONS!!!

Due to constraints, if you are forced to start early, insist on starting with a set of views, so the underlying tables may be at least minimally firewalled until the PDM is firmed up. Also, allow time for rearchitecting the sections in flux. Document all relationships, cardinality, RI assumptions, PK's, FK's, etc... Again, a lot of this work could be throw away if the PDM winds up being significantly different.

Anytime architecture must be changed their is a chance for introducing unexpected errors into metrics shared by all reports in the project. For this reason, I will sometimes develop a set of measures specific to each report to add a firewall between the sections I know are in flux versus reports based on more mature sections of the PDM. Again, none of this is a good idea, just ways of dealing with a poor situation.

A view into views:
1. Don't layer them more than two deep.
2. Don't use recursive views unless there is no other way to get "it" done.
3. Don't use GROUP BY unless you've got lots of horse power to spare in your mart.
4. Replace standard views with tables, parameterized views or indexed views as soon as possible to keep performance at an efficient level.

Poor Data Quality...
...can cause a well architected MicroStrategy project to be rejected by the business due to double counting, partial aggregates due to poor RI or missing/NULL join key data.

This leads to a lot of unproductive detective work to diagnose and prove the issues by both the ETL and MicroStrategy developers/analysts. For goodness sake, don't wait until production to find this stuff out! If possible, make sure to review and understand the QA steps involved in getting the data into the reporting PDM. If data is "cleaned", make sure the business is fully aware of these rules. In questionable situations, insist automated SQL scripts are developed and executed to test the assumptions documented in the ETL QA process before enabling reporting after a data load event has occurred. I know all this sounds like overstepping one's bounds, but it can mean the difference between keeping your business customer satisfied and having to regain their lost confidence in their reporting tool...which would you prefer ?

Best,
dmcmunn