The Downside of Stored Procedure Based Query Items

In Cognos Framework Manage one of the query item options is a stored procedure. This item seems promising, but I would suggest avoiding it. First of all, to setup a stored procedure based query item in framework manager:

1. Write your stored procedure. It can expect prompts.
2. Select that stored procedure in Framework Manager as a query item source. (I would suggest pulling this in at the database level, or at the business level if you want to make it clear that it's not a base table) It will be scanned and expected prompts will be presented, similar to a drill through definityion.
3. Enter a prompt macro in the 'Value' field. At the report level we can setup a prompt to populate this value. This procedure expects 1 prompt of 'dept'. We can define a parameter of dept in a report to pass this value.

Within the framework model, this will behave as an any other query item. It can be related to other items in the model.

The major downside to this is in the execution plans cognos gennerates around these stored procedures. In a normal query cognos' query engine will look at the selected objects and gennerate an optimal query based on the relationships defined in the framework. Ideally, all the query work can be gennerated in 1 query that can be sent to the database. In the case of stored procedures, since cognos doesn't know about the contents of the stored procedure, the sql in the procedure is not included in the optimization plan. Meaning local processing will be required to join the results of the stored procedure to any other object in the model. Be careful to only include absolutely necessary queries in stored procedures.

I would use stored procedures for any procedure needing to use several temporary tables or user defined functions. (Although, in the case of user defined functions, I'd rather registered them with the Cognos gateway so that they can be called directly from Cognos reports)

Display a # of rows per page based on output type

Most of these articles are about things that frustrated me for enough of my day to swear a bunch and then say 'hey that's annoying enough to make an article about'. Today's tip has the added bonus of being part of a report I didn't even want to build. My user wants a summary of dollars over a variable period of time with the ability to drill into the total and see the distinct set of users that gave those dollars. This is fine, but my dataset has some spots in it that it'd be pretty inefficient to drill through on. Though the standard deviation of donors/unit is pretty high, I'd rather work on a way to assure a horrid query is never passed to the database. (hard to read, large query, resources, developers headaches, etc. But hey data analysis for potential hazard queries and allowing dump reports are post to save for another time)

That being said, the user also wanted to potentially run this thing out to HTML or Excel. Well, if the data set is potentially large I want to show a good amount of it on the screen in html but not enough that rendering will take a significant amount of time. For this you can use the Rows Per Page property.

This is fine for HTML only. Based on this setting when you try to run to xls 2007 this rows per page option will create a separate workbook tab for each 150 rows. If you run to xls 2003 single sheet cognos will contain the output to one workbook but the headers and footers will be repeated each 150 rows. BUT, in xls 2007 if the default rows per page (20 unless your admin changes it) setting will cause output to be rendered on a single tab.

What do we do? I want 150 rows in html and 1 workbook tab in XLS. Well, it's Report Expressions to the rescue! There is a cognos report function ReportOutput() that will return a string populated with the output type at render time. The function definition (as of the 8.4.1 documentation) is defined as:

ReportOutput ()

Returns the name of the output format. Possible return values are: CSV, HTML, layoutDataXML, MHT, PDF, rawXML, singleXLS, spreadsheetML, XLS, XML, XLWA.

Which is somewhat obtuse, but here's a nice chart of the output formats that I'm interested in. One thing to note is that you need the right string for the version of xls you're expecting to use:

CSV	Comma Seperated Value
HTML	HTML output
layoutDataXML	XML
MHT	I assume this is a MHTML output type for 'saved html'
PDF	PDF Output
rawXML	XML
singleXLS	Excel 2000 single sheet
spreadsheetML	Excel 2007 output
XLS	appears not to be generated in xls outputs as of 8.4
XML	XML
XLWA	Excel 2003

So, ideally I'd like to be able to update the properties setting on the list based on the report format. I couldn't figure out if that was possible to overload via javascript, so I took the easy way out. I created 2 lists, one that renders when xls is run and one for html:

1. Create a copy of your list A and A1.
2. Set the rows per page property to 150 on A.
3. Leave the rows per page property blank on A2
4. Create a new string variable with 2 Values 'XLS' and 'Other' the expression can be defined as:
   

   if (ReportOutput () = 'spreadsheetML') then ('XLS') else ('Other')

5. Set the Render Variable property to this new variable on list A and check the Other value.
6. Set the Render Variable property to this new variable on list A1 and check the XLS value.

Interviewing for a BI position

I've found that interviewing for a position in a business intelligence department is an infuriating dance of half truths, hope and plans. Unlike traditional software development or IT, your experience in business intelligence seems far more dependent on the team members and department around you. That's not the interesting statement from this post, that should be self evident. Business intelligence departments concern themselves with bringing IT knowledge onto business's turf. We're not working on traditional (or even extremely quantifiable) technology products, like say, pushing out exchange server because it's better for the enterprise than that old sendmail box. We are embedding ourselves in the business unit we are working for. Discovering their process and supplying them materials to enhance decision making in that process or at times shedding light on inefficiencies in that process.

A big indicator of weather you can achieve that goal is measured in the BI world by the meta-metric 'BI Maturity'. In the past, where I've seen it, BI maturity has been calculated with some things like, turnaround time on projects, size of userbase, or other such numbers. Those metrics don't tell me how easy it will be to work in that department, projects can be short or long and failed, the userbase could be huge but only a minority percentage of the available community, or worse they could be users that never login. Worst of all they don't tell me how effective the department is in the community. Does finance hate the IT department? Does HR think BI is junk? I want to know that! I want to know how easy it will be to work in that department, these rankings of maturity don't really answer the main question that I've got when I'm interviewing for a BI position: "How effective is this department, and how interested are it's customers in analytics?".

So like any data nerd, I came up with my own BI Maturity scale. Rank the company in terms of the following categories (in no real order of importance):

While I'm interviewing I kind of keep a tally of these areas in my head and try to ask some questions to fill out the numbers.

1. Leadership - The knoledge level and direction of the department/team leaders. The ability of the leadership level to accomplish their stated goals.
• Possible Questions: 
• What's the 1 and 5 year direction of BI here? What do you see the team looking like then?
• How do you pick projects to work on? What is expected from a proposal point of view?
• Describe a project where the customer wasn't meeting their end of the relationship. What did you do to remedy that project? (Here I'm looking for a leadership level that puts up effective exit points for projects rather than one that say they can save every project. Sometimes the best use of time is to come back later)
•  What would you like to do that you currently aren't doing? What successful projects needed stewardship and how did that occur?
• Do you have a system of mentoring?
• Is there an enterprise mandate for BI or are we off on our own?
• Are there several smaller BI teams within each department that may be working on their own projects?
• Describe the roles of users at the university. What BI tools do these roles each use? (analyitics, reports, query, dashboard, etc) What types of people are in these roles?
2. Staff - The experience and skill level of the staff of the department/team, these would be your direct co workers for a non-leadership position.
• Possible Questions:
• Of the senior resources, what are there past experiences on previous successful and unsuccessful projects? (I'm looking for team members with BI specific experience, has your dba done dba tasks for BI specifically? Is your etl developer an etl developer or a transactional programmer that knows sql)
• What is the ratio of senior (in experience) employees to junior?
• Who would I get to mentor? Who would be my mentor?
• Describe the project lifecycle of a previous, successful project, what phases of development occured and what was the engagement like? (Here I am looking for them to describe a mature design, functional requirements gathering, and Testing process. You know, the stuff bad departments skip. I'm looking for them not to say the words 'excel layouts were given to the developer', but that may be asking too much.) 
• What development methodologies do we practice? What standard tools or processes do we use? Do we use version control? Do we use a robust change management process?
• Do we manage our own hardware/servers? Can we tune them how we want?
• What is the restore plan?
3. Money - Does the department have resources to do BI? Are they struggling to make do with a specific budget.
   • Possible Questions
   • What software do we use? Is there a cordinated, enterprise software purchase plan?
   • How often do team members get new hardware
   • What training or confrences did team members last attend?
   • Do we manage our own purchasing or is it through a central IT structure.
     
4. Business Buy-In - What's the excitement/need level of the business users for BI? Are they clamoring for it and have ideas of what they want and pain points to address? Are they going to be engaged fully in the projects?
• Possible Questions
• Describe a successful engagement on a prior project from start to finish. How was it proposed, how were requirements gathered, how was it deployed and maintained. (I'm looking for a lot of answers here about how the business is very involved, wants this stuff and is driving a lot of the requirements.)
• Describe an unsuccessful engagement and how it was mitigated or remedied?
• What projects are in the pipeline? Is customer demand there?
• How do they like the software we push out? Are we trying to supplement what they already have or replace something? 
• How willing are the business users to be involved in the development of their applications. (This is a big one, mature BI departments realize that this has to occur. Resources need to be full time on this thing from both sides through all stages, design, development, testing, support. Non-mature BI departments will look at these applications as deliverables, IT projects that can be built and delivered to a business customer)
• Can the users develop their own content? Are the willing to? Describe a project where this occurred or is occurring and how that's maintained.

So Simple. What's the scale? I dunno, lets say 10/10 is the highest, but the actual numbers are arbitrary. If you ask questions about the department around these areas you'll end up with a good picture of the effectiveness of the department.

Now, the thing for you to think about is where you want to a work. A less mature BI department may have it's pluses, you might be getting in on the ground floor and build some good practices and clients. A mature department may have it's minuses, everything could be so prossessified that your role would be robotic and limited in scope. (You could end up the framework modeler for specifically the database source layer!)