How to get data from Analysis Services cubes into Access

Our users are keen on using Access Reports/Queries against Analysis
Services cubes for formatted reporting.

Please let me know how I could connect to Analysis Services cubes and
report in Access.

Thanks
Karen

You would be better off looking at reporting services for something
like this I think. I don't think anyone on this group would recommend
using access for this.

if you want to mail me I can point you in the right direction of people
who can provide services etc. or even some better recommendations if
you can give me some more information on the requirement.

Steve

personally, I would reccomend using Access over Reporting Services-- but
these jerks at Microsoft are too afraid to deal with the markeing problems
associated with Access.

Access Reports against OLAP would be AWESOME



"Steve McHugh" wrote in message
ups.com...
You would be better off looking at reporting services for something
like this I think. I don't think anyone on this group would recommend
using access for this.

if you want to mail me I can point you in the right direction of people
who can provide services etc. or even some better recommendations if
you can give me some more information on the requirement.

Steve

ps, it is easy to flatten hierarchial recordsets using OLAP as a linked
server to SQL Server.

So you go to your SQL Server, you setup OLAP as a linked server; and then
you can use OLAP data in Access however you want.

This way, you only have to install OLAP drivers in once place-- on the db
server.

Since installing OLAP drivers is like 1/2 of the complexity of implementing
OLAP, I really reccomend it.

Personally, Microsoft keeps on screwing around with drivers, just so that
they can keep on squeezing office upgrades out of people.

-aaron






"Karen Middleton" wrote in message
om...
Our users are keen on using Access Reports/Queries against Analysis
Services cubes for formatted reporting.

Please let me know how I could connect to Analysis Services cubes and
report in Access.

Thanks
Karen

Steve

I am just curious-- what is your major problem with Access?

Are you too stupid to know that the default repository in OLAP uses Access;
and that Access/JET drives these products:

EXCHANGE
OUTLOOK
PROJECT
MONEY
WINDOWS ACTIVE DIRECTORY

I just dont like your bigotry against Access.

Access is the best solution for many situations-- I don't like your
attitude.

Karen--- fight the power, Access rocks.

WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!


"Steve McHugh" wrote in message
ups.com...
You would be better off looking at reporting services for something
like this I think. I don't think anyone on this group would recommend
using access for this.

if you want to mail me I can point you in the right direction of people
who can provide services etc. or even some better recommendations if
you can give me some more information on the requirement.

Steve

Aaron
In the same way that you don't like Steve's attitude (I would prefer to use
the word opinion), I don't care for your rude reply. This online community is
for friendly help and assistance, not for trading insults.

Your use of the word "bigotry" is unwarranted. As I said, Steve was merely
stating his opinion.

AS does indeed use Access as its default repository. Doubtless Microsoft
would rather have been able to default the repository to an enterprise RDBMS
(for all the benefits that it brings) but many/most people simply don't have
one of these at their disposal. Yes yes, I know that if you have AS then you
have SQL Server but thats not the whole story. Microsoft have also realised
the folly of relying on a seperate storage medium for the repository and have
done away wth this completely in Yukon.

In my opinion Access is not a viable solution for deploying enterprise-wide
reports. One simple reason for this is that it requires software to be
installed on users' machines. Reporting Services (to take one such example)
only requires a web browser which everyone has anyway.

I myself do not care much for access due to problems I have experienced with
it in the past around concurrent access, .mdb file size explosion (by orders
of magnitude), limited programability and lack of logging capability.

If there was a significant requirement for Access reports against OLAP in
the user community then I'm sure Microsoft would provide it. That they
haven't probably means the requirement isn't there.

Regards
Jamie Thomson


"aaron kempf" wrote:

Steve

I am just curious-- what is your major problem with Access?

Are you too stupid to know that the default repository in OLAP uses Access;
and that Access/JET drives these products:

EXCHANGE
OUTLOOK
PROJECT
MONEY
WINDOWS ACTIVE DIRECTORY

I just dont like your bigotry against Access.

Access is the best solution for many situations-- I don't like your
attitude.

Karen--- fight the power, Access rocks.

WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!


"Steve McHugh" wrote in message
ups.com...
You would be better off looking at reporting services for something
like this I think. I don't think anyone on this group would recommend
using access for this.

if you want to mail me I can point you in the right direction of people
who can provide services etc. or even some better recommendations if
you can give me some more information on the requirement.

Steve

Jamie -- Agreed....

/Michael Vardinghus


"Jamie Thomson" wrote in message
...
Aaron
In the same way that you don't like Steve's attitude (I would prefer to
use
the word opinion), I don't care for your rude reply. This online community
is
for friendly help and assistance, not for trading insults.

Your use of the word "bigotry" is unwarranted. As I said, Steve was merely
stating his opinion.

AS does indeed use Access as its default repository. Doubtless Microsoft
would rather have been able to default the repository to an enterprise
RDBMS
(for all the benefits that it brings) but many/most people simply don't
have
one of these at their disposal. Yes yes, I know that if you have AS then
you
have SQL Server but thats not the whole story. Microsoft have also
realised
the folly of relying on a seperate storage medium for the repository and
have
done away wth this completely in Yukon.

In my opinion Access is not a viable solution for deploying
enterprise-wide
reports. One simple reason for this is that it requires software to be
installed on users' machines. Reporting Services (to take one such
example)
only requires a web browser which everyone has anyway.

I myself do not care much for access due to problems I have experienced
with
it in the past around concurrent access, .mdb file size explosion (by
orders
of magnitude), limited programability and lack of logging capability.

If there was a significant requirement for Access reports against OLAP in
the user community then I'm sure Microsoft would provide it. That they
haven't probably means the requirement isn't there.

Regards
Jamie Thomson


"aaron kempf" wrote:

Steve

I am just curious-- what is your major problem with Access?

Are you too stupid to know that the default repository in OLAP uses
Access;
and that Access/JET drives these products:

EXCHANGE
OUTLOOK
PROJECT
MONEY
WINDOWS ACTIVE DIRECTORY

I just dont like your bigotry against Access.

Access is the best solution for many situations-- I don't like your
attitude.

Karen--- fight the power, Access rocks.

WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!
WE DEMAND ACCESS REPORTS AGAINST OLAP!!!!!!


"Steve McHugh" wrote in message
ups.com...
You would be better off looking at reporting services for something
like this I think. I don't think anyone on this group would recommend
using access for this.

if you want to mail me I can point you in the right direction of
people
who can provide services etc. or even some better recommendations if
you can give me some more information on the requirement.

Steve

Geez,

don't you hate it when you go away for a few hours and come back and
find this......

Let me clarify a few points.....

Firstly I have nothing against Access. Hey I learnt my trade on it to
start with and found it to very useful when creating nice small, easliy
distributable, single user databases with some reporting bolted in.

Secondly the AS repository is in Access because Analysis Services
doesn't have to rely on SQL Server at all, yeah you can migrate it but
there are a hell of a lot of implementations running it off of DB2 or
Oracle out there. If things were different then I'm sure it would be a
native MSDE (like the one Access is running off of now possible)

Thirdly the construction of the MDX for reporting services and access
would be identical except i don't have to issue a copy of access to
everyone who wants to view the reports and i get a hell of a lot of
bonus' from an app specificly designed for reporting (e.g. security).

Access will always have a place in my heart and a need in the business
world, there's no getting away from that, but for reporting out of a
cube I wouldn't touch it.

No witch hunt, just an opinion..!

Steve

Jamie, Aaron & Steve

Many thanks for all of your feedbacks on using Access reporting to
Analysis Services.

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.

Last but not the least we are already using reporting services it is
not for end users - somebody should not expect us to give Visual
Studio .Net to Power users it is still a developer tool.

I make a strong case for Access despite all the issues you have
pointed out I can give power users access to create adhoc formatted
tabular print quality reports customised by them and publish them as
Data access pages for web access which I cannot do today on reporting
services maybe one day when Microsoft releases something like
ActiveViews or whatever they call it as nobody knows when ActiveViews
or Yukon or coming. So we want to use something that is already there
like Access.

I was seeing if I could use Access Pivot reporting against Analysis
Services unfortunately Microsoft as not provided that feature as in
Excel.

Thanks
Karen

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