BI Blogs

Over the first part of the 2009 year I have downloaded numerous training items, whitepapers, applications, and patches and just wanted to take a little time this weekend to put out a list of items to share that I believe will be useful if you haven’t come across them yet.  Some of these maybe a little older, I might have posted about them already, or you might already know about them, but just want to put them out there just in case.  Not all of these are strictly SQL Server or Business Intelligence related, so just want to warn you.  You can share the information with someone else that might benefit from reviewing the material though.

Training Items

• Hands-On Labs for Enterprise Library 4.1 - Use this set of Hands-on Labs as a guide to learn about the application blocks included with Enterprise Library 4.1 and practice how to leverage their capabilities in various application contexts. It includes Caching, Cryptography, Data Access, Exception Handling, Interception, Logging, Security, Validation, and Unity Hands-on Labs.
• Azure Services Training Kit - April Update - The Azure Services Training Kit includes a comprehensive set of technical content including hands-on labs, presentations, and demos that are designed to help you learn how to use the Azure Services Platform.
• Office SharePoint Server 2007 Technical Library in Compiled Help format - This downloadable CHM is a copy of content in the Microsoft Office SharePoint Server 2007 technical library. The CHM is current as of the date above. For the latest information, see the technical library on the Web.
• SQL Server 2008 Developer Training Kit (SQL Server 2008 Developer Toolkit feedback)- SQL Server 2008 offers an impressive array of capabilities for developers that build upon key innovations introduced in SQL Server 2005. The SQL Server 2008 Developer Training Kit will help you understand how to build web applications which deeply exploit the rich data types, programming models and new development paradigms in SQL Server 2008.
• SQL Server 2008 R2 Information and New Site – also sign up for CTP notifications on the new site.
• IT Operations Scorecards and Dashboards – PerformancePoint Server monitoring sample available.  There is a video, whitepaper, and code sample files for download.
• Microsoft SQL Server 2008 Books Online (May 2009) - SQL Server 2008, the latest release of Microsoft SQL Server, provides a comprehensive data platform. Books Online is the primary documentation for SQL Server 2008.
• Demo Showcase 2008 Application Platform Optimization Demos - The Demo Showcase 2008 APO Demos are a set of 4 comprehensive click through demonstrations designed for partners to use with customers. These customer scenario demos feature many different Microsoft technologies working together to showcase the power of the App Plat Optimization capabilities. Included in the scenarios are the following products: Windows Server 2008, SQL Server 2008, Office Enterprise 2007, Visual Studio 2008, Windows Vista, Silverlight, Performance Point Server 2007 and more.

i>Office SharePoint Server 2007: Five Ways SharePoint Can Save You Money - When you adopt SharePoint Products and Technologies in your organization, you can: • Reduce IT costs and complexity • Reduce development costs • Simplify management and training • Improve employee productivity • Enhance the effectiveness of customer service and sales teams

Applications (all FREE)

• SharePoint Designer 2007 - SHAREPOINT DESIGNER 2007 IS NOW FREE! Learn more about these changes and future direction (watch the FAQ video link below). Office SharePoint Designer 2007 provides the powerful tools you need to deliver compelling and attractive SharePoint sites and quickly build workflow-enabled applications and reporting tools on the SharePoint platform, all in an IT-managed environment. Video of SharePoint Designer 2007 FAQ (16.8MB)
• BIDS Helper - A Visual Studio .Net add-in with features that extend and enhance the functionality of the SQL Server 2005 and SQL Server 2008 BI Development Studio (BIDS).  This is a must have tool if you are doing any BI development with BIDS (SSAS, SSIS, or SSRS).
• ZoomIt - ZoomIt is a screen zoom and annotation tool for technical presentations that include application demonstrations. ZoomIt runs unobtrusively in the tray and activates with customizable hotkeys to zoom in on an area of the screen, move around while zoomed, and draw on the zoomed image.
• Report Builder 2.0 with SP1 (stand-alone) - Features specific to Report Builder 2.0 are focused on simplifying the process of creating and editing reports and queries and include the following: Easy to use wizards for creating table, matrix and chart data regions; Support for directly opening and editing reports stored on the report server; Support for using server resources such as shared data sources; Query designers for multiple data sources including a Microsoft SQL Server-specific query designer.
• Data Mining Add-ins for Microsoft Office 2007 - Microsoft SQL Server 2005 or Microsoft SQL Server 2008 enables you to take advantage of SQL Server predictive analytics in Office Excel 2007 and Office Visio 2007.
• BI Report Automation Publisher – for moving PerformancePoint Server items between environments and will work with Workspace files. The BI Report Automation Publisher is a command line tool + .net library which provides a more automated way to move data sources and reports between servers/environments.
• Collecting Analysis Services Performance Data for Performance Analysis – ability to collect and gather information from Analysis Services using trace files, performance monitoring tools, and DMVs.  Includes all documentation, scripts, SSIS packages, trace files, XMLA scripts, SSRS reports, etc. to get started.

tches

• PerformancePoint Server 2007 Hotfixes (since SP2) Available and Build List
• SQL Server 2008 SP1 - SQL Server 2008 SP1 Available and Install Experience – build 2531
• Office 2007 SP2
• Service Pack 2 for Office SharePoint Server 2007 and Windows SharePoint Services 3.0
  • Important Notice: A bug in Service Pack 2 could affect the server farm licensing information, for more information, please refer to the new post here: http://blogs.msdn.com/sharepoint/archive/2009/05/21/attention-important-information-on-service-pack-2.aspx
• Windows Vista and Server 2008 SP2 - Windows Server 2008 and Vista SP2 is now available

I think that covers the majority of it for now.  Lots of different items and plenty of stuff to keep you busy for a few weeks.  Enjoy! 

By the way, don’t forget to check out the Microsoft BI blog.  There have been lots of useful postings added recently with videos in regards to mobile BI, Excel 2007 Data Mining Add-in, Gemini demos, etc.

A new desire came from one of our customers. The request was to have the ability to
search in Panorama Crosstab. The first solution I thought of was searching the grid
in iterative way and it worked fine using the Panorama SDK. After that, my friend
Boris came with another simple and elegant solution: We can use Parameter in the Panorama
view and highlight the number which was defined in the parameter. In this post I’ll
explain how to implement this.

1. Create a new view using Panorama NovaView Desktop and make sure you see the
grid in the view.

2. Define a new parameter: Click on View -> Paramaters, and click on the “Manage
Parameters” button. Click on Add. The default type is Number and this is exactly what
wee need (for now). In the name, type Highlight and in the Default Value type a number
that you see in the grid (this is the number that will be highlighted later). Let’s
take 0 for example. Click on OK twice and close the little Parameters window.

3. Create a new Exception: Click on Data -> Exceptions -> Exceptions… -> Add.
Click on Next and then choose “Custom Exception”. Click on “Edit Exception” and there
write the following formula: [Measures].CurrentMember = [[Highlight]]
This will simply select all the cells with the number that we defined earlier in the
Highlight parameter. Click on OK and click Next. In this step, define the style of
the highlighted cells. I picked red color and Bold font style. You can click on Finish
now and then click OK. Open the small Parameters window (right click in the crosstab’s
corner and choose Parameters) and click on “Apply Changes”. Now, you will see that
all the cells with 0 are highlighted. If you don’t see it, check that you did all
the steps correctly.

4. When we will show the view to the user, we don’t want to show him anything highlighed
when the view is loaded. This is where a little trick takes place: open the small
Parameters window (right click in the crosstab’s corner and choose Parameters), double
click on the Highlight parameter. Choose String as the parameter type (on the right
part) and in the Default value, enter abc. Click twice on OK and then on the
apply button and you’ll see that now the highlighed cells are regular ones.

5. In the dashboards page, or in the web page you created using the Panorama SDK,
create a button that will call the function searchGrid. Just add the button the property
onclick=”searchGrid(’master’)”, where master is the applet’s name. this is the code
of the searchGrid function:

function searchGrid (applet) {
var reply = prompt(’Please enter the number to search’,”);
eval(applet + ‘.CallUpdateParametersEx(”P|~|Highlight|~|’ + reply + ‘|~~|”)’);
}

Another tweaks I implemented and I didn’t write in this post in order to make it simple
(for advanced developers only):

• You can search all the views in the current web/dashboard page. Just call the function
  for every applet, but make it in Batch mode.
• You can search all the grid even if the user doesn’t see all the rows. You can tell
  him if the number he searched for is in there or not.

That’s all. Test your new page and enjoy. For every question about this and anything
else, you can leave a comment or write my mail.

With our Spring ‘09 release, it’s been sales management dashboard adoption month at LucidEra. Yesterday we received two more 5-star Force.com AppExchange reviews from Salesforce.com customers:

“If there was an Oscar for best picture (of a pipeline…..) LucidEra would win hands down!”

“Data warehouses are extremely valuable, but can also cost a lot in dollars, resources and time to implement. We’re getting this value at a fraction of the cost in a SaaS model.”

It’s extremely satisfying to hear that our analytic applications are having this level of customer impact. You can check out all of the Pipeline Insight AppExchange reviews here.

Welcome back to our series of posts that provide a green enterprise checklist for retailers. In my previous post, I described how to create a vision for sustainability and introduced five areas of focus to help you begin implementing that vision. The first area on our checklist is to evaluate your business operations.

Your business operations
Green architecture is making inroads in the retail world as large companies like Wal-Mart, Best Buy and Home Depot are building or retrofitting stores to LEED (Leadership in Energy and Environmental Design) specifications. Retailers who rent their facilities will need to get landlords onside to make changes. Most will be supportive of plans that outline possibilities for savings through energy efficiency.

Data compiled by Platts Research and Consulting shows retail buildings in the U.S. spend an annual average of $1.21 per square foot on electricity and 14 cents per square foot on natural gas. In a typical retail building, lighting, cooling and heating represent 69 to 84 percent of total use depending on climate, making those systems the best targets for energy savings. BC Hydro provides an excellent guide to managing energy costs in retail.

Immediate areas to consider, include:
Continue reading “The green retail checklist, step 2″

Why are data silos so prevalent when we really ought to know better? We know that it’s risky to have inconsistent data. We strive for the “single version of the truth” along with our IT and high tech vendors.

So, with all this awareness, why does almost every company end up with data silos?

There are two causes that I’ve seen in my consulting work. First, companies typically organize BI and DW related projects on a tactical and project-by-project basis. This makes data silos very, very likely. Second, these tactical projects are designed without an overall information architecture blueprint. This absolutely, positively guarantees a silo. You “can’t get there from here” if you don’t know where you are trying to go.

There are several reasons why otherwise smart people create projects on a tactical basis without an overall architecture:

Funding - Projects are funded by a group representing an organization or business process. These groups are focused mainly on their own information needs and delivering business value in the context of those needs. Building an enterprise-wide solution is not in their minds nor in their charter.

Confusion - Industry jargon creates the impression that things are different, when they’re actually similar. There are many similarities between CRM (customer relationship management), SCM (supply chain management), budgeting and forecasting, performance management and balanced scorecards. Many people do not see that each has data, data integration and business intelligence that can be shared across projects.

Technology - People tend to treat different technologies as different projects and applications. ETL (extract, transform and load), SOA (service oriented architecture), EAI (enterprise application integration) and data virtualization are considered different and treated as separate entities rather than different methods to implement data integration.

Disconnects - Data warehousing and enterprise applications are treated as separate worlds. Even though the business person may need reporting from operational applications and the DW, different projects are created to implement reporting from each of these data sources. And often IT splits its ERP and DW people into separate groups, further perpetuating silos.

No Architecture  - Finally, without an information architectural blueprint how is anyone to know that these projects should be connected? If you split up building a house amongst various contractors and specialists such as electricians and plumbers without a blueprint and without these groups talking to each other it is unlikely the house gets built correctly. It’s the same with a DW or BI project. You need the blueprint (information architecture) - especially if the projects are set up tactically and on a project-by-project basis. This is called accidental architecture and it creates data silos.

Data silos result in poor quality data that is often inconsistent, wastes resources and time on overlapping and redundant projects, costs more money to build and maintain, and ultimately results in the business not getting information in a consistent, comprehensive and current manner. Data silos also encourage the creation of data shadow systems or spreadmarts, further exacerbating the problems.

How do we get out of this? Stay tuned for more posts in the People, Process and Politics series.

According to this summary of the 2009 CSO Insights Performance Optimzation survey:

• 41% of sales reps are failing to make quota
• Companies on average are hitting 86% of plan
• 15% of reps are turned over
• 70% of reps are taking 7 months or more to ramp up
• 86% of companies have higher sales targets in 2009

Yesterday I wrote about a few examples of immediate pipeline impact from LucidEra Pipeline Healthcheck. In light the numbers above (which I suspect have only gotten worse over the past few months for most sales organizations), here are some examples of sales analysis that can lead to improved sales productivity, new revenue opportunities, and increased forecast accuracy.

Sales Rep Tenure Analysis

You typically expect to see better metrics for more seasoned reps, right? After analyzing a number of key performance metrics of sales reps by tenure, the LucidEra Pipeline Healthcheck discovered that newer reps actually had larger deal sizes. It turned out that a year ago, the company had switched their sales model to Solution Selling, whereby services were included with new product sales. Further analysis showed that newer reps were selling services with 50% of the deals. However, the most tenured reps had fallen back to their old product-centric selling approach, and sold services with only 20% of the deals. This led to a Solution Selling training initiative for the seasoned reps designed to help them increase their deal size by including services more often.

Average Days to Close (Won) Analysis

The Pipeline Healthcheck creates a sales rep report card, which includes looking at days to win versus days to lose. We identified that the days to lose a deal (actually, the days until a sales rep recognized that a deal has been lost!) was 2.5 times longer than days to win. Further analysis of win rates showed how win rates decline rapidly with age.  The recommendation was to go back and re-qualify all deals when they reach 175 days to avoid wasting excessive amounts of time. This process had a major impact on sales rep productivity.

Sales Rep Ramp Analysis

The LucidEra sales rep scorecard identified that reps didn’t hit peak performance until nine months, as opposed to the typical assumption of six months. The impact was that when planning for sales growth in a region, the sales organization needed to hire nine months in advance of their goals in order to deliver on the sales plan.

Tomorrow I’ll finish of this series of Pipeline Healthcheck examples by focusing on sales process analysis that can impact your performance. In the meantime, you may also find these posts to be useful (if not somewhat entertaining):

• Using Analytics to Avoid Wasting Time
• SaaS BI - Analytic Aspirin in a Difficult Economy?

This Tuesday, I had the opportunity to participate in a half-day seminar on “What Sustainability Means to a Company’s Bottom Line.” The event was hosted by SAS in our green LEED-certified Canadian Headquarters in Toronto.

As I was preparing for the seminar, I reflected on the topic at hand. I posed the question: if sustainability doesn’t improve an organization’s bottom line, then would this be a hot topic in boardrooms around the world today? Some may argue that, yes, reputation and other intangible benefits are reasons for investing in green practices. And that is true. However, David Senf from IDC shared that brand value is the least compelling of the investment drivers. What is top of mind? Cost Reductions.

There are some real case examples of organizations that derive bottom line benefits from sustainable strategies. I shared examples from the Poste Italiane Group and an Event & Entertainment Company. But the one stand-out in my mind is the University of North Carolina at Chapel Hill . In 2006, the President of the University signed the American College and Universities President’s Climate Commitment, pledging to be carbon neutral – no net greenhouse gas emissions – by 2050. Given that the university operates like a small city with over 17.5M sq ft of space, public transit, and a coal fired power plant, they needed assistance to quickly assess areas for potential improvements. Using statistical discovery software and advanced data visualization tools, they are now able to look at 10 years worth of data on their 300 campus facilities.

In one recent case, they identified a building that was consuming significantly less resources after a maintenance upgrade improved energy efficiency. However, after 8 months, the building returned to its previous consumption pattern. The change in consumption was easily spotted in the visualization technology, reported to building engineers, and within two days the faulty valve was fixed. UNC estimates that this identified $30,000 of annual energy savings in that one building alone. What could be accomplished over the entire 300 asset inventory?

The progressive nature of UNC’s program is exemplary. Those of us working in the sustainability field at SAS believe that, in the future, managing carbon will be just as normal as managing your financial budget.

Please share your experiences with how sustainability contributes to the bottom line.

What a radical idea: break business intelligence down by the types of work to be done — financial intelligence, human-resources intelligence, risk intelligence, etc. — instead of by the technology — data warehousing, data integration, dashboards, etc.

“If I put my feet in the shoes of a business person listening to someone pitching ‘business intelligence,’ what I hear is another IT silver bullet,” eLearningCurve education director Dave Wells said recently.

If you’re talking to a finance officer, for example, about forecasting cash flows or predictive analytics based on the economy, you might call be describing “financial intelligence.” If you’re talking to an HR manager about retention, recruiting and pay scales, you might be talking about “human-resources intelligence.”

Imagine: BI terms that mean something — exactly what he’s trying to promote. Radical, but smart.

The article “Gartner maps zero-cost or low-cost CRM downturn strategies” outlines the need to “put analytics tools to work” to “improve sales and marketing discipline” and increase revenues. According to Gartner’s Scott Nelson,

“If the right strategies are employed now, then companies will get a ‘sling shot’ effect going into the eventual recovery, putting them well ahead of the rivals who chose to wait and who equate CRM success with spending more money on technology.””

This is where the LucidEra Pipeline Healthcheck service comes in. Designed for VPs of Sales and CEOs of organizations invested in Salesforce.com CRM, we do a full analysis of the sales pipeline, sales people and effectiveness of the overall sales process. Here are some examples of sales insight and intelligence that led to immediate pipeline impact.

Win Rate Analysis Leads to Revenue Growth

By looking at current and historical win rates by deal size we discovered that the win rate for smaller deals was actually half of the win rate for larger deals. Sales management had been trying to get their reps to sell more smaller deals, even though win rates were 45% for large deals and only 25% for small deals. This one metric resulted in a refocus on deals where they had a higher chance of winning. It also led to a discussion about potentially introducing a new offering that would be better suited to prospects looking for a lower price point.

Stalled Deal Analysis Leads to Reduced Risk

By analyzing historical snapshots we determine how deals have moved since a specific point in time. In one account, the Pipeline Healthcheck determined that towards the end of the quarter, even though deals in the later sales stages had progressed nicely, deals in the earlier stages had barely moved. Reps had been focusing on closing the later stage deals and not working the earlier stage opportunities. This led to a discussion about new strategies to focus on earlier stage opportunity development to get things moving and reduce the risk of these earlier stage deals losing momentum. In some cases, these discussions lead to an introduction to our partner Phone Works, who specialize in building inside sales organizations and optimizing CRM processes.

Aged Out Opportunity Analysis Improves Forecast Accuracy

By looking at win rates by the age of deals for a growing tech company in the Bay Area, the Pipeline Healthcheck found that the win rate for deals over 270 days old was only 8%, compared to their average win rate of 35%. Further analysis indentified that 20% of their current quarter forecasted pipeline was over 270 days old! Going back and requalifying all deals over 270 days old to find out which one were real and which ones weren’t resulted in immediate forecast accuracy improvements.

These are just a few examples of quantifable Pipeline Healthcheck impact. Tomorrow I’ll provide some examples of how the right sales analytics can impact sales rep and sales process performance. In the meantime, you might find this sales analytics podcast and this recorded webinar on the topic to be insightful.

A

I’ve been catching up on my reading on fraud strategy recently (I was asked to contribute a couple of pages to a new book on the subject). And whilst there have been some great strides forward on the technology front, it seems to me that we still haven’t fully faced up to the magnitude of the task at hand…

• In Cybersource’s Fifth Annual UK Online Fraud Report, their survey shows that ‘fraud losses consume more than 1% of revenue for 37% of UK online merchants; 13% lose more than 5% of their revenue’.
• According to APACS - The UK Payments Association, ‘UK card fraud losses for 2008 totaled £609.9m, online banking fraud losses £52.5m and cheque (check) fraud losses £41.9m …the main driver for growth in card fraud is on those transactions without chip and PIN protection’.
• Also, from KPMG’s Fraud Barometer - ‘More than £1.1bn of fraud came to UK courts in 2008  …the highest level recorded since 1995′.

I am not trying to imply that the world is sitting on its hands doing nothing - clearly not…

• Earlier this year, the UK Government (in the form of the National Fraud Strategic Authority) formally launched the National Fraud Strategy
• The NHS is having increasing success in catching fraudsters thanks to the efforts of the NHS Counter Fraud and Security Management Service (NHS CFSMS)
• And SAS continues to build on its software and services solutions to counter fraud.

But it’s only when these things are brought together that a truly comprehensive approach to preventing, detecting and resolving fraud becomes a reality.

A counter-fraud platform / framework that crosses the silos of an enterprise’s operations is vital in an age of ever more sophisticated fraudsters. As the UK’s experiences with Chip and PIN have shown, cutting down fraud in one area tends to migrate it to others (with Chip and PIN, the fraud migrated to Card Not Present frauds (calls centers and online) and a reemergence of cheque fraud). Counter fraud professionals are also aware that organized criminals are targeting businesses (including Financial Services institutions) from the inside – either attempting to get their own people hired, or engaging an existing employee with their criminal conspiracies – there is no point locking the front door if you are going to leave all the windows and the back door open.

So, the recipe for success in reducing losses to fraud requires:

• Addressing people (culture), processes and systems
• Suppliers, employees and customers
• A committed, continuing effort across the organisation.

So if you’re administering OBIEE and you’re looking out for connectivity issues, what’s the sort of thing that can go wrong? From where I’m standing, the major things to look out for include:

• The Presentation Server going down
• The Presentation Server being up, but the BI Server has gone down
• The Scheduler is down or hasn’t actually been configured properly
• The J2EE Application Server is down whilst the OBIEE components are up
• The Java Host server is down (and what does this affect?)
• All of the OBIEE components are up including the J2EE server, but the source database is down
• All of the above, but this time Essbase is down

If you’re an administrator, what is the impact of these parts of the system failing (we’ll ignore clustering for now), and if they are down how can you find out what’s wrong?

Starting with the Presentation Server, in theory it shouldn’t crash by itself but I’ve seen this happen when, for example, there is a bug in the Presentation Server code that caused a segfault when a pivot table contained too many cells. In general though, problems with the Presentation Server are generally because the process isn’t actually running, and when running with WebLogic as the J2EE Application Server users will get a message like the one below if they try and connect when the Presentation Server service isn’t running:

This error is caused by the J2EE Presentation Services plug-in not being able to communicate with the Presentation Services itself. If Presentation Services is running on a Windows box, a quick check in the Windows Services applet will show if the service is running OK, and as we can see in the screenshot below it’s actually down.

So why has it gone down? The best places to check are the log file at

• $ORACLEBIDATA/web/log/sawlog0.log

if the Presentation Server was up at one point but then crashed or closed down for some reason, or the Windows Event Viewer (System Log) if the service won’t actually start in the first place.

Unfortunately the messages in the System Log don’t really tell you what the problem is (the error above was actually caused by a developer making changes in the instanceconfig.xml file but entering the details incorrectly, which stops the Presentation Server service from actually starting) but at least if you try and restart the service and find that it won’t actually start, you can probably assume it’s due to a change in one of the config files and the next step is to find out who’s introduced the changes. In conclusion then, if users can connect to the J2EE application server but not to the Presentation Server, check that the service is up, check the sawlog0.log file to see if there’s been an abnormal exit, if there is then work out why, if not then try and restart the service, if it won’t then restart then check and see if anyone’s mucked around with the instanceconfig.xml and other Presentation Server configuration files.

So what if it’s not the Presentation Server down, but instead the J2EE Application Server host is down instead? If this happens, user’s won’t get a Java error message when they try and connect, instead they’ll get the usual “The page cannot be displayed” message (or whatever your OS and browser shows when a website can’t be reached).

In this case, you need to check whether your application server is up and what’s in the various log files, depending on which application server you’re using, as this is effectively out of scope for OBIEE, but if this component isn’t working (and thereby providing the bridge to the Presentation Server application) your users won’t be able to access any of your dashboards and reports.

Finally on the web front, what happens in the Java Host process is down but everything else is up? Well if this process is down, you should normally be able to log in but you’ll notice that your graphs aren’t being displayed, as the Java Host process amongst other things talks the third-party Corda process that renders all the graphs.

If you’ve got OBIEE integrated with EPM Suite and your are trying to log in using credentials stored in Shared Services, you’ll find this will fail as well as the Java Host process is involved in communication between the BI Presentation Server and Shared Services.

Again, if it’s down, check the Services Applet to see if it’s actually running, and if it’s not, check the log file at

• $ORACLEBIDATA\web\log\javahost\jhost0.log.0.log

to see if anything is stopping it from starting up (or caused it to crash). If you’re using EPM integration and you suspect the problem might be down to Shared Services, you can also check the log at$ORACLEBI\web\javahost\config\hss\logs\registry\registry.logo see if there’s anything in there. Incidentally, if you’re having problems authenticating against Shared Services but you think you’ve got all the elements set up correctly, the following two Presentation Services logs will show you whether authentication is failing (indicating that it can’t reach Shared Services) and whether Shared Services in in fact reachable from Presentation Services.

• $ORACLEBIDATA\web\log\sawlog0.log
• $ORACLEBIDATA\web\log\SharedServices_Security_Client.log

So what if all the Web elements are up, Shared Services is up but the BI Server is down? If this happens, users will be able to bring up the dashboard login screen but when they try and log in they’ll get an error message saying the BI Server is unavailable:

Expanding the error message as I’ve done above shows that the Presentation Server can’t connect to the BI Server via the BI Server ODBC client; again, first check whether the BI Server service is running, if it is but the Presentation Server can’t connect to it this may be because of a network issue (assuming they are on different physical boxes), but it’s most probably because the BI Server itself is down, either because something’s caused it to crash or someone’s tried to start it against an invalid or corrupted RPD. Taking a look at the BI Server log file at

• $ORACLEBI/server/log/NQServer.log

we can see that the reason this BI Server is down is because there are no valid subject areas in the repository, probably because they are all invalid or inconsistent.

So why might the BI Server, Presentation Server, Java Host process or indeed the Scheduler or Cluster Controller services be down? In my experience it’s usually because someone has fiddled around with the config files or tried to introduce a new RPD version that’s got errors in it, although it’s not been unknown for any of the server processes to fail or crash when experiencing an abnormal load (particularly when running them on less well used platforms, or when integrating the BI Server with newer technologies such as Essbase, Shared Services and the like). But generally it’s because someone has “done something” and as long as you keep daily backups of the config files and repository files you can generally get thing back up and running by restoring back the previous working copy of the relevant file.

Moving on then, what if all the OBIEE components are working but one of the source databases goes down? This one is fairly easy to spot as the error message shows up directly in the dashboard (assuming caching is not switched on and results are being retrieved directly from the cache); in the screenshot below, the report cannot run because the database itself is down:

in the example below, the database is up but the account being used in the connection pool settings doesn’t have access to the required tables.

To find out what’s happened, firstly check that the database you are using is actually up (a good way of doing this if all you have access to is the Administration tool, is to right-click on one of the database tables and select View Data), and in the case below, we can see that it’s because the TNS Listener is down that we can’t access the database.

To find out why it’s down, use the usual database investigative techniques (check if the listener is up, check if the database instance is up, check the alert log and so on). Also, if you’re getting a permissions error, check and see whether the developers have moved to :USER and :PASSWORD based connection pool logins, check and see whether the database account that’s being used to log in has the correct permissions, and indeed check to see whether someone’s not accidentally dropped the tables in question.

It’s a similar story for Essbase. If you try and run a report and Essbase is down, again you’ll get an error message in the report, like this:

and again, you’ll need to check whether the Essbase server process is running, whether there’s a network problem stopping OBIEE reaching it and so on, using the usual tools and log files to diagnose and resolve the issue.

If by the way you’re running the OBIEE server components on Linux or Windows, there are usually equivalents to the event viewer, process viewers and so on that you get in Windows, with for example the System Monitor application available under Red Hat Linux / Oracle Enterprise Linux.

When looking to see if the BI Server is up and running you need to look for the “nqserver” process, whilst the Presentation Server runs under the “sawserver” process name (hangovers from the old nQuire and Siebel Analytics days). If you’ve not got access to the GUI you can use “ps -ef | grep sawserver” from the command line, for example, to check whether a process is up. Once you’ve found that out checking through the logs is more or less the same as when running under windows.

So there you have it, a few tips on troubleshooting basic OBIEE connectivity issues across the various server components. If I’ve missed anything or you’ve got anything else to add, just add a comment to the post.

Using the Timeout connection string property is a good way of making sure that your queries don’t run for too long, but sometimes – for example when you’re using SSRS – you want to restrict the amount of data that a query returns. You can’t properly do this with Analysis Services, but it is almost possible…

Consider the following query on Adventure Works:

SELECT
{[Measures].[Internet Sales Amount],[Measures].[Internet Tax Amount]}
ON 0,
[Date].[Date].[Date].MEMBERS
ON 1
FROM [Adventure Works]

It returns 1189 rows and 3 columns. If you click on any of the cells containing data in SQL Management Studio, to see the cell properties, you’ll see that the CellOrdinal property contains the index of each cell in the cellset. So the top left hand cell is ordinal 0, the one to its right is 1, and so on until the last column where it starts again one row down:

Using the BeginRange and EndRange connection string properties, you can limit the cells in a cellset that actually get populated with data. Note that you can’t restrict the overall number of cells though, which would be more useful. Both these properties take an integer value which represents a cell ordinal: BeginRange is the first cell ordinal you want to contain data, EndRange is the last cell ordinal. Their default value is –1, which for BeginRange means start at the first cell ordinal and for EndRange means end at the last cell ordinal. So, for example, with BeginRange=4 and EndRange=7, running the query above would give the following output:

As I said, the overall number of cells in the cellset remains the same, but only the cells in the range we specified actually contain data. This ‘filtering’ happens after the query axes have been resolved, as far as I can see, so adding NON EMPTY on Rows for example does not filter out any of the empty rows. If you were using SSRS, however, you could do this filtering at the DataSet level.

If you look in Profiler you’ll see that these properties have an affect on the amount of work SSAS does at query time. On a cold cache, with no BeginRange and EndRange set, the query scans all of the year partitions in the Internet Sales measure group as you would expect. But with BeginRange and EndRange set as above, on a cold cache SSAS only reads data from the 2001 partition.

BTW, remember that if you’re experimenting with these connection string properties in SQLMS, when you’re finished you’ll need to either close and reopen SQLMS or set BeginRange=-1 and EndRange=-1 as a result of this bug (which still doesn’t seem to be fixed in SP1).

From a reader who identified with “That’s not BI“: His product has performed BI functions for many years, though it still goes unacknowledged except by users. “Sometimes the looking down the nose from the cognoscenti gets to you.”

The TDWI San Diego conference opens in just 10 weeks, and some people are already thinking about who they hope to avoid.

“We’re sure not going to hide out in a stairway,” one promises but has no other strategy so far. Speculation about who’d win should it come down to a good old bar fight, yes. But only in fun.

One of these knows what an accidental meeting can be like. After avoiding someone for two days, he slipped into an elevator to find his nemesis there filling the space. Going down?

I’m over in Singapore at the moment, and the client I’m working with is looking for some help with troubleshooting Oracle BI EE installations. This is an interesting area because as developers, we generally focus on building new applications rather than supporting existing ones, but if you’re then handing these systems off for other people to support you need to give them some guidance on how to solve problems. So if you’re going to support an Oracle BI EE system, how do you go about troubleshooting them?

If we are considering Oracle BI EE troubleshooting, to me there are three areas we need to cover:

1. What indicators are there of current and potential problems?
2. What are some of the common problems we might encounter when supporting Oracle BI EE?
3. Are there things we can do to minimize the chance of problems occuring?

So to start with the first point, what indicators are there of potential problems? Well the first place I would look would be the various logs that the Oracle BI EE server components generate. The most obvious logs are the BI Server, BI Scheduler and BI Cluster Controller ones:

• $ORACLEBI/server/log/NQServer.log (the BI Server “alert log” file)
• $ORACLEBI/server/log/NSQuery.log (the BI Server query log file)
• $ORACLEBI/server/log/NQSheduler.log (the Delivers/scheduler log file)
• $ORACLEBI/server/log/NQCluster.log (the Cluster Controller log file)

with the NQServer.log one being the most useful when diagnosing startup and other issues with the BI Server. In the example below, the BI Server can’t start up because there are no available subject areas in the RPD file (in this case, due to developers saving the RPD with consistency errors).

If anything goes wrong with the BI Server, or causes it to do something unusual (like not start usage tracking, or not be able to connect to a required external authentication mechanism) it’ll appear in this log. If a user reports that Oracle BI EE is unavailable, particularly if the dashboard interface comes up but it can’t connect to the BI Server, this is the place to look. It also makes sense to check this log file anyway, on a regular basis, in case it’s reporting an issue that users haven’t spotted yet but that might cause problems for you later on.

The other log file that’s worth keeping an eye on is the Presentation Services log file, found at

• $ORACLEBIDATA/web/log/sawlog0.log

This log file records failed login attempt (useful to see if someone is trying to hack into your system, or you’re having problems authenticating against LDAP or Shared Services, for example), and it also shows issues you might be having with dashboard elements and reports.

An couple of examples of where this particular log file were useful include:

• On an Oracle BI Applicaitons implementation on AIX, the Presentation Server would crash and segfault on particular dashboard pages, and when we looked in the Presentation Services log we could see that certain pivot table reports had a high level of cells which should normally have been caught by a setting in the instanceconfig.xml file but instead was causing the Presentation Server to crash.
• When setting up EPM and OBIEE integration, we could see that incoming login requests from the EPM Dashboard were being rejected as the Presentation Server wasn’t properly handling the incoming SSO token.

Together with the NQServer.log file, these two files should be the first place you look if the BI Server or Presentation Server are either failing to start, regularly crashing or exhibiting strange behavior. In addition, if you’re lucky enough to have the Enterprise Manager BI Management Pack, there are various screens within the pack for remotely monitoring the contents of these logs.

Other logs that you will want to keep an eye on include:

• $ORACLEBIDATA/web/log/javahost/jhost0.log (Java host log)

This is the log file for the Java Host process, and can be useful in diagnosing problems when setting up the Hyperion Custom Authenticator amongst other things.

The logs generated by the J2EE application server hosting the Presentation Services plug-in can be useful as well. The log files for OC4J and Weblogic 9.2 can be found here:

• $ORACLEBI/oc4j_bi/j2ee/home/log/server.log
• $BEA_HOME/user_projects/domains/bi/servers/AdminServer/logs/AdminServer/logs/AdminServer.log

In addition, if you are running the Oracle BI components on Windows, the Application log in Event Viewer shows you when the server components fail to start or shut down abnormally, from what I can see though this is more or less the same information that’s in the NQServer.log file albeit in a centralized place that systems management tools can make use of.

Under Red Hat Linux / Oracle Enterprise Linux, the equivalent utility is the Systems Logs application, and I guess there are similar utilities available for the various Unix equivalents.

Outside of OBIEE, problems you might encounter might be down to the underlying database or OLAP server either not being available or failing to authenticate the user credentials being passed across by the connection pool, so it makes sense to keep an eye on the Oracle Alert Log, for example, to see if the problem lies with the database or just to spot potential database problems before they hit the user.

So, that’s it for log files and places to diagnose potential problems. Do you know of any others that you find useful on a regular basis? I’m off to work now, but later on I’ll continue this series with some tips on what problems you are likely to encounter on an Oracle BI EE system, how you can do some preventative maintenance to avoid them, and also a high-level view of how to troubleshoot an attached Essbase OLAP server and Shared Services.

I like the message in this 5-star Force.com AppExchange review LucidEra Pipeline Insight received today:

A Better Way

“LucidEra really is a better way to expose Salesforce.com data in a way that helps VPs of Sales (and sales executives) to “understand the numbers.” It is easy to use (and learn). Highly recommended.”

Short, sweet and to the point. You can check out this and other LucidEra Pipeline Insight reviews from Salesforce customers here.