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 Security Conundrum AS 2005

Hi,

I'm looking for some inspiration on how to solve the following issue.

I'm working on a cube that contains crime data. I have a number of reports built using SSRS that show summary totals for different types of crime. In addition, there are a number of linked reports where the user can drill through to the underlying details of the crimes in the fact table fact table. These reports use the DRILLTHROUGH statement via the OLE DB provider for OLAP. So far, so good.

The client now wants to restrict drillthrough on certain types of crime.

Essentially, if a user is a member of Role1, they should be able to see the summary total for Crime A in the main report but they should NOT be able to drillthrough to the details of that summary

If the user is a member of Role2, they should be able to drill through to the detail level.

So far, I cannot see a way to do this with role permissions. My understanding and experience of the role based security thus far is as follows (please correct me if I'm wrong)

1) You can enable or disable drill through at the cube level for a particular role via the Cubes > Drillthrough Access property. This is too restrictive for my needs in this case

2) You can hide certain dimension members via Dimension Data tab but this results in the particular crime types not being displayed in the main report (i.e. you can't see the summary total for that crime)

3) You can restrict access to certain cell values via Cell Data but again this results in a value like N/A being displayed in the main report.

Has anyone faced a similar problem and come up with a way to do this?

Any feedback appreciated

NJDUNNE

The right way to approach this is through dimension security (Dimension Data tab). Drillthrough respects all the dimension security settings. You can restrict access to details, but make sure that Visual Totals=false, therefore you will see the summaries but won't be able to go to details.|||

Hi ,

Do u know if we can create drill through reports in SSRS through SSAS & Sql Server 2005 as the Database . I want to know how this can be done ?

Regards

Rashmi

Drillthrough Security Conundrum AS 2005

Hi,

I'm looking for some inspiration on how to solve the following issue.

I'm working on a cube that contains crime data. I have a number of reports built using SSRS that show summary totals for different types of crime. In addition, there are a number of linked reports where the user can drill through to the underlying details of the crimes in the fact table fact table. These reports use the DRILLTHROUGH statement via the OLE DB provider for OLAP. So far, so good.

The client now wants to restrict drillthrough on certain types of crime.

Essentially, if a user is a member of Role1, they should be able to see the summary total for Crime A in the main report but they should NOT be able to drillthrough to the details of that summary

If the user is a member of Role2, they should be able to drill through to the detail level.

So far, I cannot see a way to do this with role permissions. My understanding and experience of the role based security thus far is as follows (please correct me if I'm wrong)

1) You can enable or disable drill through at the cube level for a particular role via the Cubes > Drillthrough Access property. This is too restrictive for my needs in this case

2) You can hide certain dimension members via Dimension Data tab but this results in the particular crime types not being displayed in the main report (i.e. you can't see the summary total for that crime)

3) You can restrict access to certain cell values via Cell Data but again this results in a value like N/A being displayed in the main report.

Has anyone faced a similar problem and come up with a way to do this?

Any feedback appreciated

NJDUNNE

The right way to approach this is through dimension security (Dimension Data tab). Drillthrough respects all the dimension security settings. You can restrict access to details, but make sure that Visual Totals=false, therefore you will see the summaries but won't be able to go to details.|||

Hi ,

Do u know if we can create drill through reports in SSRS through SSAS & Sql Server 2005 as the Database . I want to know how this can be done ?

Regards

Rashmi

Drillthrough row count is less than [Measures].[Sales Count] from cube

The reason that this is happening is somewhat attributed to the fact that I have 2 different breakouts (2 attributes in the same Customer dimension) of customer territories and security is only set at one of the territory types.

(Security was set at both, but I was having the fore mentioned issue, so I trimmed the restriction down to one territory for only one territory type - hoping that any customers under that one territory type would also show up under their corresponding territory type 2)

Thinking about the mdx/drillthrough results in more detail, I'm leaning toward the mdx results being wrong and the drillthrough being right.

Is there something I can do to fix this or is it a bug?

I wish I could give more detail, but it's tough.Adventure Works didn't seem to have a similar setup because customer.geography isn't related to sales territory.group.

If I run the following sql query on the source data, 3 of the type2 territories are returned:

select Territory2Name, count(*)

from dimCustomer

where Territory1Name = 'East'

group by Territory2Name

One of the Territory2Names is 'Section 2'.Now in here, there's only a count of 1 customer, yet when I look at the cube for Section 2 and [Sales Count], there's easily more than 1 customer composing the total and if I look at a sales dollars, it's the same thing.When I do a drillthrough, then it's correct - results are only for that one customer.

Any thoughts on this perplexing problem?

Does your security role have the Visual Totals option ticked?

If not it will be showing the total for the "All" member of the Territory1 attribute unless you are also explicitly filtering by a Territory1 member.

|||Both of the dimension data security attributes had visual totals checked.

What I ended up doing to solve this problem is only giving access to an area in territory type1 and did not specify any security on territory type 2. This was exceptable for my case - users just needed access to a sampling of the items in both territory types, but in other cases, a person might have to do more digging to get access to a specific area from both geographical types..

Drillthrough is incomplete

I have a strange problem in my cube when drilling down to details.

If I run the following query and leave out the DRILLTHROUGH, I get only one number as result, 7.

The Measure Counter is defined as "COUNT on lines" of the source table. So I expect that the number 7 results from 7 lines in the source table - And when I run an SQL query translated to use the same filter options as WHERE part, I do indeed get 7 lines matching the date and other settings I asked for. Everything ok so far.

When I execute the MDX query with DRILLTHROUGH I get only 3 lines as result - I remove the DRILLTHROUGH again and get 7 as result again... what can be the reason for this?

DRILLTHROUGH SELECT {[Measures].[Counter]} ON 0 FROM [Cube] WHERE ([Final Level].[Level Hierarchy].&[-6], [Action].[Action].&[4], [Submitted Date].[Submitted Date].[Submitted Year].&[2007], [LMU-SL].[LMU-SL Hierarchy].&[3])

Perhaps there are duplicate records at the measure group granularity, therefore in the cube you ended up with only 3 distinct records, but the total value is still correct - 7.|||

Mosha Pasumansky wrote:

Perhaps there are duplicate records at the measure group granularity, therefore in the cube you ended up with only 3 distinct records, but the total value is still correct - 7.

Mosha, thanks for answering.

I understand what you mean, but I can confirm that this is not the reason / solution.

I did confirm that in fact the 7 rows are distinct for the granularity of the measure group. They each have their own primary key in the database, and the measure group is based on this table, on this pk.

I browse the cube in Excel, and there are more combinations like this one where a number of lines is counted in cube ("11" in this other case), 11 distinct lines can be confirmed using sql, but no line at all is returned for drill. If I create my MDX without drillthrough, I get "11" as value for the measure. If I put "drillthrough" before it - I get no results at all.

As always, it is all far more complicated, as you guessed... My original MDX showed 4 dimensions sliced. 3 of them are really dimension on the measure group, these dimensions are joined in as regular to the measure group. The 4th one is a dimension on a measure group with a finer grain, a deeper level of detail as I try to imagine it. This 4th dimension is joined to the finer grain as a regular dimension, and to the measure group queried using m-n joining "back up from finer detail level".

For information, I can display a row count on the detail measure group as well, and these numbers in the cube are also perfectly in line with tests done using sql. But drills to the detail group give no results at all (even if the cube showed some 10 (distinct) detail lines for this dimension, verified using sql.

I suspect the problem is around that m-n from a finer level, since drilling into both measure groups without using this dimensions mix from different grains works perfectly fine. I am quite sure my dimension - measure setup is correct, since all counts in the cube come out the same value as manual sql test comparisons.

I am really a bit worried here with the reliability of SSAS 2005.

I am now cleaning out the db to only host the 7 lines in question as all data available, opposed to 2.5 mill on the queried level and 10 mill lines in the finer grain section to see if that makes any change in the result, and reload the whole SSAS db with this greatly simplified amount of data. I might also use CTP of SP2 to see what happens.

|||

Ralf_from_Europe wrote:

I am now cleaning out the db to only host the 7 lines in question as all data available, opposed to 2.5 mill on the queried level and 10 mill lines in the finer grain section to see if that makes any change in the result, and reload the whole SSAS db with this greatly simplified amount of data.

I think I found out the worst I could imagine: With less data loaded, or better: only data that falls within the area I slice for, I get ALL my 7 lines as result in drill and as cube measure. I did not change any settings in the project or made any changes to the query. I only threw out around 12 mill lines of data from the source db... I really doubt the precision, or maybe better the reliability of SSAS now.

In case anyone wants to know I noticed the difference between the lines given back and not given back: I suspect the problem I have is somewhere along the aggregations build for the cube. Why? The lines that work are in one category of a dimension (a pretty simple one with the order state, 8 items, like in "ordered", "mailed", "received" or "returned" and so). Anyway, the lines that work are in one state, the ones that do not are in another state.

This dimension is NOT used in this example, and also not part of my MDX query, neither with or without the drillthrough part. I suspect there is no aggregation on this dimension since the design wizard did not call an 8 entry dimension worth it. Then again, I did not change the aggregation setup, I only cleaned the source db of other data...

I am SERIOUSLY in doubt now! Trying SP2 CTP now...

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

Hi,
Can anyone explain me how to work with the filter tab on Cube drill through
options?
I added an extra table and specified the joining clause in this tab. It
works fine for the first time; but after saving the cube it gives invalid
alias or alias cannot be located. I tried putting the same alias name for the
table but it doesn’t work.
Thanks in advance.
Regards,
Yash
Run SQL Profiler to see what the exact SQL statement being generated looks
like.
My guess is that you have an edit issue in your filter statement.
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.
"Yash" <Yash@.discussions.microsoft.com> wrote in message
news:F34694EB-D644-4671-AF0D-2C1896CDDDEE@.microsoft.com...
> Hi,
> Can anyone explain me how to work with the filter tab on Cube drill
through
> options?
> I added an extra table and specified the joining clause in this tab. It
> works fine for the first time; but after saving the cube it gives invalid
> alias or alias cannot be located. I tried putting the same alias name for
the
> table but it doesn't work.
> Thanks in advance.
> --
> Regards,
> Yash
>

Drillthrough Filters

Hi,
Can anyone explain me how to work with the filter tab on Cube drill through
options?
I added an extra table and specified the joining clause in this tab. It
works fine for the first time; but after saving the cube it gives invalid
alias or alias cannot be located. I tried putting the same alias name for th
e
table but it doesn’t work.
Thanks in advance.
--
Regards,
YashRun SQL Profiler to see what the exact SQL statement being generated looks
like.
My guess is that you have an edit issue in your filter statement.
--
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.
"Yash" <Yash@.discussions.microsoft.com> wrote in message
news:F34694EB-D644-4671-AF0D-2C1896CDDDEE@.microsoft.com...
> Hi,
> Can anyone explain me how to work with the filter tab on Cube drill
through
> options?
> I added an extra table and specified the joining clause in this tab. It
> works fine for the first time; but after saving the cube it gives invalid
> alias or alias cannot be located. I tried putting the same alias name for
the
> table but it doesn't work.
> Thanks in advance.
> --
> Regards,
> Yash
>

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

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

Drillthrough

Hi,
While enable drillthrough on a cube. There is a option to extra add tables.
How is the relationship mananged for these table; do i need to join it to the
fact table or the dimension tables.
How to remove a table added through this option.
Thanks a lot in advance.
Regards,
Yash
You need to write the join clause in the Filter tab. If you uncheck all
columns used by the additional tables and save they will be gone
Regards,
Plamen
"Yash" <Yash@.discussions.microsoft.com> wrote in message
news:5D9CD437-D326-415E-880F-EF1D590D3B88@.microsoft.com...
> Hi,
> While enable drillthrough on a cube. There is a option to extra add
tables.
> How is the relationship mananged for these table; do i need to join it to
the
> fact table or the dimension tables.
> How to remove a table added through this option.
> Thanks a lot in advance.
> --
> Regards,
> Yash
>

Drillthrough

Hi,
While enable drillthrough on a cube. There is a option to extra add tables.
How is the relationship mananged for these table; do i need to join it to th
e
fact table or the dimension tables.
How to remove a table added through this option.
Thanks a lot in advance.
--
Regards,
YashYou need to write the join clause in the Filter tab. If you uncheck all
columns used by the additional tables and save they will be gone
Regards,
Plamen
"Yash" <Yash@.discussions.microsoft.com> wrote in message
news:5D9CD437-D326-415E-880F-EF1D590D3B88@.microsoft.com...
> Hi,
> While enable drillthrough on a cube. There is a option to extra add
tables.
> How is the relationship mananged for these table; do i need to join it to
the
> fact table or the dimension tables.
> How to remove a table added through this option.
> Thanks a lot in advance.
> --
> Regards,
> Yash
>

Drill through in Olap Cube and implement at Share Point

Hi,

I want to integrate a Olap cube to sharepoint in which we can achieve drill through and multilingual also.

Please help me out to get solution of 2 problems.

Thanks

For the OLAP cube drillthrough from SharePoint, I'm using 3rd-party OLAP Web Parts, which expose cube-defined Reporting Actions. When the user selects such an action, a Reporting Services report is launched, which executes the MDX drillthrough query (using the OLE DB for OLAP Provider) and displays tabular results. Relevant current cube co-ordinates for the drillhrough are passed as URL paramters to the report.|||Deepak,

Which 3rd party tools which expose cube actions have you had the best luck with?

Thanks,
Todd
|||

Hi Todd,

I'm using DSP Portal Edition 4.3 right now, but perhaps ProClarity for SharePoint Portal Server will also work...

Drill through in Olap Cube and implement at Share Point

Hi,

I want to integrate a Olap cube to sharepoint in which we can achieve drill through and multilingual also.

Please help me out to get solution of 2 problems.

Thanks

For the OLAP cube drillthrough from SharePoint, I'm using 3rd-party OLAP Web Parts, which expose cube-defined Reporting Actions. When the user selects such an action, a Reporting Services report is launched, which executes the MDX drillthrough query (using the OLE DB for OLAP Provider) and displays tabular results. Relevant current cube co-ordinates for the drillhrough are passed as URL paramters to the report.|||Deepak,

Which 3rd party tools which expose cube actions have you had the best luck with?

Thanks,
Todd
|||

Hi Todd,

I'm using DSP Portal Edition 4.3 right now, but perhaps ProClarity for SharePoint Portal Server will also work...