topic Re: OLAP with Office Add In write back in SAS Web Report Studio

OLAP with Office Add In write back

metalray — Thu, 12 Aug 2010 12:08:19 GMT

Hello,

does the SAS add in for MS Office has similar write-back capabilities to the cube as the Oracle Hyperion Smart View (MS Office Add in) ?

Thanks.

Re: OLAP with Office Add In write back

AngelaHall — Fri, 13 Aug 2010 02:50:52 GMT

1st, please remember - everything in Hyperion is a Cube.

In SAS - you have the ability to send back information from Add-in to MS Office into a data table, if you have access to do so. From Excel, with the workbook open, select the drop down box next to 'Active Data' and select the option: 'Copy to SAS Server...'

Navigate to the location you would like to move this data to.

You can not edit the cube from here. However - you can provide the users a Stored Process to execute from here that includes the PROC OLAP code so they can copy the data back up to the server & execute the Proc OLAP refresh - all from Add-in to MS Office.

~ Angela
http://sas-bi.blogspot.com

Re: OLAP with Office Add In write back

metalray — Fri, 13 Aug 2010 08:05:53 GMT

Hi Angela,

Thanks for the clarification.
I find the functionality of the Hyperion Block storage cube
quite handy. Users can easily send back data to the cube
(e.g. update an individual account) and if the members are set to dynamic calculation the roll-up is right up to date as well.

assuming that my sas olap cube is fed from an oracle staging area, in that
case, do I undertand correctly that the users update the staging area (data table) and then I would need to develop a little refresh procedure that
pushes the new data through to the sas cube?
if that is so the sas olap storage model is different from hyperions block storage model.

Re: OLAP with Office Add In write back

AngelaHall — Fri, 13 Aug 2010 18:36:56 GMT

Yes, the storage model is dramatically different in a typical OLAP setup.
If you want to 'push' the changes quicker, I would suggest researching the ROLAP model (where OLAP cube data is stored in a RDBMS such as SQL/Teradata/Oracle). There is a potential to modify the cells within a table directly and have those changes available immediately within the OLAP Cube report.

Included are some ROLAP papers:
> http://support.sas.com/resources/papers/proceedings09/103-2009.pdf - provides an overview
> http://www2.sas.com/proceedings/sugi31/219-31.pdf pg6 offers detail on how to point large aggregations to pre-summarized tables stored in a RDBMS.

~ Angela
http://sas-bi.blogspot.com

Re: OLAP with Office Add In write back

metalray — Thu, 26 Aug 2010 16:01:59 GMT

Hi Angela,
Thanks for the info, I better be carefull to avoid the drama/"dramatically".
I am aware of the different models, ROLAP, MOLAP, OLAP, since I worked quite a while with Hyperion solutions.
Thanks,
Philip