Help with Cube design

[aceavl via SQLMonster.com]

i've been playing with this for a week and i'm not sure is the right tool for
what my boss wants.
they want to give the people in customer service an olap cube so that they
can query it and have all the data for reports.

BUT i think it's just too much data, they want ALL the tables and when they
do the report thay want it like in excel eg(in columns - Name - Sex - Address
- Tel - TicketNumber - LastDateofPurchase.... and a lot more) so i made a
cube but in excel 2007 when i try to put those fields it just hangs and
crashes.

what are my options?

thanks!

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-olap/200912/1

[lgnurf]

you can do listings from olap cubes, but it performs very badly in bad olap
clients (excel). the more dimensions or attribute hierarchies you add to the
rows, the worse the performance.

i would suggest the following options, if you want to use olap cubes
- probably the better option is to use drill-troughs, which you can activate
from excel. you define them in the actions tab of the cubes in Visual Studio,
and you can tell which columns to return.
- use an advanced client like proclarity, where you will be able to do a lot
more of these cross-joins (but there aren't really good clients which are not
complicated, expensive or end-of-life)
- use reporting services or the like, where you can write MDX directly and
use non empty crossjoins

otherwise you have to base the "listing" reports on the relational star-
schema behind your cubes.. and you can duplicate your security roles, etc..
you defined in the cubes

to my knowledge, there aren't any real solutions to this problem. but
everyone has the issue. the advantages of olap cubes are immediate for
aggregate data, but for detailed data you have some limitations.

[Classiarius]

Sounds like a problem better solved by the relational database to me.
I solved a similar problem by deploying an Excel workbook where I
wrote all the queries to our relational DB and consequently have full
control to optimize the queries used. The workbook made heavy use of
macros to make it dirt simple for end users with a few button clicks
and even incorporated an auto-update feature for new revisions. Most
queries returned in less than 2 seconds for 2,000 rows by 200+ columns
of data, with pivot tables being automatically created on the returned
table data. Larger queries took proportionally longer simply due to
network I/O.

[aceavl via SQLMonster.com]

thank you for answering!

lgnurf:
i'll try with drill-throughs and DWE

Classiarius:
the problem is that right now we have someone full time writing querys to
make reports.
the personel who is going to use this doesn't know sql so it would almost be
the same.
we'd have to keep writing the querys when they want some other piece of info.

:)

--
Message posted via http://www.sqlmonster.com

[Classiarius]

That's the beauty of my approach, my end users don't know SQL either.
The end-users simply click a button in Excel and the query (we made)
runs, optionally, using some of their input in other predetermined
cells. When we add a new feature(s), I simply create a new workbook,
and update my auto-update lookup table and Excel auto-loads the
updated workbook when end users click the "Ok" prompt on the Excel
popup presented upon opening an depreciated version.

[aceavl via SQLMonster.com]

Classiarius:

i hate to ask for files but could you send me an example of you workbook?
i think that's what i'm going to do.

ace...@gmail.com

[Classiarius]

Sorry, but no. Not only would that be a gross violation of corporate
policy, it would be rather unreasonable in my industry.

Questions are still free. 8)

[lgnurf via SQLMonster.com]

a thing that i've used in the past are .dqy files, which open automatically
with excel and run a relational query.

they are very simple and you can easily create them from a macro, if you need
to parameterize the query. they have 4 lines:

XLODBC
1
the Connection String
the SQL statement

however i think you are better off using reporting services reports, even if
they are not the best. Excel is not really a reporting tool. there are
hundreds of things which can break in a spreadsheet, plus you will need to
give read access to users to your sql database.

Classiarius wrote:
>> Classiarius:
>>
>[quoted text clipped - 7 lines]

>> --
>> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-olap/200912/1
>
>Sorry, but no. Not only would that be a gross violation of corporate
>policy, it would be rather unreasonable in my industry.
>
>Questions are still free. 8)

--