BusObj: Object failed to run due to insufficient security privileges.
I had a situation recently where scheduled Business Objects reports began to fail with the error message: “Object failed to run due to insufficient security privileges. “. Having spent some time googling the error there seem to be many potential causes if permissions have been changed but in this case it turned out that a user had been deleted. In Business Objects (XI R3.1 at least), when a user is deleted their scheduler instances will be allocated to the Administrator who, despite having scheduler privileges, appears not to be able to execute scheduled reports.
The only solution I could find was to manually reschedule all reports previously owned by the deleted user, if anyone has any better solutions or knows of a method to make a bulk change rather than individually I would love to hear it!
Categories: Business Intelligence, Business Objects Tags: BO, BOXI, business objects, error, scheduling, Security, XI, XIR3
Quick Fix: Invalid Parameter Number (WIS 10901) in BOXIR3.1
I encountered this error whilst testing a Stored Procedure Universe in BO XI R3.1, given that it says “Invalid Parameter Number” you would naturally assume that it was something relating to the stored procedure itself, passing parameters from the front-end to the DB in SQL, etc. but in my case it was a classic red herring error message.
The issue turned out to be permissions problem, the user executing the stored procedure did not have permissions to execute it. After running the following SQL statement everything ran perfectly…
GRANT EXECUTE ON my_dwh.dbo.usp_do_something_useful TO PUBLIC
… though bear in mind that you may not want to grant permissions to everyone (‘public’), I just use it as a simple example.
Categories: Business Intelligence, Business Objects, Microsoft SQL Server Tags: BO, Business Objects, error, Stored Procedure, Universe, Web Intelligence, WebI
The Database Countryside Code: Best Practices for BI & SQL Users
Those ‘City Folk’ among you may not be aware but in Rural England we have what is called The Countryside Code, it’s a set of guidelines that everyone should follow in order to keep the countryside clean, tidy and a nice place to visit. You may be asking – what does this have to do with Business Intelligence and Database Administration? Well, I think it’s vital – if we all follow a fairly simple but broad set of guidelines then all classes of database user will have a better experience from Developers to DBAs and Analysts to CIOs. This isn’t really about making your databases perform better, it’s about working better with each-other and taking other people’s perspectives on board. Having been in most of the related roles over the years this is what I’d put into The Database Countryside Code…
1. Enjoy the countryside and respect its life and work
Whether your application is an ‘out of the box’ software suite, a Business Intelligence package that can be tweaked on implementation or a hand-crafted bespoke solution if you’re running against a database maintained by someone else or shared with other applications you need to take heed of this point. Remember that cooperation is key and if you build a good relationship with the DBA and the other key users of the database you’ll have a much better time of things and if there are any critical issues you’ll be included in the remediation process and may even be able to help your own users get back online faster. It’s easy to see DBAs as grouchy, narrowly focused sorts who tend to view all user activity as bothersome (I can say that as I’ve been one myself) but generally speaking if the DBA is aware of user activity at all the chances are that there’s already a problem as it’s the long running, resource intensive activity that will stand out in alerts and performance reports. Before your application goes live you should do some testing, run your designs and SQL statements / stored procedures past the DBA for some advice (but remember, you don’t have to take it) and establish some sort of procedure for reporting issues, and remember that an SLA can work both ways as you may need the DBA’s help as much as they might need yours.
2. Guard against all risk of fire
Security is a huge issue and as exploit frameworks and toolkits become more and more prevalent and feature-rich the likelihood of vulnerabilities being discovered in our applications should be treated more like a certainty. If you’re developing bespoke applications and especially web apps you’ll need to pay close attention to the OWASP Top 10 application security risks but from a database perspective the most notable threat is SQL Injection - the art of passing SQL into an application so that it might be executed by the database (as a good starting point check out OWASP’s SQL Injection Prevention Cheat Sheet). If you’re deploying packaged apps or BI tools don’t think that you’ve gotten away with it, the primary responsibility may be on software developers to avoid exploits but if they’re baked into an application you’re implementing it will affect your users and your business, so…
3. Protect wildlife, plants and trees
The most important security contribution we as implementers can bring to the table is to review and limit the privileges required by our applications. Many install guides and expensive external consultants ask for a ‘dbo’ (database owner) level user and some even ask for ‘sa’ (system administrator) or ‘root’ level privileges but don’t hand these out like candy on halloween. In most cases these high-level privileges are only required during setup and install and can be removed afterwards but often basic read/write access is all that is required (and for BI tools often read-only), it may only be achievable through a few frustrating rounds of trial and error but if you assign your applications the lowest possible permissions you will significantly reduce the risk of compromise in the future. Another important step during implementation is to make sure that your permissions are segregated, where possible have a separate user for each service and an entirely separate user for accessing each database not shared by any other application. Whilst it may seem excessive this setup will allow you to audit any security issues and identify which user was compromised and exactly what they had access to.
4. Fasten all gates
Many Business Intelligence tools include some degree of control over connection management and if you’re developing your own application you’ll have complete control over all database connections, the decision to be made is whether connections are ‘pinned’ open, closed after x minutes or closed at the end of each transaction. The preference will vary depending on the load and the usage, in most Business Intelligence use cases there tend to be a large number of users, not always connecting concurrently and issuing fairly large queries against the database followed by periods of quiet whilst a report is read – in this case there is usually no need to keep the connection open for long. On the other hand if you have users issuing a constant stream of small transactions (e.g. a Point of Sale system) the overhead of creating and dropping connections might actually add load to the database so it would be more effective in this scenario to maintain the connection.
5. Keep your dogs under close control
This applies more to developers and BI architects where your dogs are your users, if you are deploying an application that creates load on somebody else’s database you should do whatever you can to limit each user’s ability to cause long running queries – in some BI tools you are handed an option to let a query time out after x minutes and perhaps limiting the number of rows returned. If you are developing your own application you should include both of these options but make sure that you kill the query at the database level rather than just killing the thread in your application that made the request otherwise it’s equally bad if not worse since the user may simply re-issue the offending query. The actual limits are bound to vary from database to database but that’s where the first point comes in, discuss this with both your users and the DBA.
6. Keep to public paths across farmland / Use gates and stiles to cross fences, hedges and walls
When it comes to solving problems try to stick within the basic and simple boundaries of an ordinary user, avoid using undocumented stored procedures, excessive use of user defined functions, custom data types, plugins and extended stored procedures or anything else that strays too far from a standard install of the database platform. Obviously you’ve got an app to deploy and you want to solve your problems in whatever way is best for your users but the further you are from a standard deployment the more issues you’re likely to encounter, both you and the DBA might be fully aware of this amazing new setting you tweaked to make things run better but a couple of years down the line during a disaster recovery will it all come flooding back quite as easily? What if one or both of you that setup the application have moved on to other roles? Thinking outside the box is great but be conscious of introducing risk and if you do feel that it is necessary then make sure that it’s well documented in the Run Book or the corporate wiki.
7. Leave livestock, crops and machinery alone
Since you may already have elevated privileges on your own database, a shared database or even the server you may be tempted from time to time to perform maintenance tasks or make minor ‘improvements’ to indexes or configuration settings – do not do so without the DBA’s blessing. If you’re following the rules above you’ll probably have a fairly good rapport with the DBA already so it’s likely that you’ll be granted some level of trust not to mess things up but be careful not to overreach, the DBA will be ‘in the loop’ of many changes and other requirements (e.g. critical deadlines, disaster recovery tests, unplanned maintenance) whereas you may not be aware of them so before you make any changes run them past the DBA – just in case.
8. Take your litter home / Help to keep all water clean
If you’ve ever been a DBA you’ll have seen, on more than one occasion, tables popping up called tmpSomethingorOther, tblToBeDeleted or TableName_bak but when it comes to the key questions (How long have these been around? Are they still required?) nobody seems to have a straight answer. I know myself that whilst I’ve been developing data warehouses I’ve created these sorts of tables and subsequently forgotten what they were used for, not too much of a problem if you’re ‘the guy’ but in a large team or with personnel changes over time it can be hard to know what is required and what isn’t – I came to a database once with temporary tables over five years old which had not been deleted out of fear that they were important. The moral here is an obvious one, clean up after yourself or if the table must exist for some short period of time put a note in your diary to come back and cull it.
9. Make no unnecessary noise
Be mindful of what errors you raise and what you write to public logs, if your application causes a large amount of data to be written to database or other centrally collated logs you may inadvertently make it harder to detect genuine issues which will hurt both you and and other users of the database. If you do occasionally need exhaustive logs consider adding a ‘debug mode’ into your application which can be turned on or off via a configuration setting, that way you can turn it on whilst you’re tracing a fault and need more verbose logging then turn it off when you’re done.
10. Take special care on country roads
There can be plenty of unexpected hazards on country roads so don’t always rush around everywhere at 60mph, acknowledge that whist you might want everything to go as fast as possible you could be causing some other critical process to slow or stop. Driving at night can be treacherous too as you might come across an unexpected backup window or import/export process, talk to your DBA and coordinate the major tasks. If it’s a shared server make sure you have access to the task list so that you know where to slot in your jobs and that those jobs get put back into the master list.
Really it comes down to one thing, as the great and wise Jerry Springer oft said, “take care of yourselves, and each other”.
Categories: Business Intelligence, DBA Tags: BI, DBA, logging, performance, Security, sql
You reach a fork in the road…
Recently I’ve been working more in the smartphone world than in BI and SQL although for the time being at least I’ll be doing a bit of both so you’ll probably see more mobile app related posts in the feed. In order to provide some degree of separation I’ve created a Mobile User Interface Blog at usabl.net which contains links and screenshots of interesting designs, though any longer more technical articles will still appear here.
If you’re wondering what the crossover is between mobile apps and Business Intelligence it’s all about the user experience, in BI you generally aim to give people the information they need as quickly and efficiently as possible not necessarily in terms of software performance but in report design. I’m not the only one that’s thinking along these lines, Jen Stirrup gave an interesting talk on Data Visualisation at the inaugural SQLHerts meeting and if you’re interested in either topic you’ll most likely be able to catch both of us at the next meeting on July 28th at the University of Hertfordshire (register here).
Categories: Business Intelligence, Mobile Apps Tags: Android, BI, iPhone, Mobile, sql, sqlherts, visualisation
SQLBits by the Sea a.k.a. Nerds on a Beach!
Who’d of thought it – it’s early April and I’ve come back from a SQL Server technical conference with sunburnt arms and a smile on my face. Usually when I tell people that I’m going to a Microsoft SQL Server conference they sarcastically say things like “have fun”, or “rather you than me” and when I tell them that it also runs into Saturday and that I’m not even getting paid for it they’re even more incredulous. I can understand the attitude since on paper it sounds like a rather dry affair but nobody that’s ever attended a SQLBits event would ever agree with that sentiment because it’s the strangest thing, somehow against all odds the SQLBits organisers have built an event crammed full of technical talks from some of the leading lights of the industry and they’ve also made it fun. The craziest thing of all is that the Saturday ‘community day’ is also completely free and with over 40 talks it’s every bit as good as the the paid days, couple that with the prizes and other swag on offer from the sponsors and you can’t fail to walk away better-off than you went in!
Friday’s keynote was delivered by Microsoft’s Mark Souza and gave an insightful rundown of some new features coming in the next release of SQL Server codenamed Denali, there were some interesting High Availability and Disaster Recovery (HADR) features, a new Column Store styled index, the new data visualisation / dashboard tool Crescent as well as some additional enhancements that sound minor but will probably yield a large benefit (Windows Core support, Contained Databases, FileTable). Also sharing the stage with Mark was David Flynn, CEO of Fusion-IO who gave a short but interesting overview of their ioDrive product which can provide amazing performance increases by moving IO bottleneck from the SAN onto NAND Flash directly attached to the PCI Express bus – though some nearby graffiti (pictured) showed that there’s still some loyalty to old storage media!
My top talks of the weekend included Jamie Thompson‘s session on SSIS Performance and Vincent Rainardi‘s talk on Advanced Dimensional Modelling but my ‘best of show’ goes to Martijn Evers’ talk on Data Vault – a data modelling technique that complements use of the well established Kimball and Inmon approaches to data warehousing. I took a lot of useful hints, tips and tricks away from many of the talks I attended but the Data Vault talk gave me something much more valuable – an entirely new idea, something I’d never heard before but will make me think about data modelling in a slightly different way. It’s the inclusion of these satellite topics (i.e. not directly related to SQL Server) that really makes SQLBits stand out and one of the reasons I keep coming back. Aside from the talks my other highlights were sitting on the beach eating lunch and getting my first go on the Xbox 360 Kinect!
It’s worth mentioning that the entire event couldn’t happen without the sponsors and in particular I think a lot of good will should be shown to Microsoft. Windows users tend to see them as a giant corporate entity that they never get to interact with but somehow it’s different in the SQL Server product team and they make a lot of effort drafting in some of their best people including Thomas Kejser, Ewan Fairweather, Lubor Kollar, Mark Souza, Connor Cunningham and Andrew Fryer (who completely schooled me on Kinect boxing). Many of these guys fly in from the US and of course Microsoft can afford it but the point is that they don’t make a big deal out of it, these guys run sessions and wander around conference offering support to anyone that asks – where else do you get that?
There’s nothing quite like listening to a talk from someone who’s utterly engaged in what they do and wants to share the information with others and that’s what SQLBits is all about so if you’re a SQL Server developer or DBA you just have to go, it’s that simple but if even you’re a .NET developer or work with other database and BI products it’s well worth attending – for now I’ll look forward to seeing you all at the next SQLBits.
Categories: Business Intelligence, Data Modelling, Events, Microsoft SQL Server Tags: Data Vault, Denali, FusionIO, SQLBits, SSIS
SAP BI 4.0 / Business Objects XI Release 4 Launch Rundown
Today I attended the SAP BI 4.0 launch event at the Royal College of Physicians in Regent’s Park London, the first major launch event for Business Objects since being firmly under the SAP banner lending it a degree of expectation amongst the BO user community. With all said and done the day had a slightly unusual structure, interleaving the “new features” sessions with “the future of BI” and “other interesting stuff” talks so the remainder of this post covers the main themes of the day and the “big picture” topics whilst I’ve broken out the new features into a “What’s New in Business Objects XI Release 4 / SAP BI 4.0?” post.
The morning’s main talk came from SAP’s Technology Evangelist, Timo Elliott (for those of you from the SQL Server world think of a cross between Brent Ozar and Andrew Fryer) who delivered a punchy and informative overview of the ‘big ticket’ enhancements coming in 4.0. The main themes of Timo’s talk an of the day in general were the forthcoming enhancements in the Enterprise Analytics space including the recently acquired Column-Oriented data store Sybase IQ and AP’s latest iteration of In-Memory analysis, HANA (High-performance ANalytical Appliance). HANA will run on hardware from vendors such as IBM and HP with upwards of 1TB RAM and sit between SAP BW and other large data sources providing lightning-fast (up to 350x faster in SAP tests) though later iterations of HANA will all-but replace the current storage engine behind SAP BW (planned late 2011) and ultimately will replace the entire data storage infrastructure behind SAP’s ERP systems and potentially other third-party applications.
Timo’s enthusiasm for these new technologies clearly showed and having been in the industry (and the company) for 20 years it’s worth noting that he described the advent of large-scale in-memory analytics as a “once in a decade” leap in capability and for Enterprise-class organisations I’m quite sure it will be but having worked in much smaller companies I’m somewhat sceptical about how much of an impact it will make at the lower end of the market.
Another major theme for the day was the advent of Analytic Applications, essentially packaged BI and Data Warehouse products pre-built for specific industries (e.g. Healthcare, Retail, Manufacturing) or for departmental purposes (e.g. Finance, HR). Demoed by Jeff Veis and Andy Hirst, these applications are presented as a series of dashboards but since much of the underlying KPI definitions and data architecture are already built they can reduce implementation time to as little as 12 weeks vs. 6-9 months for a ‘from scratch’ implementation. It’s easy to be sceptical about this as we all tend to believe that our problems are unique but each application is focused so closely on a particular industry/department that even if they’re only able to meet 70% of the core requirements out of the box the simplicity and reduced timescales ought to be well worth the sacrifice, especially since they’re customisable after the initial setup.
The third major theme of the day was Data Quality, in fact in addition to the session on Information Steward by Barry Dodds and Dave Pugh four other speakers made a point of telling the audience that everybody in the room had data quality problems – it’s probably true but I couldn’t help feeling a little nagged by the end of it all! The tool itself seemed very capable and for a DQ application it was remarkably visual and included dashboard-style elements (to paraphrase Barry) “using analytics to improve analytics” which despite being a cool soundbite is a actually a very sensible approach to take.
Also announced was the new Complex Event Processing engine Event Insight, essentially these CEP engines (like Microsoft’s StreamInsight) take an incoming stream of events in real-time from operational systems and provide monitoring and alerting capabilities as well as processing for more traditional reporting and dashboarding. Additional products mentioned but not thoroughly explored were a collaboration tool sapstreamwork.com and a new unstructured text processing engine that is able to parse free text such as Twitter feeds and provide “sentiment analysis” as well as tagging various context indicators including geography.
Roadmap wise we were told to export more along the lines of Pervasive BI, Big Data, Social / Collaboration and more in the Mobile BI space. On the latter we should expect enhancements to the existing Business Objects Explorer mobile app as well as a native WebI application, mobile platforms mentioned included Blackberry, Symbian, Windows Mobile, iPhone, iPad and even the RIM Playbook but oddly no mention of Android. I’m not sure if it was left off of the slide by accident or there are legitimately no Android plans, I’d assume the former since Android is almost certain to become the market leader in terms of wide-spread adoption.
In addition to the Business Objects staff there were also a couple of external speakers both of whom gave interesting talks…
Tony Harper of Capgemini who spoke on the general topic of Mobile BI, highlighting the increased user expectations presented by high quality consumer-oriented smartphone and tablet apps as a particular challenge. The talk was thought-provoking and in particular it Tony’s statement that Mobile BI projects will be “sending information farther from the walls of the data centre than ever before” really underscored one of his main themes that providing so many people in so many disparate locations live access to your data will significantly stretch both performance and data quality and these expectations should be factored into Mobile BI projects from the beginning.
Following Tony was Alys Woodward from the research firm IDC who gave a good talk on the factors influencing BI uptake within organisations listing the most important contributing factors as being as Degree of Training (including training on KPIs as well as the tools), Design Quality (of architecture and processes), Non-Executive Involvement (i.e. get the business users involved), Importance of Governance and Use of Performance Management Methodology (the last two being important drivers in organisations where they are relevant).
Don’t forget to check out my ”What’s New in Business Objects XI Release 4 / SAP BI 4.0?” post too for more detail on the core Business Objects product stack.
Categories: Business Intelligence, Business Objects, Sybase, The Cloud Tags: Analytical Applications, Andrew Fryer, BI, BI 4.0, Blackberry, BO, Brent Ozar, Capgemini, CEP, Column-Oriented, Data Quality, DQ, ERP, Event Insight, HANA, IDC, In-Memory, Information Steward, iOS, iPad, iPhone, IQ, Mobile BI, Playbook, SAP, SAP BW, Sybase, Timo Elliott, WinMo, XIR4