How to get data from Analysis Services cubes into Access

[Karen Middleton]

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 McHugh]

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 kempf]

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

 

[aaron kempf]

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

 

[aaron kempf]

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!!!!!!

 

[Guest]

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]

Jamie;

No, there is a significant demand for this, and Microsoft is too cheap to
come up with a solution. In their most profitable division; the most
profitable company of the world decides that they can't afford to fill the
needs of their customers. WAKE UP MICROSOFT!!!!

SOMEONE ISN'T DOING THEIR JOB RIGHT-- FIX THE BUGS IN MICROSOFT ACCESS; AND
EMPOWER US TO REPORT AGAINST OLAP. CANNED REPORTS AGAINST OLAP.
HIGH-PERFORMANCE, CANNED REPORTS AGAINST OLAP.

HIGH-PERFORMANCE, CANNED REPORTS AGAINST OLAP. THAT IS WHAT WE DEMAND.

Microsoft Access-- ADP-- is the best reporting solution for SQL Server; and
it has been around for 4 years. Screw reporting services (ok, so i admit
it-- im implementing reporting services right now).

While people were out spending hundreds of thousands of dollars on crystal
reports-- and reporting services; I was there doing ADP reports for large
companies.

I just think that Excel is a total waste of peoples times.. you look at any
large company.. perhaps 1/2 of the users spend 50% of their time typing
stuff into Excel. THAT IS A TOTAL JOKE-- COME ON MICROSOFT-- GIVE US THE
TOOLS THAT WE ARE ASKING FOR!!! Give us the tools so that we don't need to
pay these beancounters $50k each.. just to make the same report
Month-After-Month.

Give us the right tools to kick these bean-counters to the curb!!!

Let us make our companies EFFICIENT!!!!!

I just worked in an environment (MSN) where people typed the same report in
Excel every friggin month.. There were people, making boatloads of money..
and they spent all their time; typing stuff into Excel... copying and
pasting in Excel-- I mean.. give me a break!!! That is SOOOOO 1995!!! I
mean-- It would have been EASY to implement something with Access that would
have automated all of it-- it would have MAYBE taken me a week.

BUT NO. EVEN _WITHIN_ MICROSOFT, THERE IS A MAJOR MARKETING PROBLEM WITH
ACCESS; AND MICROSOFT HASN'T EVER DONE ANYTHING ABOUT IT.

Access is a different market than Excel-- but Access-- as a general rule--
is more efficient than Excel for writing canned reports. Access has a real
object model. Access has about 100 times more functionality than Excel.
Access can import and export data; and it is 100x times more powerful than
Excel for reporting on SQL Server data.

This isn't about welfare-- we don't need to keep these beancounters in a
job.. Microsoft is purposefully handicapping Access. AND IT IS BULLSHIT.

GIVE ACCESS OLAP REPORTING. WE DEMAND IT!!! MAKE ACCESS STABLE-- WE DEMAND
IT!!!

You can pass parameters into Access for example; and you can't do stuff like
that in Excel. You can export to a snapshot report in Access-- you can't do
that in EXCEL. WHY CAN'T YOU BUILD SOMETHING THAT WILL PRINT XLS INTO SNP--
AND THEN WE CAN HAVE ONE VIEWER INSTEAD OF HAVING TO INSTALL THE MICROSOFT
DOCUMENT IMAGING BULLSHIT ANYWAYS?? I mean-- who came up with the idea of
needing to include a seperate tool-- similiar to PDF-- when Office 2000
already included this?? Why couldn't you just use the SNP viewer for
everything.. It is already on peoples' machines??

But no-- there is this stigma against Access... and Microsoft is out of
touch with the needs of their customers.

I mean, Microsoft-- you have failed to execute; and I am sick and tired of
this bias against Access. It is unfounded. It is a result of you still
trying to push ODBC-linked bs in MDB. I mean-- MICROSOFT MAKE ADP A REAL
PRODUCT, TEST IT AND GIVE IT OLAP REPORTING ABILITIES. TAKE AWAY THE NAME
ACCESS FROM ADP-- INFOPATH & SHAREPOINT ISN'T EVER GOING TO MEET THE NEEDS
OF SMALL BUSINESSES. I DONT WANT ANYTHING TO DO WITH THE MARKETING PROBLEM
THAT MDB HAS-- SET ADP FREE, MICROSOFT.. OR AT LEAST START TAKING IT
SERIOUSLY AND FIX BUGS WHEN THE COMMUNITY FINDS THEM.

I am sorry that Microsoft doesn't understand the marketing problem that they
have with Microsoft Access. Microsoft doesn't understand the database
market. And that is why Microsoft is losing in the SQL Server vs. Oracle
war. That is why Microsoft is losing-- because they can't deal with the
marketing problems associated with Access; and they have NEVER been able to
shake the marketing problems that are a result of the stability problems of
MDB.

Oracle Forms and Oracle Reports-- these compete with ADP; and yet Microsoft
doesn't take it seriously.
RPG developers--- still developing forms in AS/400.. Just because Microsoft
has a marketing problem and they refuse to deal with it.

THIS IS THE FRONT-LINE IN THE BATTLE BETWEEN SQL SERVER AND ORACLE. AND
MICROSOFT IS REFUSING TO COMPETE. WE DON'T NEED ANOTHER WEBPAGE. WE DON'T
NEED TO MAKE EVERYTHING INTO A WEBPAGE. CANNED REPORTS; THAT ARE SIMPLE AND
CAN PRINT--- MAKE IT HAPPEN MICROSOFT.

Microsoft Access is the best product on the market; but Microsoft refuses to
take it seriously.

SQL Server Reporting Services isn't going to make Access go away-- So what
gives for the future of Access?

I just wish that Microsoft would spin ACCESS into an independent company.
Microsoft hasn't had a drop of innovation with Access since Office 2000.
DAP is a complete failure.. But that doesn't mean that ADP is a waste of
time.

WHY CAN'T MICROSOFT EITHER SPIN OFF ACCESS OR MAKE IT OPEN SOURCE?? SO THAT
WE CAN AT LEAST FIX THEIR BUGS FOR THEM-- SINCE MICROSOFT REFUSES TO FIX
BUGS IN ACCESS-- SET US FREE!!!


Aaron Kempf
(e-mail address removed)
(e-mail address removed)

I CANT WAIT TILL GOOGLE COMES OUT WITH A DATABASE!!!!


PS - anyone that claims to understand the db market that hasn't read this
report should be fired:
http://www.fmsinc.com/tpapers/genaccess/DBOD.asp

 

[Michael Vardinghus]

Jamie --> Agreed....

/Michael Vardinghus

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

 

[Steve McHugh]

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

 

[Karen Middleton]

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

 

[markie]

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]

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)

 

[Mark Hill]

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

 

[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

 

[Karen Middleton]

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

 

[Guest]

Why the fuss about Access/OLAP??

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

 

[Guest]

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

 

[Karen Middleton]

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

 

[Guest]

Karen,
You've some good ideas there. Send them to (e-mail address removed)

cheers
Jamie