Withdrawal of MS Access by Organisation, "replacing" with Business Objects - Some Key Ques 1

[NWildblood]

Hi, an open question regarding functionality of BO.

My (large) organisation is finally upgrading from Windows 2003 to 2010. However, although we have had no clear communication on this, it is widely expected that MS Access will not be part of the Windows suite (due to cost / maintenance.)

I am gobsmacked to learn this as I am a data analyst, and I use MS Access for a number of ad-hoc, large volume but "quick and dirty" data processing tasks, typically using a combination of VBA and SQL.

The rather vapid response I have had (within an Asset Data Management environment...) is that Business Objects (which we already use for a number of querying and reporting tasks) will "replace ms access"

Although there are probably too many Access DBs across the organisation doing business as usual tasks, my fear is that the assumption that has been made is that Access is only being used for such tasks, so, Sorry for such a greenhorn question but:

Is it possible to import data sets and set up and run ad hoc tasks using VBA and SQL ??.
If so, there is scant info about it on the web.
If not, should I simply face-palm and consider getting another job ?
(The latter question is rhetorical)

Any insight-bringing responses gratefully received - thank you !


No hay nada como un buitre cabrón como un ojo de cristal.

 

[hilfy]

I can see how this will be a difficult transition. Basically, one or more universes will have to be created to allow any data that is being pulled into MS Access for analysis. A universe does not actually contain data. Instead it is a "metadata" layer between the user and the data which, if properly set up, allows the user to access data without knowing much about the underlying data tables and joins. The user then drags various "dimensions" and "measures" into a query definition in, for example, Web Intelligence (Webi) which then builds the SQL behind the scenes based on the info in the universe. The result can then either be analyzed in Webi or it can be exported to Excel for further "slice and dice" analysis.

While Webi is not a difficult tool to learn, the quality of the results is VERY dependent on how the configuration of the universes it uses to build the SQL. So, I would try to get more information about how the universes you will be using to do your analyses will be defined and who will be doing that work (it's usually not the end users) and then make a point to work closely with those folks to make sure that you get what you need, not what they think you need.

Depending on your system configuration, you might also be able to set up what's called "Query as a Web Service" (QaaWS) where you define a query based on a universe and then publish that as a web service that can be consumed by any software that will connect to a web service. You could then potentially pull this information into Access or Excel.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[NWildblood]

Thanks Dell

Fortunately (!) from experience and after reading around the subject a little I was formulating an understanding not a million miles from what you have said.

We already use the Web Intelligence for querying & reporting on a Universe that has been set up to interrogate underlying data tables in a large asset maintenance database. So your first para makes perfect sense.

With regard to your second paragraph, yes that's what I imagined the process may look like in the next 6-24 months. There is also a very large asset register database that was built in the 70s that contains flat files only and is updated via a terminal emulator. The cost of replacing it has proven prohibitive and several attempts to do so over the last 3 decades have been aborted (although the support license runs out next year apparently.) If we need to interrogate it at any scale, for management, regulatory, customer or strategic reasons, we run (usually overnight) reports to .txt files, which we then import into MS Access, and off we go. Unfortunately my organisation does like to maintain demarcation between functions and getting some insight & input on the Universe's build - to "work closely with those folks to make sure that we get what we need" - may be a big ask - but a really appreciate your feed back; I can now try and build this understanding and collaboration into my objectives for the year.

Thanks again, you've been very helpful.

Rgds


No hay nada como un buitre cabrón como un ojo de cristal.