Dealing with personal files has become a harder task with the latest integrated platforms. Higher centralized security together with the presence of web-based tools has reduced the amount of control that users have over the information.
In order to give some power back to users, SAP BusinessObjects has provided rich client tools that enable users to access some of their personal files. However this feature is limited to excel and text formats and causes the corporation to lose control of the files inventory because users are then able to store files in their own machine.
For those companies who wish to have a tight administrative control on their content but at the same time provide quick access to personal files such as text, Excel or even through free hand SQL’s (FHS) , the concept of Universes on top of personal data might be a good solution.
Universes on top of Excel, Text or Free Hand SQL
The concept is simple: the idea is to demand from users which data they want to monitor in their systems and give it back to them in Universe format. They may then use it in their reports as if it was corporate information, either through merging dimensions in WebI or building Queries as a Web Service to populate front-end modules such as WebI itself or Xcelsius.
Universes on top of Excel or Text files will typically have the following features:
- ODBC generic connections using the Microsoft Excel or Text native drivers
- Every text file or every Excel tab will appear as a Universe table
- Tables in Designer do not generally have joins, that is to say, tables show like isolated “islands”
- A Universe class is typically corresponding with an isolated table and it can be secured so it can be seen for the user only
In the case of FHS, if personalized SQL sentences are needed, we will act similarly but with the following differentiations:
- The Universe connection is the native connection of the data source
- Every table in the Universe is a derived table containing the query
Text files Roadmap
Read and apply the following guidelines to use text files as personal data sources:
- Place all the text files in a common folder
- Define a Microsoft text driver pointing to that folder
- The folder acts as a database
- Text files act as tables
- The key here is to define well the schema.ini file. Normally the assistant fails to create it so it needs to be adjusted manually (see sample below)
- Create an empty universe using an ODBC generic connection pointing to the driver, add the text files as tables, link them if necessary, create objects and export (use aliases if format brings Errors)
- Export the universe
Fig1. A sample of a schema.ini file
Excel files Roadmap
Read and apply the following guidelines to use Excel files as personal data sources:
- Place all the Excel files in a common folder
- Define a Microsoft excel driver pointing one of the files in the folder
- The folder acts as the database instance
- The Excel files act as the databases
- The Excel tab files act as the tables
- Create an empty universe with a Microsoft Excel connection pointing to the driver. Keep in mind that there is no alternative as an ODBC generic connection will not work!!
- Add the tables, link them if necessary, create Objects and export (use aliases if format brings Errors)
- Export the universe
A very good feature we can take advantage of is that a driver pointing to one file will be enough to retrieve all Excel files in that folder.
Free Hand SQL Roadmap
Read and apply the following guidelines to use personalized SQL calls as personal data source:
- Define a native driver pointing to the desired database
- Create an empty universe with a proper connection pointing to the desired database
- Create derived tables without linking. The content of them will be the [SELECT XX FROM XX WHERE…] sentences that we want to pull from the database. Use AS command if we want to give a different name to the field
- Create Objects
- Export the universe
Conclusions
With these pieces of advice a system to access personal information can be built into your corporation’s BI system. With a minimum of three universes we can query these three types of files. A drawback is that for Excel or text formats only single access is possible, so only one user at a time can access that information. This can be bypassed configuring universe security accordingly so one class can only be seen by one user.
This method provides quick visibility on personal data which is ideal for actions that require a single access like building prototypes & demos or for sequential scheduling and distribution of reports.
If you have any questions or comments about this concept, please feel free to get in touch.