How to get data from Analysis Services cubes into Access

I prefer keeping it in Access; so that you can

a) stop the SQL Service and use memory for OLAP.

This is really necessary if you have OLAP and SQL Server on the same
machine.

b) not have to assign permissons to each user in 2 places (on the OLAP side
and on the SQL repository side)


"markie" wrote in message
oups.com...

aaron,

Yes you are right, the Microsoft Analysis Services repository is stored
in Access when it comes out of the box, however standard best practice
(access has a nasty habit of getting itself corrupted) is to migrate
this to a grown up database like SQL Server as soon as possible.

Quote from MSDN
"The Access format is used so that users who do not use SQL Server for
relational data can still use Analysis Services. However, if you do use
SQL Server, migrating the repository to a SQL Server database adds
enterprise-level scalability, support, and security. Migrating the
repository also enables you to perform coordinated backups of the
repository database with a file-based backup of the Data folder. For
more information, see "Backup and Recovery" later in this paper.
"

In terms of Reporting Services VS Access for reporting, Access did have
a reporting engine that was away ahead of its time, however time has
moved on , and i would recommed Reporting Services not least for its
ability to scale effectively ....

Sorry that you feel we are dissing Access, i am sure that in some niche
areas it still has value to add.

Mark

"aaron kempf" wrote in message ...
I prefer keeping it in Access; so that you can

a) stop the SQL Service and use memory for OLAP.

We run production systems where SQL Server and OLAP are on the same
machine, if you are planning on having your SQL Server answer queries
for other reporting as well as just feeding the cubes then splitting
them is best practice, totally agreed. If however all your SQL Server
was doing was serving the repository then you could limit the amount
of memory that SQL Server uses , as the repository stuff is tiny. So
still dont see why you would need to keep the repostitory in access.

b) not have to assign permissons to each user in 2 places (on the OLAP side
and on the SQL repository side)

Not sure what you would have to assign permissions for the repository,
remember MSAS does not use the repository at query time, only at
process time. In fact it does not use any of the metadata at query
time. The only user that needs to see the repository is the one that
the MSAS service is running under.

At the end of the day we all have different opinions, this is what
keeps IT such an interesting area ... Nobody on this thread i am sure
means any disrespect to the other (especially Steve, as i know him),
but its cool to get some of these discussions in the open so other
people can learn and make their own choices from them.

Mark Hill

Please note the main reason something like Access as such a unique
place in OLAP reporting is there are so many times when the OLAP cube
data needs to be joined with a relational table and most of these some
power users would like to do this the data in the relational table
could be few hundred records we cannot expect to give a SQL Server or
Oracle and DTS to all these people. Power users being familiar with
Access would love to get the output of a Analysis Server cube into
Access and merge it with other data and report.


Karen

I think this is an interesting scenario, maybe you can share with us
the kind of business problems you are trying to solve with this
approach ...

What kind of data do you need to add to your existing cube data, is it
not possible to add this data to the cube as a dimension , why do you
need to merge the data in Access.

Mark Hill

Thanks all to your inputs.

Just a suggestion perhaps Microsoft instead of using Visual Studio
..Net as the development environment when other ISV vendors have
developed integration of Excel with Reporting Services.

Perhaps Microsoft can take a cue from this and atleast consider Access
as the development environment to create and publish Reporting
Services reports. This will also boost licensing for MS Access.

After all these discussions I could not get much out of it I ended up
developing a integration using VBA and ADO for MDX to get the data
into Access. I am so happy at what I can do in Access with the data
from OLAP cube that I could not do in Excel.

I would just suggest more people think more seriously at the value add
of Access in a OLAP environment and put significant pressure on
Microsoft to consider integration of Analysis Services and Access for
reporting.

Thanks
Karen

Why the fuss about Access/OLAP??

If you are bent on reporting through Access, source the base tables
directly...



"Karen Middleton" wrote:

Thanks all to your inputs.

Just a suggestion perhaps Microsoft instead of using Visual Studio
..Net as the development environment when other ISV vendors have
developed integration of Excel with Reporting Services.

Perhaps Microsoft can take a cue from this and atleast consider Access
as the development environment to create and publish Reporting
Services reports. This will also boost licensing for MS Access.

After all these discussions I could not get much out of it I ended up
developing a integration using VBA and ADO for MDX to get the data
into Access. I am so happy at what I can do in Access with the data
from OLAP cube that I could not do in Excel.

I would just suggest more people think more seriously at the value add
of Access in a OLAP environment and put significant pressure on
Microsoft to consider integration of Analysis Services and Access for
reporting.

Thanks
Karen

uh, the fuss about Access is that we've all been writing reports in Access
for 10 years; and you lil punk kids come along and tell us to migrate
everything to reporting services.

Reporting Services doesn't PRINT very well. Excel doesn't print as well as
Access can-- If Excel came out with _REAL_ reporting functionality-- instead
of copying and duplicating numbers-- then MAYBE we could use Excel. As it
is; Excel is just a waste of time. Excel is just a money hole. Too many
people use the exact same product to create the exact same report every
month. a waste of time nonetheless.

Access can export to Excel. Does Excel have a 1-button click to export to
Access?
Reporting Services can export to Excel. Why can't reporting services
publish data as an MDB?

Microsoft hasn't innovated; hasn't done _anything_ for Access developers
since Access 95 came out.. All they've done is fixed bugs. They came out
with ADP in office 2000.. But they won't fix MAJOR bugs in ADP.. and DAP are
_SO_ 1996. I mean-- comeon microsoft--

It's like they keep on adding functionality; making new products (infopath
and sharepoint and all that other web CRAP).. but you forget about your
faithful Access developers.. Who have been writing reports and ACCOMPLISHING
THINGS in the past 10 years.

Time to throw us a gift, Microsoft-- give us OLAP reporting in Access.

Put some database people in charge; Redmond. From top to bottom-- Make
Access a _REAL_ reporting solution.

Microsoft has major holes in thier product offerings-- they expect us to be
100% microsoft shops-- but they keep on forgetting about lil details like
PRINTING.
A lot of companies need to PRINT financial reports-- every company in the
whole wide world does.. and Reporting Services; Excel-- SQL Server-- none of
that stuff does a very good job of PRINTING.


-Aaron
ADP Nationalist


"luminary" wrote in message
...
Why the fuss about Access/OLAP??

If you are bent on reporting through Access, source the base tables
directly...



"Karen Middleton" wrote:

Thanks all to your inputs.

Just a suggestion perhaps Microsoft instead of using Visual Studio
..Net as the development environment when other ISV vendors have
developed integration of Excel with Reporting Services.

Perhaps Microsoft can take a cue from this and atleast consider Access
as the development environment to create and publish Reporting
Services reports. This will also boost licensing for MS Access.

After all these discussions I could not get much out of it I ended up
developing a integration using VBA and ADO for MDX to get the data
into Access. I am so happy at what I can do in Access with the data
from OLAP cube that I could not do in Excel.

I would just suggest more people think more seriously at the value add
of Access in a OLAP environment and put significant pressure on
Microsoft to consider integration of Analysis Services and Access for
reporting.

Thanks
Karen

Thanks for some good feedback.

Most situations Excel to Analysis Services linkage is sufficient there
are so many situations where I need to combine data from 3 or 4 other
cubes and combine and report which perhaps even a virtual cube cannot
achieve. I have gone down that track of trying to invent some complex
MDX and still achieve it I could do this by getting data from AS cubes
into Access and combining it in Access and reporting it.

- Bottomline - how quickly can you do this. I guess SQL language is
still very powerful when compared to MDX.

- Last but not the least formatted reporting in Access is so easy I
would not bother to go and setup reporting services to do some adhoc
formatted reporting atleast not in the current incarnation of
reporting services with need for Visual Studio to author reports - I
agree this is a very big area for Microsoft Innovation to link up
Analysis Services, Access and even reporting Services.

If I am to add a suggestion the Access data pages is such a quick way
to web enable even data from Oracle or SQL Server as linked tables
perhaps Microsoft should seriously consider a feature in Access to
enable Access reports to be deployed into Reporting Services reports.
So that way Access could be a fantastic report authoring tool and
still as its place.

A word of advice to people questioning Access the latest version of
Oracle 10g uses an extension of SQL to query OLAP cubes in Oracle. So
the interface of AS cubes to Access almost lets developers a SQL
access to OLAP data in AS 2000.

Thanks
Karen

Karen,
You've some good ideas there. Send them to

cheers
Jamie

"Karen Middleton" wrote:

Thanks for some good feedback.

Most situations Excel to Analysis Services linkage is sufficient there
are so many situations where I need to combine data from 3 or 4 other
cubes and combine and report which perhaps even a virtual cube cannot
achieve. I have gone down that track of trying to invent some complex
MDX and still achieve it I could do this by getting data from AS cubes
into Access and combining it in Access and reporting it.

- Bottomline - how quickly can you do this. I guess SQL language is
still very powerful when compared to MDX.

- Last but not the least formatted reporting in Access is so easy I
would not bother to go and setup reporting services to do some adhoc
formatted reporting atleast not in the current incarnation of
reporting services with need for Visual Studio to author reports - I
agree this is a very big area for Microsoft Innovation to link up
Analysis Services, Access and even reporting Services.

If I am to add a suggestion the Access data pages is such a quick way
to web enable even data from Oracle or SQL Server as linked tables
perhaps Microsoft should seriously consider a feature in Access to
enable Access reports to be deployed into Reporting Services reports.
So that way Access could be a fantastic report authoring tool and
still as its place.

A word of advice to people questioning Access the latest version of
Oracle 10g uses an extension of SQL to query OLAP cubes in Oracle. So
the interface of AS cubes to Access almost lets developers a SQL
access to OLAP data in AS 2000.

Thanks
Karen