Hillyer Consulting, LLC

I’m feeling inspired to write about #4, imbalanced tool privileges. People either have too much access or not enough. If you have no limitations it is likely that you have less centralization which means duplicate content all over the place, inconsistent data results where different business users get a completely different answer because they each applied their own logic rather than having centralized logic, and many situations where someone technical created something because they could but the person that takes their job after them is not as technical and can’t support what they created. Of course, that can happen regardless of what your support model is but if you have user roles defined and each user has the RIGHT amount of permissions it is more likely that the roles and responsibilities will get factored into the job description.

However, if users do not have enough permissions they are handicapped in the data tool they are trying to use. Because they can’t take advantage of all of the features they need they end up finding other tools to do the job. And you’ll end up with reduced adoption, losing important ROI. 

So what is the appropriate balance? The key word there is balance. You need to make sure to give just the right amount of permissions. This is easiest if you define roles within the organization such as system administrator, IT/COE developer, business developer/power user, creator/writer, consumer.  Over the years I have found what I believe is a good balance for most organizations, although each orgs culture, regulatory requirements, data maturity, and data goals certainly come into play. 

Here are the permissions I generally encourage for each role.
System administrator - full permissions to the application security module and control of stopping/restarting application services. Responsible for promoting production content from the COE. When I was a system administrator (for an extremely short time) I once had a manager equate the system to “my house” and anything entering or exiting my house should be controlled by me. That guidance has served me well for the most part. This person also needs all the developer tool access because they are supporting the developers. But they don’t and shouldn’t have a content creation role. They only create stuff for issue testing.

IT/COE developer- this person is part of a central group with deep data and technical skills. They define and follow consistent standards and provide tool support and training. They need expert tool knowledge and strong data modeling and analysis skills. They’re generally responsible for producing the “certified” standard content and any shared data models specific to the toolset you’re using. They need full on tool permissions and all the developer tools but they don’t actually have the right to publish their own stuff in production. If they have that, changes will get pushed spontaneously to production without proper testing and testing ends up happening in prod rather than test. Trust me. I’m the first to abuse that privilege and the first to tell the system admin I shouldn’t have it. 

Business developer/Power user - these are people who’s job description includes data, reporting, analytics, etc. as at least a third but more ideally half of their job. They also have business/subject-matter expertise. This is super important because you want to make sure that if they leave the position their replacement can handle supporting what they’ve built and continue to act as an extension of the COE. These people should have the right to use all content creation tools (not necessarily the modeling tools as that is a specific skillset best kept in a central group for oh so many reasons), save shared content without going through a ticket process (required so they can move at the speed of business), automate/schedule distribution of content, and help create business-specific training and documentation.

Content creator/writer - these people can edit and create their own content (reports, dashboards, analytics) but need to hand it off to be broadly shared.  They don’t necessarily have access to every single data tool in the company, but those that make sense for their role and skillset. They are still very empowered to use most tool features.

Consumer - these people are found throughout every level of the organization including executives, field/factory workers, salespeople, etc. They view and refresh data content others create for them, and perhaps interact, drill, print, send, and export. They may or may not have the skills to create their own data content or it simply isn’t part of their job. It is my recommendation that when licensing permits and it doesn’t add cost to your deployment, the consumers have the same tool permissions as the content writers even if they don’t all use it because that is how you empower those with a desire to move to the next level and become content writers.

This is the structure and the permission levels I have found to provide the best balance between meeting both IT and business needs. Hopefully you’ll be open-minded enough to consider this topic if it is different than what you do today. When in doubt simply ask the business. They’ll tell you their biggest pain points and that is where you should consider if you’re balanced enough or if there are users you should promote to a more exalted role. Enjoy!

Well past time to address item #3 from the earlier list of common barriers to Information Delivery/Business Intelligence: using the waterfall method.

The big struggle here is that the business changes more rapidly than we are able to finish content (or a data warehouse, an application, or almost anything being built for the business really). And when they see the content first-hand, they change their minds about what they really wanted. New thoughts arise about how it would work better. That’s why a more iterative methodology has become so popular across software development efforts.

Having a business user identify “requirements“ and then ticking off those boxes as you create BI content so that the work can be considered “done“ is a very often-used process. But I don’t think it’s the most effective method.

Instead, I would recommend a pilot/proof of concept/mock-up output at the outset. This one probably seems like common sense and is one of the easiest of my five suggestions to implement but I’m amazed at how infrequently this is done. Create something visual, even if it does not have real data. Then the business can see it firsthand to understand how it will work for them and offer up change suggestions immediately, rather than going through a whole development process, getting the end product, and then wanting a bunch of changes too late in the game.

This can take more upfront time, but the result is well worth it. The business ends up with a much better product that they are happier with, and it saves time on the backend.

Sometimes one of the big challenges to this is data is not yet available. Especially on new data projects. That should not stop you from creating a mock up data file in Excel or something of that nature, and loading it into the report, dashboard, etc. tool. Most tools can handle an external data file as a data source. You can just create Excel mock ups, but there is something about seeing it firsthand in the BI tool that helps the business think outside of the typical Excel functionality. And they will have a better understanding of how it will look, and what the possibilities are.

This method allows you to easily get feedback before anything is built and greatly helps you understand how the database or any BI semantic layer/model, if applicable, needs to be designed. I’m always surprised when data architects or business analysts don’t do this. A picture speaks a thousand words. Why wouldn’t we provide one?

Time to address issue #2 from two posts ago: Making users log a ticket for BI content changes or requests.

It all sounds very good...you only have so many resources so you create a request system. Then you get more work than you can handle so you implement a prioritization system. And maybe you add a person to act as the spokesperson for the business and to do the prioritizing so your developers can stay busy developing. It makes sense. Unfortunately it doesn’t work. And here is why...business users take the path of least resistance. And that means 7 times out of 10 when they learn that something they need requires a ticket they just find another way. They would rather implement a complicated Access database or Excel spreadsheet process than submit a ticket that will likely take months to complete or will get lost in la la land. So we fail. Rogue IT increase, BI usage suffers, inefficient processes win the day.

So what do we do? Do we just let everyone request whatever they want whenever they want and work to the bone to get everything completed? Not exactly. We don’t want abused resources and mass chaos, but we also need to be responsive and keep it easy for the business to get what they need, when they need it. Management is probably going to want job justification for you (or them) by showing how much intake and output you do. That makes sense for resource management, so we need some sort of tracking mechanism. And we want to work on the most important things first, of course. That takes prioritization. We also want to know how long something is going to take so we know when the next thing can be worked on. That requires estimation.

But none of that requires the complexity we tend to give it. The best person to hear the business need and determine the level of effort is the BI developer that will be doing the work. Period. Take the money spent on the middle person and put it towards another developer. (Sorry middle person). “But our developers can’t speak the business’ language and the business can’t speak the technical language!”, you say? Hire another developer. Seriously. That person should be able to play both roles or they aren’t the best person for the job. Hiring one person to play both roles and paying them more because of their extra skillset will still be cheaper than hiring two different people that can only work in their silo. Plus, you’ll have the added benefit of quicker turnover. Instead of playing a game of telephone with a middle man your developer will hear directly from the business, be able to ask pertinent questions, and make suggestions on the fly, which will avoid rework and reduce time to market. And they’ll learn about the business which may apply to the next project, bringing more value to the organization in the long run.

Okay, so step one is take out the middle man. Step two, assign specialties to each BI developer. What I mean by that is each developer specializes in certain areas of the business. A small organization might have only one or two developers and then they specialize in everything. But a medium or larger organization might have developers that only handle one or two business areas. This is so important because they establish a service relationship with that area, give the business area a consistent and direct contact point, come to understand the business enough to increase their effectiveness, AND will have an easier time with prioritization because the business users will be competing with requests from their own area and they’ll be able to identify what is most important. The developer can ask “what do you want me to work on first?” and the business will understand the implications and decide, as it should be.

Now, for this to work it is very important that the developer is NOT on the same team as the business. I can’t stress this enough. They can sit with them (I encourage that) and be in their team meetings (I highly encourage that), but they must not report to the business team. Instead they should report to a separate BI group. This is a MUST because when it comes down to brass tacks the business is going to do whatever they need to do to get the job done in the short-term. And that isn’t always the best choice for development. Standards, best practices, and good design tend to go out the window. Consistency across business areas becomes non-existent. And a non-IT developer tends to lose a little of their pull with the rest of the IT org versus being part of IT themselves. And the worst thing that would happen if the BI developer reported to the business area is that when they change jobs or leave the organization, the business will often backfill with someone that doesn’t have the required skillset. The only way this works is if the developer is assigned to an area but reports to another manager focused on more of a BI center of excellence role.

So now the developer is directly communicating with the business, is responsible for their own estimates, and prioritizing based on simple conversations with the business. But, there still needs to be a tracking mechanism and a way to handle resource management to make sure one developer isn’t really busy while another is twiddling their thumbs. Both have an easy solution.

Remember that developers may have a primary specialty but that doesn’t mean they can’t work on other things. And since developers are on the same team (like a central BI team) it is just a matter of weekly team check-ins to make sure teammates can ask for help and pitch in to balance the workload when necessary. “I’m overloaded. Does anyone have time to work on x?”

And a tracking mechanism can be anything...categorized emails, an Excel spreadsheet, a formal ticket application, etc. The key is that the user doesn’t fill it out. They’d rather go directly to a person and there really isn’t a strong enough reason not to allow this. I know, radical!! But in the ideal process the business comes directly to their appointed developer, who is empowered to manage their own workload, communicate their availability, and more than capable of using any chosen tracking system. As long as the system enables management to identify how much work is being done and by whom, it serves its purpose.

Additional benefits include a built-in SME that knows the tool AND the data and can help with area-specific training examples. Something that, as a BI tool trainer of almost 20 years, I can tell you is very challenging. Having this go to person is invaluable. And the ability for developers to be right by the business proposing better solutions, suggesting automation opportunities, and providing guidance to the business on using the self-service tools just creates a stronger, more loyal user base.

In all fairness, this setup will create one big problem...the business will keep wanting more and more. And the current resources won’t be able to handle all the incoming requests once the business catches on that this is awesome for them. That’s a sign that this way of managing BI is creating value for them. Ways to offset this are to look for even more automation opportunities, to add a floater resource that works with each developer to pitch in where they can, and to encourage self-service by increasing self-service resources such as BI catalogs, training and mentoring programs, interactive dashboards, etc. and by making sure users have enough access that they aren’t dependent on others if they have the required skills to get what they need in their own.

Nothing is perfect, but I can tell you I’ve personally seen MANY different organizations structure their BI work and I have been a developer in many of those organizations. And the most successful are the ones with a setup as similar to what I’m describing as possible. The others...they just eventually switch BI tools a bunch of times trying to find ones that “work” (expensive), when really the issue isn’t the tool, it’s the process.

As a follow up to my previous post regarding the speed of business and common (yet fixable) issues, I’ll start by addressing some thoughts around issue #1: No one knows what is available and where to find it.

This is one of the trickiest to solve because it does involve a lot of work. But it is well worth the effort because if there is a bunch of BI content that goes unused, there was a cost without seeing much return. The answer to the problem is probably fairly obvious. You NEED a living, breathing searchable content catalog, relevant metadata, and the stewardship that goes along with it. The search built in to most BI platforms is a start but you really need more. And some searches require the user to have access to the content before it shows up in the list. If they don’t have access to the content, it is hard to know it is there to even request access!

I’ve created a variety of templates such as report documentation, universe/metadata layer documentation, content lists, training guides, inventories, etc. over the years but what I have found is that too much information is hard to keep up and doesn’t really get read. Users are interested in a quick answer. Not many will spend more than a few minutes looking for something that is already built before they try to build something on their own or throw in the towel completely. So this needs to be a catalog that is not necessarily tied to their current security access, but is tied to their function. Sometimes those things are one in the same, but often people play roles other than those formally identified. It is useful if they can search the entire catalog, regardless of their role in the organization.

So what we need is something:
1. Searchable
2. Up-to-date
3. Quick to use
4. Straightforward
5. With a Support Contact

Searchable generally is achieved by storing an inventory of BI content and relevant metadata in some sort of database or searchable file, along with useful search filters and keywords. To create and maintain the inventory the ideal scenario is that you leverage your BI platform’s built-in querying capability. Most will have one or a third-party has probably built one you can buy. Worst case, is that a developer is manually entering content in a catalog every time something is created, which is manual but can be done IF you make it part of the change management process.

Next, you will have a fairly manual job of categorizing the inventory for your searchable catalog. Some of this can be done using lineage tools to identify a source system and maybe even some advanced searching tools that will pull out keywords from within the content, but most of the time this process requires at least some manual intervention. Oh, but it is so worth the effort if you do a good job. Just ask the users.

There are many categorizations or search tags to choose from but I have landed on three that seem to resonate well with the user base:
1. source system(s) (as in the name of the system(s) the user knows and uses)
2. business area/category (not always the org structure but can be similar. Categories might include supply chain, accounts payable, customer billing, workforce management, performance management, corporate accounting, etc.)
3. data keywords (e.g orders, sales, customer billing, vendor payments, employee time, etc).

This creates a business-relevant, easy-to-search BI content catalog. Just store this information in the catalog file or a database that can be queried with the catalog.

How do you keep this up-to-date? It MUST be part of the change control process or let’s face it—it’ll get outdated immediately. Any automation you can add to automatically inventory your content as mentioned above reduces manual intervention, but make a quick validation of assigned categories and data sources for existing content as well as assigning initial categories, etc. for any new content a mandatory step to move corporate content into production. Many companies I’ve worked with have purchased or built a change management application to request reports and dashboards move from Dev to Prod (or dev to test to prod, or whatever). Add a step to that workflow to update/enter metadata such as the searchable fields mentioned above, contact info, a description, and data refresh frequency. If you don’t already have a way to manage changing content (you probably should—but that’s another topic), you can create a simple access database or even an Excel spreadsheet for requests. Then build in the metadata as part of the Access workflow or another tab in the spreadsheet. The point is that if the developers have to go elsewhere to update it, the step will be missed. Integrate it in.

What support contact info do we include? 1) the business person that knows about the data, and their manager (so if the person leaves or changes jobs you can ask about a new contact person), 2) the initial/responsible developer of the content and the last developer that has changed it, and 3) the data steward, if different than the business person listed in #1. Why bother with this contact info? Because it isn’t realistic to train everyone on every bit of content and if the user doesn’t understand how to use the BI content or what it is telling them they’ll need someone to ask, ideally without a ticket. And these people are also great go-to people when looking to cleanup old content or to arrange content testing during an upgrade.

Granted, it can be very difficult to maintain this contact list at a report/dashboard level but doable if you work it into change management like you should. Alternatively, you can assign contacts at a folder/group level aligned with security access or a relevant business grouping.

Another helpful addition to the catalog is the security group/folder access a user needs to request to get access to the listed content. Your users will thank you.

I obviously feel strongly about this whole change management thing and advocate for building your updates into that process. But a worst case option is to do a quarterly review/update...the concern there is how easy it is to put the review off when times get busy, and then the catalog quickly loses its relevancy and is out of date. And if someone spearheads the catalog and then moves on, the next person may not have a passion for it. Working it into the change process creates consistency despite personnel changes and keeps your users coming back to it since it remains relevant.

So now let’s say you’ve done the work — you have an inventory of content with useful metadata and assigned searchable categories, data sources, and keywords. With that you’ve already created a straightforward, quick, and easy to use catalog. The next step is to make sure it is accessible by publishing it to an intranet site or Sharepoint site that all users can access, regardless of what BI security permissions they have. Don’t publish it only in your BI environment. That way even someone without a BI account can use it. Although adding a link right from within your BI interface wouldn’t be a bad idea. Include a “last modified” date so the user base can see it is updated regularly. Then, advertise the heck out of it. Hold an info session and demonstrate the use. Include it in your training classes and internal user group meetings (yes, you should have those things...). Make sure it shows up when people search your intranet. Email your active user base AND email your inactive user base.

If you make a comprehensive BI catalog a priority you’ll see an increase in the use of the BI content the organization spent money to build, an increase in user satisfaction, an increase in tool usage, a decrease in rogue and duplicate IT, and a clearer vision of BI shortages/shortcomings/gaps that exist. You’ll also see an improvement in consistency of reporting across different areas of the business. Isn’t all of that worth a few week’s effort? Ask any business user and you’ll get a definitive “yes”!

I’ve been thinking a lot lately about “the speed of business” within BI and the many roadblocks we face in moving fast enough for the business. We can provide the highest quality data, the deepest insights, or the most exhilarating visual representations but if they all get to the business after the fact (i.e. too late) then we’ve taken the “business” out of Business Intelligence, and that isn’t helpful at all.

So what are some of the common barriers to timely information delivery? Here are five I see regularly in almost every organization...
1. No one knows what is available and/or where to find it.
2. IT request process (i.e. logging tickets) is cumbersome or seen as cumbersome. The business either resists submitting the request in the first place or they brave the request process only to get results months later. By that time they either no longer need the solution or have established a work around in which they have control that they don’t want to give up (which they like because there is no IT bottleneck).
3. Using waterfall development process for information delivery. By the time the work gets done the business has already changed.
4. Unbalanced tool privileges such as not enough access or too much access.
5. Designing for the 10% rather than the 90%. If you create solutions with the knowledgeable, power user types in mind the other 90% of your user base will struggle to use it.

The thing is organizations can TOTALLY CONTROL these five things!!! I’m not saying it won’t take some work or even a culture shift, but all of these are generally self-made issues. My next post will offer some suggestions.

Here is a tip I presented at a user group meeting the other day, to create a search field in a report. Super easy way to add some useful interactivity.

1. Create an empty variable called Search Entry for the search input, like =""
2. Create a variable called Search Filter Flag to flag matching records, like =If Match(Lower([Product Color/Style]);"*"+Trim(Lower([Search Entry]+" "))+"*") Then "Show" Else "Hide“

The Lower() function ensures the function works regardless of the case used for the search and the +" " concatenates a space on to the search word, ensuring that if numeric values are entered they are converted to a string, with the Trim() function trimming the extra space right back off.

3. Create the input control entry field
4. Filter the report tab to Search Filter Flag=“Show”

Working on a fun user group presentation to show how to create custom column selectors with input controls so a user can choose what is in the first column, second column, third column, etc. of a Web Intelligence table without having to know how to use WebI.

I'll publish the instructions after I write them up!

Little SAP Lumira tip if you ever have to work with tabs. I used a CSV extension to bring tab-delimited content into Lumira from a website. So of course I got a single column with all my values separated by what looked like a space but was actually a tab. (By the way, if anyone can find an extension that can grab and refresh tab-delimited content directly from a website, please let me know!!) Anyway, I wanted to use the Split feature to break it into separate columns but Tab is not an option for the split. So I created a Calculated Dimension that used the Replace() function to replace the tab with a comma. To represent the tab you can use \t as shown below:
Replace({My Field}, "\t", ",")
Then I was able to use Split. I was just pleased the \t worked. It was a good day.

Been thinking about how to do Agile or Iterative project methodologies when doing BusinessObjects Universe development (or any semantic layer like the Oracle BI repository for that matter). A challenge for universes is the rework of contexts and object definition as new tables are introduced and additional chasm and fan traps are identified. New alias tables are created, join changes might come about, and the downstream reports are likely impacted and need to be retested. As the universe morphs the users might have to change their understanding of how it works, creating confusion. Even if you have some foresight into what the universe will look like in the end you'll still have changes, and generally more than standard universe maintenance.

I think the key is to focus on the end product--the dashboards, reports, alerts, etc. We have all created content that met the "requirements" but doesn't at all meet the business needs. It's just sometimes hard to know what you want until you see what you don't want. So I think iterative and agile project management methodologies lend themselves well to focusing on mockups, and fine-tuning the end product over and over until it is right before trying to build out any sort of database and universe solution. The idea of "build with the end in mind" has always been there but waterfall doesn't really allow us to have more than one or two chances to get it right. With agile and iterative you can partner with the business throughout and spend more time capturing their real wants and needs rather than documenting a long list of requirements that have changed by the time you're done.

I've been asked a few times lately to go over how you can create an element picker in a WebI report, that allows the users to choose if they want to display the data in a table or chart. I should really do a blog on it with pictures, etc. but thought I would just document it quickly here.

1. Create a variable called "Selector" and make the formula =""
2. Create a single-value input control using the Selector variable and customize the list of values. Enter "Chart" and "Table" as the values for the control.
3. Create a chart and a Table with whatever data you want to display.
4. Right-click on the chart and choose Format Chart. Under the General properties check the "hide when the formula is true" checkbox and enter the formula =[Selector]="Table".
5. Repeat for the table, but use the formula =[Selector]="Chart"

Hope that helps those of you that were looking to do this recently.

When you create a Lumira document that has contexts and try to refresh it through BI Launch Pad it throws an error. One solution is to simply use multiple data sources, one for each context. A colleague of mine also shared the option of adjusting the universe parameter JOIN_BY_SQL so the there is only one SQL statement. Thanks, V!