Archive for April, 2010

How to Implement “BOUSER” Row-Level Security in Xcelsius QAAWS and Live Office Connections

How to Implement “BOUSER” Row-Level Security in Xcelsius QAAWS and Live Office Connections

Question: How can I get user-specific data results for Xcelsius dashboards viewed through BusinessObjects InfoView?

Answer: There are a few application variables that BusinessObjects provides for use when a user logs into InfoView, one of which is BOUSER.  BOUser is the variable for the userId that is logged in and can be used when Xcelsius is aware of the user session.  The user session is passed to Xcelsius via a flash variable that has been made available in XI for this purpose.  It is called, CELogonToken.

BOUSER contains the value of the userId of the user logged in.  It can be used to provide user-specific row-level security through the universe design by adding a self join to a field containing the userIds in your data.  With XI3.1 Designer, this can be applied at the class level (across all objects in a class), or through the traditional method of being applied just when objects from that specific table are used in the query.

Here is an example of how this variable is applied:
Note that the self join is applied against the Manager field.

The Manager field for eFashion contains usernames like “Queen” and “Tuttle”.  For this example, we have added these as users into our CMC.

In QAAWS, I have created a web service to return Manager and their corresponding Store.  This query automatically prompts for the BOUSER since the SQL contains the @Variable(‘BOUSER’) restriction.

In Xcelsius, I have referenced this QAAWS with the appropriate connection defined and entering Name of Manager and Store name results into cells that are viewable through a spreadsheet component.

IMPORTANT: QAAWS will return a promptValue invalid error message if a valid value is not declared as a default for bouser.  Make sure you point bouser to a cell in the Excel spreadsheet of your Xcelsius design and input a valid userId as a default.

In order for Xcelsius to know the BOUSER userId of the user logged into BO, Xcelsius needs to be aware of the session.  If you enter a login and password to the QAAWS connection, that username will be used all the time.  Instead, these entries should be left blank.

If they are left blank, then the users will be prompted to log into QAAWS when they attempt to refresh the QAAWS query from the dashboard. 

For both performance and ease of use, it is usually desired that the users continue using their existing session for a single-sign on method.  To do this, a flash variable connection should be added which includes the range name CELogonToken.  This hands the existing session token over to Xcelsius seamlessly.

The BOUSER or userId is not viewable in the token value as displayed.  The token, however, makes this application variable available for use as the Login value passed to the QAAWS connector upon refresh.

In the end, my excel spreadsheet design looks like this:

I export the Xcelsius dashboard to the SAP BusinessObjects Platform (note that an Enterprise license is required for this functionality).

Then, I log into InfoView as Tuttle and view the dashboard. When I click the refresh button, the Manager Tuttle and his/her store gets returned.

Incidentally, the default value of Queen is never used.  The default is only to allow the bouser variable to load with the QAAWS definition, but only the actual user logged in ever gets passed to the query.

Shakespeare, Soccer Fans and IBIS

Shakespeare, Soccer Fans and IBIS

On my last day in London, after being stranded for 10 days by the Icelandic volcanic eruption, I decided to go visit Shakespeare’s Globe Theater.  This is a neat reconstruction of what was thought to be a likeness of the original Globe theater of Shakespeare’s day built about 200 yards from the original site. I took the guided tour and got to actually sit through 20 minutes of a last minute rehearsal of Macbeth which was to be performed that night. 

The actors used real swords and weapons as they would have done in Shakespeare’s day and the guide explained that back in those days, people expected the actors to be good swordsmen and everything had to be authentic to be entertaining.  He went on to tell us that going to see a play in the time of Shakespeare was equivalent to going to see a soccer game today. The crowd would be a little rowdy, drinking beer and wanting to be entertained. A good fight scene, compelling drama, bawdy jokes, witty dialogue and a good story were all essential ingredients to a successful play. Shakespeare was a master at this, and not only did he write his plays for the actors on stage, but he wrote them in a way to educate, involve and engage this audience of “soccer fans.”  Famous scenes like Marc Anthony’s speech after the death of Caesar “Friends, Roman’s Countrymen lend me your ears……” is a good example.

In fact, the audience participated so much in one play that they actually caused the first Globe Theater to burn down after some soldiers in the audience fired-off a real cannon in the middle of a battle scene! Yes, “soccer hooligans” date way back in British history!

The education part was important too because most people in those days were illiterate and never attended schools. The plays were a great way to teach history, tell stories, inspire passions and provide life lessons.

The InfoSol Business Intelligence Seminar (IBIS) has incorporated some of the same concepts as Shakespeare did in his plays. IBIS is all about engaging and involving the attendees in Business Intelligence in meaningful and practical ways. That is why most of the tracks are hands-on, so you are participating actively. The boot camps and workshops are based on real world usage of the solutions, so they will be compelling and tell a good (and true) story of how they are applied.

The instructors and consultants are like professional Shakespearian actors in that they are skilled, not just in explaining their topic, but also in its practical usage. They know how to wield those Business Intelligence tools like those actors wield their weapons! They are also passionate about Business Intelligence and inspire their audience to go forth and do great things with their newly acquired skills.

IBIS is also all about education; making it interesting and entertaining to gain more knowledge. Outside of the main tracks, there are plenty of opportunities to interact with your peers over a drink or meal and exchange ideas and war stories just like the original Globe Theater audiences did before the play and between the Acts.  In addition, plenty of Business Intelligence experts and developers are on hand to both demonstrate the newest solutions and field your toughest questions.

While at IBIS (June 6 to June 9, 2010 at the Ritz Carlton, Laguna Nigelview map), I only ask that you try your best not to get too carried away during the event and to definitely leave the cannons back at the office!

Xcelsius Dashboards – Best Practices – Data Layout

In addition to the Xcelsius Dashboard Maintenance – Best Practices – Using Labels in Excel I wanted to share other important aspects of making Xcelsius dashboards easy to maintain and for other developers to understand.  One such practice is having some kind of conformity in your approach to how you lay out the data for your Xcelsius project.

In this blog I will discuss 2 methods, both of which are valid and are useful ways to organize dashboard data.  Deciding as an organization which approach you adopt can help with the process of taking up someone else’s project.

Some Xcelsius developers like to use a method of having all the data that is being used in the dashboard on one (or more) tab/s of the Excel spreadsheet, and then as you need to use data for display, using a series of vlookups or match & index functions for displaying data on charts, which they layout on other tabs of the spreadsheet.

My preferred method is to spread the layout in such a way that the names of the Excel tabs generally correspond to the functional area on the Xcelsius dashboard, perhaps even the same name of the Xcelsius  tab (Either the Tab Set Container or the Label based menu, is what I’m thinking of), so that all the data being expressed on that “page” of the dashboard is on one tab in the Excel spreadsheet behind the scenes.

I like to have the data insert & any manipulations I have to do for expressing that data nearby the loaded data, so that it is all in one place & I can find it easily.  The only difficulty with this is when you have many components on one “page” or you have to create several aggregate levels to display.  Spreading it out & spacing it can become an issue, and this is where again, labeling the spreadsheet is so important.  (But aren’t we glad that if we move data around on the spreadsheet that the links follow, for those of you who have been using Xcelsius since the 4.5 days!).

This approach, of course, assumes you have the aggregated data to work with!  Which leads back to my previous blog regarding data connectivity options.   If I have to use QaaWS, I end up having to do a lot of manipulation in the universe to get the data to look or be ordered the way I want to use it in Xcelsius.  I usually have to end up making many of what I call, “dimensionalized measures” when I want to end up with a crosstab result.  So I have a measure for Jan Sales, Feb Sales, etc.  Very tedious.  Sorting is another  issue that is often easier addressed in the universe, but also can be labor intensive.

Live Office gives me the option to create crosstabs and sort the data in any way I wish and I can also have multiple tabs to aggregate the data at different levels (YTD, MTD,  past 30 days, etc).  The problem that I so often bump into with this approach is the amount of data when we get down to the detail level.  We have all experienced poor performance any time we go over the 512 row default and horrible performance when we approach 1000 rows.

All of these are reasons why I enjoy using the InfoBurst XDS or XDM option, as it addresses all these issues, plus gives me many more options.  The Intelligent Cache syntax of this tool is by far the most exciting feature, in my opinion.  Using their syntax, I can pass parameters to the XML url call and only return the rows of data I wish to display.  For example, within my xml url call, I can add syntax that returns …. YEAR=2009;DIVISION=HPD;DEPT=23.  This ability to extract just the rows of data I want to display helps keep my dashboards very responsive, since I am not loading it down with too much data.  It also gives me the ability to go to a much more detailed level than I would have been able to with QaaWS or LiveOffice.

XDS or XDM also has syntax to return unique values of a particular column from an xml data set which I can use for  my selector.  My selector will always have accurate values, as it is loaded from the current xml cache, rather than from a hard-coded list in my Excel spreadsheet which may not be up-to-date.

And, I have the flexibility to create these large XML data sets from a Web Intelligence document or by writing a query, directly to the database, or even from an Excel spreadsheet, and any combination of all three!

Post Updated 2012-05-21:  XDM links fixed.

Data Quality Dashboards

Data Quality Dashboards

The stream of articles, white papers and videos desperately trying to convince the business community of the negative impact of poor data quality on their bottom line seems to be never ending.  It almost seems like the IT brigade is marching into a cul-de-sac at the end of which is a brick wall with three large letters written on it – ‘ROI’.  Data Quality projects share their dreaded fate with all other projects emanating from the IT division and including the geeky word ‘data’ in their title.  While the management continues to remain blissfully ignorant of the unsettling financial and human consequences of bad data, customers, employees and business itself continues to suffer from ‘Data Quality Blues’.

The one technology in recent years that has managed to captivate the business users in organizations across the globe is undoubtedly – Dashboards.  Dashboards speak to them in a language which they are familiar with, the language of graphs, charts and metrics.  The BI Dashboard serves as an abstraction layer that shields the business users from the intricate and unwelcoming world of the ‘Data Churners’.  It is this instrument therefore, that should be leveraged to enlighten the management of financial losses that are incurred due to a seemingly trivial typo error or a misplaced digit in the sales figures.  Although this may not be the panacea one is looking for, creating a real-time dashboard which attaches a price tag to every data quality issue and extrapolates the figures to demonstrate their long term impact, may just act like a sip of coffee that will stimulate their business senses.

Here’s an example of a Data Quality Dashboard


This dashboard was the outcome of the convergence of three technologies: BO Data Quality,  InfoBurst 2009,  and Xcelsius.  Bundling a DQ dashboard with DQ Services benefits in the following ways:

  • Getting the initial buy in from the senior management
  • Understanding the DQ issues that are creating havoc
  • Tracking the performance of the DQ initiative itself
  • Displaying the relevant financial metrics to answer the ROI question
Photo Credit: Sieve photo in this post from Wikimedia

Update: 2013-04-02 – Broken link fixed

Europe Trip 2010: Day 8 Denmark – Vikings and Excel Post-Processing

Europe Trip 2010: Day 8 Denmark – Vikings and Excel Post-Processing

I arrived late at night at Copenhagen airport and headed for the taxi rank to get to my hotel. As I exited the terminal, there was a large sign pointing to “Danish Taxis” to the right and “Swedish Taxis” to the left. Not understanding the difference, I figured I should opt for the Danish taxis since I was in Denmark.  As we drove to the hotel, I asked the driver about the Swedish taxis and he explained that they were cheaper but they take you to Sweden so I guess I made the right choice!

I checked in at the Kong Arthur Hotel (Kong being King in Danish) and made it to my room where this huge modern painting hung above my bed. I have included a picture since it will have different interpretations to each viewer.  At that time it was saying to me “No matter how tied up in knots you are, come to bed”. In the morning, I noticed there was both the outline of a man and a woman in the picture and I had a totally different interpretation.

Although I did not see a lot of Denmark in one day, I did get to meet quite a few Danish people in my meetings and learned that Denmark is the home of Lego, Lager beer, ham and Vikings. I also noticed that the average Dane is significantly taller than the average European – must be that special beer and ham!

I was visiting one of the largest companies in Denmark where a couple of the divisions use BusinessObjects for their Business Intelligence. They were interested in finding a better way to schedule, publish and distribute their reports so I showed them a demonstration of the new InfoBurst 2009 along with presenting a few customer case studies. They were particularly excited about the ability of InfoBurst to apply Excel macro post processing. It seems they, like many other companies, have large numbers of Excel users that want all their Business Intelligence reports delivered in Excel format but publishing reports directly from BusinessObjects to Excel does not provide all the features in the Excel output that they want delivered to the user. For example, one customer wanted to make use of data grouping and outlining within Excel for one-click collapse and expansion of individual data groupings in the report. In addition, they wanted each and every data group and outline to be automatically collapsed in the initial delivery of the report – keeping the report compact and easier to view. This simply cannot be done publishing from BusinessObjects to Excel but with InfoBurst 2009, you can execute a VBA macro following report production and produce a revised version of the report for publishing.

This caused great excitement amongst my Danish audience and there were smiles and vigorous hand shaking at the end of the meeting.  I think they really appreciated the logical building block of adding the Excel post-processing – just like Lego!

Europe Trip 2010: Day 7 Switzerland – Finding a Common Language

Europe Trip 2010: Day 7 Switzerland – Finding a Common Language

Although Switzerland is a relatively small country, there are four distinctive cultures speaking four different languages within its borders. There are French, German, Italian and Rumantsch regions all speaking their respective languages.

This leads to some interesting situations as I discovered when meeting with perspective partners there. The first partner was primarily based in the French region and most of their clients were also based in the French region whereas the second partner was from the German region as were most of their clients. Even more fascinating was that the French Swiss prefer not to speak German to the German Swiss and vice versa even though they learned the respective languages growing up. Ironically they often end up speaking to each other in English which is not a native language to either of them!

When I enquired about the Italian region of Switzerland, I was referred to another partner that is Swiss Italian based!

In the world of Business Intelligence, we find a similar situation with the different vendors’ product suites. BusinessObjects, Cognos, Oracle and Microsoft BI all have their native tools that communicate very differently and there is no easy way to dialog between them.

In many customer situations that is changing as the demand and desire to use multiple vendor tools has become a necessity for many businesses.  The common dialog in this case is the portal or the main application where the user first logs in.  The most common portals I encounter are either the homegrown kind or Microsoft’s SharePoint. Through this portal, it is possible to seamlessly connect with different applications and different vendors Business Intelligence tools. Reports, dashboards and ad-hoc queries can all be accessed and viewed as if they were coming from the same application. Utilizing single sign-on also makes it possible to transparently pass authentication credentials to the various applications and tools without the user having to tediously log on to multiple systems and applications.

I end up discussing with my perspective Swiss partners how many corporations in North America are using mixed Business Intelligence tools through portals like SharePoint and how key solutions like Web Intelligence, Xcelsius and InfoBurst integrate easily.

As I head to Geneva airport, I spot a chocolate store and have to go in to browse and inhale the smell of heaven (I have a weak spot for chocolate). As I float through the store in my element, I cannot help wondering that while the Swiss may speak different languages, they have their chocolate as their portal to universal communication!

Europe Trip 2010: Day 6 Snowboarding Lessons

Europe Trip 2010: Day 6 Snowboarding Lessons

After my previous disastrous attempts to ski when I visited my son last year, he suggested I try snowboarding. When I told him I had heard it was more difficult than skiing, he just shrugged and said that I couldn’t do any worse. I figured he had a point and agreed to give it a shot.

After spending 20 minutes putting on all the protective equipment and some bright purple pants that my son insisted were an essential part of any serious snowboarder’s attire, we headed for the slopes where he showed me how to strap my boots to the snowboard.  The next part was significantly more challenging, namely how to stand up when attached to your board. After scores of failed attempts that amused the groups of youngsters watching to no end, my son finally showed me that if I rolled on my belly and then pushed myself up while facing up the mountain, I could actually stand up without immediately falling over again! Of course, this made me look like a beached whale, but it did work!

My first run down the slope was not very graceful and ended with a spectacular head first crash into the snow.  On my second run I collided with my son who bravely tried to slow me down and paid the price as I simultaneously wiped us both out.

The third and forth runs were not actually bad as I completed a couple of 50 yard runs at some speed. Amazingly, I survived my first ever 90 minutes of snowboarding with no broken bones or serious bruises but I was so exhausted from all the falls and attempts to stand up, that I could barely move a muscle.

As we sat and ate lunch, my son explained that I did better on a snowboard than I did on skis because my feet were locked in a position on the board and I could not move them like I can with skis.

Sometimes when there are less options or steps involved, we tend to master them quicker.  The same is true in Business Intelligence. If you give a user a Universe or View with access to hundreds of objects in different databases and a tool like Web Intelligence for self-service query and analysis, they will usually struggle to create any type of useful report, get themselves tied up in knots and become frustrated to the point of blaming the tool and not wanting to use it again.

If, on the other hand, you create a small specialized universe or view for a user with just a few relevant objects and a pre-made report that with which they can start, they have less options and directions to choose and they can master the tool a lot easier. They can still add, delete, modify, sort, filter and save in different formats but they are working from an already created report and do not have an overwhelming number of choices to select.

While many Business Intelligence tools have become more powerful, feature-rich and versatile, most users just want information delivered in a simple, easy-to-understand way and do not want to spend time learning products and becoming experts in tools that they may not use that often.

InfoSol has developed many self-service business intelligence solutions using Web Intelligence, Xcelsius Dashboards and InfoBurst and applying best practices to allow users to get immediate benefit from them without weeks of training and usage.

Now, not everyone wants to be a casual user and those individuals should take more advanced training.  But, for the novice snowboarders like me, just standing, staying up and gliding for 50 yards is great!

Xcelsius Dashboard Maintenance – Best Practices – Using Labels in Excel

In this post I will share with you a sample of what I consider to be one of many “best practices” to help in the ongoing maintenance of Xcelsius dashboards; Using labels in your Excel Spreadheet.  Some of the content in this post is extracted from the Excel for Xcelsius course offered by InfoSol

 Whether you find yourself having to update one of your own dashboards after a period of time of not working on it, or having to hand off the maintenance of the dashboard to a colleague, the initial effort of good documentation will save valuable hours and ensure continuity and success.  In this post some of the graphics will speak for themselves – you know the cliche of ‘a picture paints a thousand words’. 

The graphics show examples of: 

  • good labeling,
  • use of color to enhance visual cues
  • and the use of comments within Excel. 

Use of Color and Labeling

Below are samples of Excel spreadsheets that incorporates the use of visual cues like color coding with simple, clear and easy to follow labels showing where components derive their inputs.

Using Excel Comments

Another helpful option is to use the built in Excel feature of Inserting a Comment.  This leaves a small red triangle in the top right corner of a cell to indicate that a comment has been added to that cell. When the mouse hovers over the cell, the comment pops up. 


Helpful tips can be included within the comment.

Comments can be added to any cell by right-clicking on a cell and selecting “Insert Comment”.  Once a comment has been added to a cell, the small red triangle will appear.  A comment can be edited or deleted by right-clicking on the cell.

Using a Legend

There are many ways to document your Xcelsius Dashboard Excel Spreadsheet. 

I would love to hear about your “Best Practices”.

There is an Xcelsius Bootcamp being offered in Track 4 of this year’s IBIS 2010 event June 6-9 - Seeing Beyond Business Intelligence. 

Europe Trip 2010: Day 5 The French Alps – Social Networking in the Snow

Europe Trip 2010: Day 5 The French Alps – Social Networking in the Snow

A weekend in the middle of my trip gives me the opportunity to visit my son who works and snowboards in the French Alps for six months of the year.

He has to work on the Saturday so he sets me up in an Italian restaurant /pub with WiFi so I can catch up on a week of being out of the office. So for four hours, I diligently work away as various interesting characters come in and out – mainly to use the bathroom but sometimes for food or a drink.

At about 6 pm, I hear a lot of noise outside in the street; a combination of shouting and honking car horns.  Then suddenly, this man with his pants around his ankles and wearing a thong and a tiger mask bursts into the restaurant shouting in English with a strong French accent, “I am not John Lennon, I am John Lion!”

He continued to repeat this mantra multiple times as people either stood there smiling or made a quick exit in the opposite direction. Since the people in the bar (the smiling ones) offered him a drink and patted him on the back, I figured that this was not an uncommon occurrence.  A few minutes later my son shows up to tell me that it was this guy’s birthday and that before he came into the restaurant, he had run into his shop minus the thong!

When my son finished work, we went to a different restaurant for dinner which also had free WiFi for customers and since I still had my computer, my son wanted to show me a couple of things on Facebook.  We ended up on my Facebook page where I had very little written on my wall so I decided to post a message saying, “Visiting my son in the French Alps and watching naked Frenchmen running around in the streets”.

Now I only have about 15 friends on Facebook who can see my messages but within minutes I had 7 or 8 responses to my posting in addition to several requests to become my friend!

In this tiny little example, I had learned firsthand the power and effectiveness of social networking and viral messaging on the internet. No wonder so many businesses are shifting their sales, marketing and collaboration focus to this medium.

It is no longer sufficient just to have a nice looking web site to promote your business or interact with your customers and prospects. Without an internet social networking presence  through Blogs, Forums, Twitter, YouTube, LinkedIn, Plaxo, Facebook and others, many businesses and institutions are simply missing out on marketing, sales, technical, cost savings, recruitment and collaboration (just to name a few) opportunities .

There is no other medium in the world today where information can be disseminated to so many people so quickly.

So get ready to see a lot more from InfoSol in the social networking arena in the coming weeks and months but that will not include video clips of naked Frenchmen running around in the Alps.

Related links: InfoBurst 2009 YouTube channel and 9 ways to use social networking for blog promotion

Europe Trip 2010: Day 4 Lyon – Ease of Use Combined with Security

As I checked out of my hotel in Paris, I had finally figured out how to use my high-tech, multi-functional room key. When I checked in, I was given a room card key with some writing on it that I did not pay attention to until I got to the door of my room and could not find anywhere to insert the card. After struggling for a minute, I finally looked at the card which had the words “wave at the door” written in multiple languages.  So I checked nobody was in the corridor and feeling very silly, I waved at the door with my left hand. Nothing happened.  I tried waving with my right hand and several variations of wave including the “Queen of England” wave which is quite slow and deliberate.

Then it dawned on me that I was supposed to wave the room key card at the door. This, of course, worked and a green light lit up and I was able to open the door. The room was pitch black inside and so I fumbled around for a light switch but to no avail.  However I did see a red glow on the wall on the right which was a square plate about the size of a regular light switch. I tried waving my room key at it but nothing happened. I felt around the plate and discovered it had a slot on top. I put my room key in the slot (I know, a stroke of genius especially after waving at the door with my hand!) and all the lights came on.

Anyway, I had to laugh to myself as I left the hotel to take the train to Lyon at my first encounter with a wave room key. When I arrived at the train station, I had to validate my ticket by placing it in a machine which recorded and stamped it – no human ticket collectors.

I left the train in Lyon and headed straight for my appointment with a partner where I was treated to a demonstration (over lunch of course) of the newest version of 360View Plus. This is the neat security management tool for BusinessObjects XI that allows you to view and manage user and object security access. The tool has this really cool security matrix grid that allows you to modify access rights with a single click on the relevant cell. It can also produce these fantastic reports in Excel that show different security access for users and objects that are really useful for auditing purposes. Anyway, the product has new drag and drop features making it even easier to manage security settings as well as the ability to automatically schedule the security audit reports.

Although in most BusinessObjects environments a small number of people tend to manage security, it is still important to make the task easy and intuitive. 360View Plus allows that and, in addition, makes it just as easy for Help Desk staff to have the ability to reset access so that key BusinessObjects administrators do not have to be involved with these tasks. Best of all 360View Plus provides the capability to manage multiple BO instances (CMS’s) from a single interface enabling remote security management of different sites as well as comparing security rights between environments.

As I returned to the station to take a train to Grenoble, I needed to buy a ticket. I just walked up to a machine with a touch screen and selected my preferences and paid by credit card on the spot. Since the train I was taking left in 10 minutes, the machine pre-validated my ticket prior to issuing it and once again, I boarded without any human intervention.

The need for automated simple user interfaces combined with solid security is a clear differentiator in today’s fast moving world. France certainly appears to be one of the innovators here.

Of course, it may take a little time for us non-French to catch on but you will not see me waving at a door in a hotel ever again!