Could I load an Excel file to the Universe and have it act as a data provider? So it would just be another table in IDT and I could link it to other tables / derived views?
I know how to use Excel as a data provider in a WebI report (and then merge dimensions). But that requires a report to be created and then send around to people’s InBoxes … this is too much overhead.
If I could load Excel to the Universe, then I could build the data properly and it’s all self service reporting - no jiggling with the Excel file in a WebI report.
The Supported Platforms document says that MS Access / Excel is supported using an ODBC (Office Access ODBC) only on windows.
I’ve never worked with Excel and only worked enough with Access until I ported the databases over to SQL Server.
I’m guessing from the description, that you would not be able to add the Excel file based table to an existing universe has it requires it’s own connection. Depending on your report requirements, it might work to have your Web Intelligence report query two data sources and then merge dimensions similar to what you are doing now, but your Excel file would not have to be distributed to your users.
I routinely do this for data not held in the database (or not held in a sensible format). The main issue is merging suitable variables - generally descriptions as ID objects in the database may or may not be static.
The other thing is maintaining the structure of the file - always append new columns to the right of the file, don’t insert them in the middle of existing columns. Then it’s just a matter of checking the source data periodically, and if it’s been updated, do a new export and replace the existing file in Business Objects. Also make sure tab names remain the same - one of our source systems renames tabs every financial year…
I also use Excel uploads when I need to do a lot of min/max/previous calculations to make the output into a dimension for further calculations.
Loading to Webi and merging, we don’t have access to the universe. Which is annoying, as it means I can’t use the mapping table in query conditions - hmm, just had a thought about that, I wonder if preselecting as a separate query would work.
I would like to link the Excel tables to the universe, but that’s not possible as our IT department doesn’t have the skills to do it let alone maintain any custom configurations. However, it’s been done (I hope!) with the new data lake which will be supporting Power BI.
Hope it keeps working! The main thing to do is to thoroughly document it so that when your universe is upgraded your admin can link the Excel table to the new universe.
I really wish I had the skills (and access) to do this for our universe; there’s a number of pieces of information that need to come from external data which I add using Excel files uploaded to Webi and replaced when necessary. Unfortunately, our IT wants to drop BO as we’ve now got a ‘data platform’ to use with Power BI; so far, we’ve fended them off as not all the tables are included in the refresh - it takes more than 24 hours to do a complete refresh so we’re waiting for our software supplier to implement delta loads.
