Drillthrough to AS2005 cube in excel

Hi,
I am trying to get the 'Drillthrough' feature working in excel 2003 (I
have an action set against my 2005 cube for drillthrough).
I can drillthrough fine in the cube browser but cannot get it to work
in excel.
If I double-click the cell I get an error and have read that I may need
to use the excel addin to do this. However, when I have installed the
addin and the 'required components' I can setup a connection to the
cube but when I try to connect get an error saying I do not have
permissions or my cube does not exist - both of which are false.
Anyone come across this and have a solution?
Thanks,
DarrenHi Darren,
Just write an DRILLTHROUGH MDX Query in Managamnet studio and see your
getting the data.
Let me know if your still having any issue.
Balaji
darrenmr2@.gmail.com wrote:
>Hi,
>I am trying to get the 'Drillthrough' feature working in excel 2003 (I
>have an action set against my 2005 cube for drillthrough).
>I can drillthrough fine in the cube browser but cannot get it to work
>in excel.
>If I double-click the cell I get an error and have read that I may need
>to use the excel addin to do this. However, when I have installed the
>addin and the 'required components' I can setup a connection to the
>cube but when I try to connect get an error saying I do not have
>permissions or my cube does not exist - both of which are false.
>Anyone come across this and have a solution?
>Thanks,
>Darren
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200602/1

drillthrough security error whereas the cube has no security

I have defined several drillthrouh actions on a big cube - each action asigned to a measuregroup. While I want to run one of this actions I receive the error below (BI Dev Studio)

The message states a security restriction, but the database does not have any security roles defined - i work as local admin on the machine with no specific security defined.

The Server is local installed - 9.0.3042.0

This drillthrouh runs on a distinct count measure and returns several attributes from one dimension and the measure itself. I have dropped and recreated several times but it does not change. The profiler shows Error number -1056308962 in the Error trace column.

Additionally I have defined several other drillthrough actions on distinct count measures with the same dimension attribute information - and there the drillthrough works.

ANY Ideas?

Best Regards, HANNES

===================================

The selected action cannot be completed because of the following error.
The drillthrough operation cannot be performed because cell security does not allow access to the source data.
This error may have occurred because the definition for the action is not valid. Verify the definition using the Actions view. (Microsoft Visual Studio)

Program Location:

at Microsoft.AnalysisServices.Browse.ActionsAwarePivotTable.OnAction(MenuCommand menuCommandSender, CommandHandlingArgs args)
at Microsoft.DataWarehouse.Design.CommandHandlingInfoMap.HandleCommand(MenuCommand menuCommand)
at Microsoft.AnalysisServices.Controls.PivotTableControl.Microsoft.DataWarehouse.Interfaces.ICommandTarget.InvokeCommand(MenuCommand menuCommand)
at Microsoft.AnalysisServices.Browse.CubeBrowser.BrowserStateSwitchManager.ConnectedAndActiveStateCommandTarget.InvokeCommand(MenuCommand menuCommand)
at Microsoft.AnalysisServices.Browse.CubeBrowser.Microsoft.DataWarehouse.Interfaces.ICommandTarget.InvokeCommand(MenuCommand menuCommand)

===================================

The drillthrough operation cannot be performed because cell security does not allow access to the source data. (Microsoft SQL Server 2005 Analysis Services)

Program Location:

at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.IExecuteProvider.ExecuteTabular(CommandBehavior behavior, ICommandContentProvider contentProvider, AdomdPropertyCollection commandProperties, IDataParameterCollection parameters)
at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.AnalysisServices.Browse.Actions.DataReaderCompatibleAction.Invoke(IServiceProvider iserviceProvider)
at Microsoft.AnalysisServices.Browse.ActionsAwarePivotTable.OnAction(MenuCommand menuCommandSender, CommandHandlingArgs args)

The error message is not ideal, but I have hard time thinking what else could cause drill through not work. Wild guess - if you change IgnoreUnrelatedDimensions to false for the measure group in question - do you still get the error ?|||

you are a genius. This solved the problem.

Could you explain the background of this - why I need to set this property for this measruegroup and not for others?

I am not 100% sure about the effects this have on some of my calculations - a have lots of calculations like ValueX/DistinctCount and then STDDEV({Bigset},(ValueX/DistinctCount)) - if the distinct count is related to all dimensions then this should have no effect - I am right? Whereas ValueX is not related to all dimensions

Oh no - the distinct count is not the lowest granularity - for the time its on day level - but there are some measures which are on hour level (the distinct count is not related to the key attribute in the time dimension) - do calculations with valueonhourlevel/distinctcount getting wrong?

(My cube has 500 calculated measures)

May Thanks, HANNES

|||Hannes - you should not have switched to IgnoreUnrelatedDimension=false. Drillthrough is supposed to work fine when it is true. I only asked you to check the value of false, because that was the only thing I could think of. So, I am convinced that this is a bug - and you should contact Product Support about it. You can reference this thread.

Drillthrough in Excel 2003

Hi,
I set up drillthrough action in my cube in MS SQL 2000 AS and it's working
fine when I explore the cube with the cube browser in AS, but when I try to
invoke the drillthrough action in MS Excel 2003 I get an message the there
are no details to be displayed - it failes.
Does anyone know why? Maybe Excel doesn't support drillthroughs?
Thanks,
Marcin
Marcin Pksa wrote:
> Hi,
> I set up drillthrough action in my cube in MS SQL 2000 AS and it's working
> fine when I explore the cube with the cube browser in AS, but when I try to
> invoke the drillthrough action in MS Excel 2003 I get an message the there
> are no details to be displayed - it failes.
> Does anyone know why? Maybe Excel doesn't support drillthroughs?
> Thanks,
> Marcin
>
Right, not supported out-of-the-box. But there's some sample VBA code
you can get from the microsoft web site somewhere that shows how to
implement drill through as a right-click context menu.

Drillthrough in Excel 2003

Hi,
I set up drillthrough action in my cube in MS SQL 2000 AS and it's working
fine when I explore the cube with the cube browser in AS, but when I try to
invoke the drillthrough action in MS Excel 2003 I get an message the there
are no details to be displayed - it failes.
Does anyone know why? Maybe Excel doesn't support drillthroughs?
Thanks,
MarcinMarcin Pksa wrote:
> Hi,
> I set up drillthrough action in my cube in MS SQL 2000 AS and it's working
> fine when I explore the cube with the cube browser in AS, but when I try t
o
> invoke the drillthrough action in MS Excel 2003 I get an message the there
> are no details to be displayed - it failes.
> Does anyone know why? Maybe Excel doesn't support drillthroughs?
> Thanks,
> Marcin
>
Right, not supported out-of-the-box. But there's some sample VBA code
you can get from the microsoft web site somewhere that shows how to
implement drill through as a right-click context menu.

Drillthrough action not working

Hello,

I have an AS2005 cube in which I created a drillthrough action. For some reason, it won't return any rows. Even the cube browser within BIDS won't show any results. I have the same setup for another project where it works fine. Can this be security related? I have a single role which has all possible permissions.

Thnx. Jeroen

I'm assuming you have a SQL Server database backend to this. Have you run Profiler against that database to see what query is being submitted or whether errors are being raised on the backend that for some reason are not bubbling up to you?

B.

|||

Hello Bryan,

I captured the drillthrough query with Profiler like you suggested, and ran the following MDX in Management Studio:

Code Snippet

DRILLTHROUGH

SELECT

( [Measures].[Number of Visits]

, [Visit Status].[Visit Status].&[Done]

) ON 0

FROM

[Visits]

RETURN

[$Visit Status].[Visit Status]

, [$Account].[Account Code]

Now, the SELECT clause contains a lot more slicers, but for some reason, when I leave out all parts which select a specific value using the "&" sign, the query returns a result. So in the query here, when I leave out

, [Visit Status].[Visit Status].&[Done]

data is returned. When I leave out only ".&Done" then a result is returned as well, with in most cases the Visit Status being "Done".

Any clue?

Thnx, Jeroen

<some time passed, did some further research>

It seems to be related with the "IgnoreUnrelatedDimensions" property of the MeasureGroup. When this property is set to

TRUE - won't give any results

FALSE - works OK

However, in some cases I need it to be TRUE... Anyone knows a good bit of explanation of this property and of using MeasureGroups and Dimension Usage in general?

Thnx, Jeroen

|||

I'm not 100% what all the IgnoreUnrelatedDimensions property does, but the one thing we use if for is to ensure that when a dimension that does not have a relationship to a measure group from which a measure is derived is used with that measure in a query, no values are returned. Wow! That was a long sentence! Let me try to explain this another way....

In a cube, I may have multiple measure groups, each containing one or more measures. Each measure group has relationships defined with the dimensions and some of these dimensions will have relationships with multiple measure groups. When I browse the cube in a traditional browser, e.g. SSMS or Proclarity, I don't see those relationships. So, it is possible for me to grab a dimension, put it on an axis (so to speak), and then grab an unrelated measure. Because the two are unrelated, there is no data at the intersection of these two sets. So, SSAS has to decide what to show you.

The internal logic of how SSAS thinks about cubes should return the top level aggregated value for that measure. In other words, the measure as it exists if you selected no dimensions to dice it by. (There are variations on this, but we're keeping the concept simple. Just imagine a single dimension on rows and the measure on columns.) If you've ever seen a repeated value all the way down a column in your cell-set, you're probably looking at this.

This isn;t the most intuitive way to display the data to a user. So, the IgnoreUnrelatedDimensions property can be set to True to adjust the behavior. With this property set to True, the values of the unrelated measure are just hidden.

So, if this property is affecting whether or not you get results, do you have a relationship established between the Visit Status dimension and the measure group holding Number of Visits? Also, do you have a relationship established between the Account dimension and that same measure group?

B.

|||

Hi Bryan, thanks for your explanation -verbose!

I had noticed a bit of this behaviour as well. I have a few cubes and some of them have multiple measuregroups, like Order / OrderItems. I think hiding the measures is the most decent way to do it as well. So I guess that only leaves the question why the drillthrough option doesn't work when the option is set to TRUE...

Jeroen

|||

So, if this property is affecting whether or not you get results, do you have a relationship established between the Visit Status dimension and the measure group holding Number of Visits? Also, do you have a relationship established between the Account dimension and that same measure group?

|||

Hi, both relationships are present, since this is a cube with just a single MeasureGroup.

Jeroen

|||

Sorry, but I'm kinda out of ideas with this one. If there is a suspicion this might be security related, you may want to get rid of that role to see if this changes anything.

B.

Drillthrough action not working

Hello,

I have an AS2005 cube in which I created a drillthrough action. For some reason, it won't return any rows. Even the cube browser within BIDS won't show any results. I have the same setup for another project where it works fine. Can this be security related? I have a single role which has all possible permissions.

Thnx. Jeroen

I'm assuming you have a SQL Server database backend to this. Have you run Profiler against that database to see what query is being submitted or whether errors are being raised on the backend that for some reason are not bubbling up to you?

B.

|||

Hello Bryan,

I captured the drillthrough query with Profiler like you suggested, and ran the following MDX in Management Studio:

Code Snippet

DRILLTHROUGH

SELECT

( [Measures].[Number of Visits]

, [Visit Status].[Visit Status].&[Done]

) ON 0

FROM

[Visits]

RETURN

[$Visit Status].[Visit Status]

, [$Account].[Account Code]

Now, the SELECT clause contains a lot more slicers, but for some reason, when I leave out all parts which select a specific value using the "&" sign, the query returns a result. So in the query here, when I leave out

, [Visit Status].[Visit Status].&[Done]

data is returned. When I leave out only ".&Done" then a result is returned as well, with in most cases the Visit Status being "Done".

Any clue?

Thnx, Jeroen

<some time passed, did some further research>

It seems to be related with the "IgnoreUnrelatedDimensions" property of the MeasureGroup. When this property is set to

TRUE - won't give any results

FALSE - works OK

However, in some cases I need it to be TRUE... Anyone knows a good bit of explanation of this property and of using MeasureGroups and Dimension Usage in general?

Thnx, Jeroen

|||

I'm not 100% what all the IgnoreUnrelatedDimensions property does, but the one thing we use if for is to ensure that when a dimension that does not have a relationship to a measure group from which a measure is derived is used with that measure in a query, no values are returned. Wow! That was a long sentence! Let me try to explain this another way....

In a cube, I may have multiple measure groups, each containing one or more measures. Each measure group has relationships defined with the dimensions and some of these dimensions will have relationships with multiple measure groups. When I browse the cube in a traditional browser, e.g. SSMS or Proclarity, I don't see those relationships. So, it is possible for me to grab a dimension, put it on an axis (so to speak), and then grab an unrelated measure. Because the two are unrelated, there is no data at the intersection of these two sets. So, SSAS has to decide what to show you.

The internal logic of how SSAS thinks about cubes should return the top level aggregated value for that measure. In other words, the measure as it exists if you selected no dimensions to dice it by. (There are variations on this, but we're keeping the concept simple. Just imagine a single dimension on rows and the measure on columns.) If you've ever seen a repeated value all the way down a column in your cell-set, you're probably looking at this.

This isn;t the most intuitive way to display the data to a user. So, the IgnoreUnrelatedDimensions property can be set to True to adjust the behavior. With this property set to True, the values of the unrelated measure are just hidden.

So, if this property is affecting whether or not you get results, do you have a relationship established between the Visit Status dimension and the measure group holding Number of Visits? Also, do you have a relationship established between the Account dimension and that same measure group?

B.

|||

Hi Bryan, thanks for your explanation -verbose!

I had noticed a bit of this behaviour as well. I have a few cubes and some of them have multiple measuregroups, like Order / OrderItems. I think hiding the measures is the most decent way to do it as well. So I guess that only leaves the question why the drillthrough option doesn't work when the option is set to TRUE...

Jeroen

|||

So, if this property is affecting whether or not you get results, do you have a relationship established between the Visit Status dimension and the measure group holding Number of Visits? Also, do you have a relationship established between the Account dimension and that same measure group?

|||

Hi, both relationships are present, since this is a cube with just a single MeasureGroup.

Jeroen

|||

Sorry, but I'm kinda out of ideas with this one. If there is a suspicion this might be security related, you may want to get rid of that role to see if this changes anything.

B.

Drillthrough action not working

Hello,

I have an AS2005 cube in which I created a drillthrough action. For some reason, it won't return any rows. Even the cube browser within BIDS won't show any results. I have the same setup for another project where it works fine. Can this be security related? I have a single role which has all possible permissions.

Thnx. Jeroen

I'm assuming you have a SQL Server database backend to this. Have you run Profiler against that database to see what query is being submitted or whether errors are being raised on the backend that for some reason are not bubbling up to you?

B.

|||

Hello Bryan,

I captured the drillthrough query with Profiler like you suggested, and ran the following MDX in Management Studio:

Code Snippet

DRILLTHROUGH

SELECT

( [Measures].[Number of Visits]

, [Visit Status].[Visit Status].&[Done]

) ON 0

FROM

[Visits]

RETURN

[$Visit Status].[Visit Status]

, [$Account].[Account Code]

Now, the SELECT clause contains a lot more slicers, but for some reason, when I leave out all parts which select a specific value using the "&" sign, the query returns a result. So in the query here, when I leave out

, [Visit Status].[Visit Status].&[Done]

data is returned. When I leave out only ".&Done" then a result is returned as well, with in most cases the Visit Status being "Done".

Any clue?

Thnx, Jeroen

<some time passed, did some further research>

It seems to be related with the "IgnoreUnrelatedDimensions" property of the MeasureGroup. When this property is set to

TRUE - won't give any results

FALSE - works OK

However, in some cases I need it to be TRUE... Anyone knows a good bit of explanation of this property and of using MeasureGroups and Dimension Usage in general?

Thnx, Jeroen

|||

I'm not 100% what all the IgnoreUnrelatedDimensions property does, but the one thing we use if for is to ensure that when a dimension that does not have a relationship to a measure group from which a measure is derived is used with that measure in a query, no values are returned. Wow! That was a long sentence! Let me try to explain this another way....

In a cube, I may have multiple measure groups, each containing one or more measures. Each measure group has relationships defined with the dimensions and some of these dimensions will have relationships with multiple measure groups. When I browse the cube in a traditional browser, e.g. SSMS or Proclarity, I don't see those relationships. So, it is possible for me to grab a dimension, put it on an axis (so to speak), and then grab an unrelated measure. Because the two are unrelated, there is no data at the intersection of these two sets. So, SSAS has to decide what to show you.

The internal logic of how SSAS thinks about cubes should return the top level aggregated value for that measure. In other words, the measure as it exists if you selected no dimensions to dice it by. (There are variations on this, but we're keeping the concept simple. Just imagine a single dimension on rows and the measure on columns.) If you've ever seen a repeated value all the way down a column in your cell-set, you're probably looking at this.

This isn;t the most intuitive way to display the data to a user. So, the IgnoreUnrelatedDimensions property can be set to True to adjust the behavior. With this property set to True, the values of the unrelated measure are just hidden.

So, if this property is affecting whether or not you get results, do you have a relationship established between the Visit Status dimension and the measure group holding Number of Visits? Also, do you have a relationship established between the Account dimension and that same measure group?

B.

|||

Hi Bryan, thanks for your explanation -verbose!

I had noticed a bit of this behaviour as well. I have a few cubes and some of them have multiple measuregroups, like Order / OrderItems. I think hiding the measures is the most decent way to do it as well. So I guess that only leaves the question why the drillthrough option doesn't work when the option is set to TRUE...

Jeroen

|||

So, if this property is affecting whether or not you get results, do you have a relationship established between the Visit Status dimension and the measure group holding Number of Visits? Also, do you have a relationship established between the Account dimension and that same measure group?

|||

Hi, both relationships are present, since this is a cube with just a single MeasureGroup.

Jeroen

|||

Sorry, but I'm kinda out of ideas with this one. If there is a suspicion this might be security related, you may want to get rid of that role to see if this changes anything.

B.

DrillThrough action is broken and returns no data

I have a simple DrillThrough action which has been working for sometime. However, it has now stopped working and will not return any data... even if I invoke it from the highest level. I have re-deployed and processed the whole database from scratch. There is no conditional statement to this so I cannot see why it would not run.

What could possibly been happening? Has anyone had this problem before? Any suggestions about finding the source of this problem?

Ta

Dirc

Hello Dirc/Dorc,

Have you ever been able to solve this? I seem to be experiencing the same thing.

Regards, Jeroen

DrillThrough action is broken and returns no data

I have a simple DrillThrough action which has been working for sometime. However, it has now stopped working and will not return any data... even if I invoke it from the highest level. I have re-deployed and processed the whole database from scratch. There is no conditional statement to this so I cannot see why it would not run.

What could possibly been happening? Has anyone had this problem before? Any suggestions about finding the source of this problem?

Ta

Dirc

Hello Dirc/Dorc,

Have you ever been able to solve this? I seem to be experiencing the same thing.

Regards, Jeroen

Drillthrough action

Hi,

Is there any way in AS2005, I can specify a cube action which will bring up an HTML page displaying the Drillthrough MDX for the cell that I just initiated the action on?

Also, will this MDX work across all cube partitions (no need for FIRSTROWSET clause)?

Thanks,

JGP

Drillthrough actions return a DRILLTHROUGH statement to client application (e.g. cube browser) which in turn executes the statement and displays the results. The results will contain data for all partitions (no need for FIRSTROWSET clause). See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_anservdrill.asp for more info.

HTML actions are unsafe and BI Dev Studio does not let you create them. If you do want to expose the DRILLTHROUGH statement to the user, you have to plug in some code (depends on the client app) that retrieves the actions schema rowset, extracts the statement and displays it.

|||

Any idea how I would go about retrieving the action schema rowset? Any samples would be greatly helpful.

I'm trying to get this working from the ProClarity clients (Standard and Professional).

Thanks,

JGP

Drillthrough action

Hi,

Is there any way in AS2005, I can specify a cube action which will bring up an HTML page displaying the Drillthrough MDX for the cell that I just initiated the action on?

Also, will this MDX work across all cube partitions (no need for FIRSTROWSET clause)?

Thanks,

JGP

Drillthrough actions return a DRILLTHROUGH statement to client application (e.g. cube browser) which in turn executes the statement and displays the results. The results will contain data for all partitions (no need for FIRSTROWSET clause). See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5_anservdrill.asp for more info.

HTML actions are unsafe and BI Dev Studio does not let you create them. If you do want to expose the DRILLTHROUGH statement to the user, you have to plug in some code (depends on the client app) that retrieves the actions schema rowset, extracts the statement and displays it.

|||

Any idea how I would go about retrieving the action schema rowset? Any samples would be greatly helpful.

I'm trying to get this working from the ProClarity clients (Standard and Professional).

Thanks,

JGP

drilldown values

When you use the action property of a text box for drill down, how do you
pass the selected value to the report? or is sql reporting smart enough to
know?
--
Thanks,
CGWNever mind... found the button for parameters.
"CGW" wrote:
> When you use the action property of a text box for drill down, how do you
> pass the selected value to the report? or is sql reporting smart enough to
> know?
> --
> Thanks,
> CGW

drilldown newbie

Please excuse me if this double posts. Thought I posted earlier but do not
see it.)
When I use the action property of a text box to bring up a chart, how do i
tell it to chart only that row's values. For example, I want to chart manager
sales over months. I list the managers and their sales as rows and have set
the manager's name text box to jump to my chart. What displays are the sums
for all managers. (all data rows). What do I need to do?
--
Thanks,
CGWNever mind... found the button for parameters.
"CGW" wrote:
> Please excuse me if this double posts. Thought I posted earlier but do not
> see it.)
> When I use the action property of a text box to bring up a chart, how do i
> tell it to chart only that row's values. For example, I want to chart manager
> sales over months. I list the managers and their sales as rows and have set
> the manager's name text box to jump to my chart. What displays are the sums
> for all managers. (all data rows). What do I need to do?
> --
> Thanks,
> CGW