Drillthrough Functionality via Excel Add-in

Hi,

I'm using the Analysis Services Excel Add-In to query a Cube built with partitions.

When using the drilldown functionality for my current\default partition the function seems to work fine.

However, when I try to drilldown on data in the other partitions, the following error message appears:

"Unable to Drillthrough on this Cell"

Would someone please be able to shed some light on this?

Many Thanks

Jonathan Derbyshire

Jonathan_Derbyshire@.Hotmail.com

We have the same problem (also using OWC controls). do you have the solution?
if yes, please, let me know.
Thanks

Carlos
tfcarlos@.hotmail.com
|||No I'm still stuck.

If anyone can help I would be very grateful...

Thanks

Jon Derbyshire|||I think Excell can't drillthrough over partitiones.
I'm not sure but if you have more than 1 partition you can't drillthrough. Did you try to execute the drillthrough query against an ado connection?
It's a large cube?
Do you have to show many columns in drillthrough?
Because you can make some calculated measures to solve this (be carefull with the result).|||Why do you think that Excel cant drillthrough over partitions? Have you read this somewehere or are you just speculating?

I can drillthrough - but only on the current/default partition...

Its a Medium to large cube.

Drillthrough shows about 20 columns, but I've tried limiting it to 5 and still the same error.|||I'm speculating.
But I found the solution:
In addition to enabling drillthrough at the cube level, you can also modify the options on each individual partition in cubes that contain more than one partition. You can access a dialog box analogous to the one in Figure B through the Partition Wizard—to access the Partition Wizard, right-click the partition and select Edit from the context menu—by clicking the Advanced Settings button and Drillthrough Options. The resulting Partition Drillthrough Options dialog box lets you change the columns and filter to use when creating drillthrough data from that partition. The ability to set drillthrough options on individual partitions means that if you execute a drillthrough operation on a cell that aggregates data from more than one partition, each partition returns its own result set and the columns in those result sets can differ from one another. Application developers need to be aware of this behavior so that they can display the results accordingly. The administrator's next task, configuring drillthrough security, requires that you use the Cube Role Manager dialog box and click the Drillthrough setting for the role you want to grant access to. Each role has a security setting, called Allow Drillthrough, that you can select to let that role execute the queries that perform a drillthrough operation. After you enable drillthrough and configure security, you can implement drillthrough for applications.
Text From Implementing Drillthrough for Developers in http://www.windowsitpro.com/Articles/Print.cfm?ArticleID=41679|||Thanks - I've been through and it appears that drillthrough was already definitely enabled...

It still doesnt enable me to drillthrough as I wish. I've posted my question on the site you provided in an attempt for more help.|||I want to ask you.
Are you using sql 2000 or sql 2005?
If you are using sql 2005, are you connecting from excel in the server or in a remote computer?
If you are connecting from a remote computer, what have you installed in the computer to making it works?
I can't connect from a remote computer with excel to AS 2005.
Thanks in advance|||2000 version

DrillThrough - How to do it under OWC

I define the Drillthrough and able to browse the data under Analysis Manager.
But I can drillthrough under OWC(Office Web Components).
Can anyone let me know how to do it?
Look at the OWC Tool Pack -- it has an example.
http://www.microsoft.com/downloads/d...DisplayLang=en
Hope that helps.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kam" <Kam@.discussions.microsoft.com> wrote in message
news:A9BF9E7B-0697-461B-B7E3-914AEBF4F0FF@.microsoft.com...
> I define the Drillthrough and able to browse the data under Analysis
Manager.
> But I can drillthrough under OWC(Office Web Components).
> Can anyone let me know how to do it?

DrillThrough - How to do it under OWC

I define the Drillthrough and able to browse the data under Analysis Manager
.
But I can drillthrough under OWC(Office Web Components).
Can anyone let me know how to do it?Look at the OWC Tool Pack -- it has an example.
http://www.microsoft.com/downloads/...&DisplayLang=en
Hope that helps.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kam" <Kam@.discussions.microsoft.com> wrote in message
news:A9BF9E7B-0697-461B-B7E3-914AEBF4F0FF@.microsoft.com...
> I define the Drillthrough and able to browse the data under Analysis
Manager.
> But I can drillthrough under OWC(Office Web Components).
> Can anyone let me know how to do it?

Drilldown Problem Using Parent Hierarchy

Does anyone know how to create a parent-child hierarchy that can be expanded (ie drilldown capabilities) in reporting services as well as analysis services?

Currently, I have a dimension with three attributes: parent, child, and key. Everything works fine in Analysis Services. When I click browse for the cube and I add the parent-child hierarchy, Analysis Services autogenerates a drilldown from the top level to the bottom level. However, reporting services doesn't seem to recognize the parent hierarchy as being a true hierarchy. After I add the parent-child hierarchy to a table or matrix, it will only add the very first level. There doesn't seem to be any way for reporting services to autogenerate the drilldown like in Analysis Services.

I really need this to work on a report. Any ideas would be much appreciated? Thank you.

You need to set the parent property on the grouping, check out this page on Recursive Hierarchies for details http://msdn2.microsoft.com/en-us/library/ms155903.aspx|||That link applies to Report Designer but what about Report Builder? When I generate a SMDL from an Analysis Services cube, the model contains all my dimensions but if I drag them over to the report, it returns all the available members.

Drilldown Problem Using Parent Hierarchy

Does anyone know how to create a parent-child hierarchy that can be expanded (ie drilldown capabilities) in reporting services as well as analysis services?

Currently, I have a dimension with three attributes: parent, child, and key. Everything works fine in Analysis Services. When I click browse for the cube and I add the parent-child hierarchy, Analysis Services autogenerates a drilldown from the top level to the bottom level. However, reporting services doesn't seem to recognize the parent hierarchy as being a true hierarchy. After I add the parent-child hierarchy to a table or matrix, it will only add the very first level. There doesn't seem to be any way for reporting services to autogenerate the drilldown like in Analysis Services.

I really need this to work on a report. Any ideas would be much appreciated? Thank you.

You need to set the parent property on the grouping, check out this page on Recursive Hierarchies for details http://msdn2.microsoft.com/en-us/library/ms155903.aspx|||That link applies to Report Designer but what about Report Builder? When I generate a SMDL from an Analysis Services cube, the model contains all my dimensions but if I drag them over to the report, it returns all the available members.

Drilldown for Parent Attribute

Does anyone know how to create a drilldown matrix or table using a parent attribute in Analysis Services?

Currently, I have a dimension with this parent attribute which contains up to 10 levels. Then, if you click "Browse" in the Analysis Services toolbar it will let you expand each node until you reach the end of the branch. This allows a variable length tree. However, the problem is that when I go to add this attribute to my query in reporting services it only adds the first level. In other words, when you expand the attribute in the query designer, you can see all ten levels exist but only the first level is added to the report. I've also tried to manually add each level into the query, but this also fails. It ignores whatever level I add and just keeps the attribute's first level.

I know this is kind of a confusing problem, but I'd really appreciate any advice.

Thanks!!
Joel

Does anyone have any ideas or need more clarification?

Thanks

|||

Still stuck... Just wanted to get a few more looks at this problem.

Thanks

drill through timeout

In Analysis Manager I have received the message:
Unable to drill through
The operation requested failed due to timeout
This happens when the query takes 30 secons or more to return the result set.
How can I resolve this issue ?
(Analysis Services 8.0 SP2)SP3 for AS fixes quite a few problems, I would reccomend applying sp3 before you try anything else.

HTH|||Originally posted by rhigdon
SP3 for AS fixes quite a few problems, I would reccomend applying sp3 before you try anything else.

HTH
Hi, thans a lot !!! I have tried applying SP3a but the timeout problem were not corrected.
Any other suggestions ?

Drill through in migrated cubes ?

We have some migrated cubes that implemented drillthrough in Analysis Services 2000 and appear to have drill through in the migrated version if viewed through Proclarity. But when I look at the cube in the 2005 BI studio, I see no structure to implement this drill through. Nor am I able to drill through from the Management Suite--just Proclarity.

What's going on ?

Dan,

Drillthrough has gone through significant changes in AS2005. The following is a link to a good whitepaper on the topic:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_anservdrill.asp

What you are seeing in ProClarity is "backward compatibility" support that was built into our 6.1 release. If you build a "Drillthrough Action" in AS 2005 it will show up in the cube browser used by the BI Studio.

HTH,

- Steve

|||

Steve:

I appreciate the help, but am a little confused.

First, my group has implemented drill through in 2005 using the concept of a degernate dimension on new (i.e., NOT migrated) cubes.

Our confusion occurs becauses migrated cubes exhibit drill through when viewed in Proclarity, but do not have a degernate dimension when examined in the BI 2005 Development Suite.

So, where is Proclarity getting the drill through data from ?

The answer apparently must be that SQL Server Analysis Services 2005 implements an "alternate drill through capability" that cannot be built using the 2005 BI Suite at all, but can only be created by first building a cube in 2000, and then migrating it.

Is this true ?

So, why is this all relevant to us ? Because we wish to add a field to the drill thru in the migrated cube.

|||

Dan,

If you can email me a phone number at spontello@.proclarity.com we can discuss the specifics.

- Steve

|||

Steve,

Is there a way to turn off this "Drill to detail" feature at the PAS server level?

Or to map the "Drill to detail" action to a Drillthrough action that was specifically created by the cube designer?

Thanks,

JGP

|||

JGP,

This could be done by customizing the PAS code. If you want more details send me an email at spontello@.proclarity.com.

- Steve

Drill down slow

Hello everyone

For starters i'm using Microsoft analysis services, integration services and reporting services.

All goes well when creating some general overview reports.

Once i get to making drill down reports with only 2000 rows of data is already starts to work very slow (sometimes takes hours). It seems a data cube is only fast for calculations like SUM, PARALLELPERIOD, ... but if it comes to amount of data it is extreemly weak! 2000 rows is not even alot, i've seen reporting tools beeing able to handle a million rows with drill down function.

I'm thinking that this microsoft tool is just not able to handle reports bigger than 100 rows in it combined with drill down. Please proof me wrong because we already paid for the microsoft tools and it would be a shame if we were not able to use them because of bad functionality...

The machine we work on is quite strong (dual core 2.4 ghz, 4 gig ram, ...)

And yes i know you can make reports clickable so they take the value with them to a next report as a parameters but thats not what we are looking for, we need this drill down to work properly with larger amounts of data.

Hmm, I don't think it's the tool in this case - I have drill down reports with far more data than that and I've never run into 'hours' of execution time. Do you have more detail?

Drill down slow

Hello everyone

For starters i'm using Microsoft analysis services, integration services and reporting services.

All goes well when creating some general overview reports.

Once i get to making drill down reports with only 2000 rows of data is already starts to work very slow (sometimes takes hours). It seems a data cube is only fast for calculations like SUM, PARALLELPERIOD, ... but if it comes to amount of data it is extreemly weak! 2000 rows is not even alot, i've seen reporting tools beeing able to handle a million rows with drill down function.

I'm thinking that this microsoft tool is just not able to handle reports bigger than 100 rows in it combined with drill down. Please proof me wrong because we already paid for the microsoft tools and it would be a shame if we were not able to use them because of bad functionality...

The machine we work on is quite strong (dual core 2.4 ghz, 4 gig ram, ...)

And yes i know you can make reports clickable so they take the value with them to a next report as a parameters but thats not what we are looking for, we need this drill down to work properly with larger amounts of data.

Hmm, I don't think it's the tool in this case - I have drill down reports with far more data than that and I've never run into 'hours' of execution time. Do you have more detail?