| 8:24 am on Jun 16, 2004 (gmt 0)|
We are a MS-SQL shop but present all of our reports in Excel format.
Basically they are set up as pivot tables querying the MS SQL database using OLAP. We can then massage the data any way we want, without having to change queries or involve IT.
I am no an expert on Oracle by any stretch of the imagination but I thought Oracle was OLAP compliant?
Is that going to acheive what you want?
| 12:38 am on Jun 24, 2004 (gmt 0)|
Does this provide dynamic updates back to the MS-SQL database (and therefore relatively easily back to Oracle)?
Do you do updates back to the source?
| 12:51 am on Jun 24, 2004 (gmt 0)|
You can use the standard pivot table refresh function (the bight red exclamation mark) which updates the data.
At my present company, we simply use the standard pivot table commands. At a previous company, that had thier developers put some VBA script in the front end to make it much prettier and easier. Depends on how many people are going to be using it I guess.
By allowing the data to be put into Excel, I can manipulate the data any way I want and do my own analysis without waiting for any IT involvement. This provides greater flexiability.
There is a heap of resource in TechNet, on the Excell Help and the Office Developer site.
| 1:03 am on Jun 24, 2004 (gmt 0)|
Excellent - thanks.
Forgive my ignorance (and laziness) but can you lock the spreadsheet from column and row insertions and deletions and also sorting it into complete garbage, or do you have to rely on well educated users?
| 3:20 am on Jun 24, 2004 (gmt 0)|
Haven't tried. You may be able to lock the spreadsheet but does that defeat the purpose of doing it in Excel - i.e. maniuplate the data yourself.
If you don't want people to change the look of the data, why not present it as an online (Internet or Intranet) style of report? Especially if the queries are static against the database.
| 3:37 am on Jun 24, 2004 (gmt 0)|
I'm looking for something that builds 'a model' in the morning (fixed number of columns and rows but you can change the look - the formulas for derivations are static) that also provides the functionality of Excel:
* grab bits of data and plot stuff (different plots all the time);
* tweak some data (but not add columns or rows or sort it);
* assess the impacts of that tweak on other bits of 'the model' and tweak a bit more;
....and then commits the tweaks (only) back to the Oracle database.
You've pointed me in right direction though so thanks very much for that.
PS. I've also considered xml'ing (or something else) to some product or other but have not found the product - there will be no new data - just changes to existing data - and no changes to formula. The original structure (from Oracle) can be locked during the time between creation and the updates coming back - so the mapping is one to one.