Drill Problem

[AnaFlor]

Hello,

I have a problem with the drills.
I have the hierarchy Time wich have the attributes year,quarter, month and day.

I have two fact tables. My fact_table1 have month and my fact_table2 have day.

When I have a report with the fact_table1 and the attribute month and I try to do a drill to day it's giving me an error because it is not possible to do that.

How can I configure the hierarchy to say that when I have fact_table1 I cannot drill to day and when I have fact_table2 I can drill to day?

I want to do this because imagine that the users don't know all the model of the database, so I need to limit the access to drill to certain attributes when associated with certain fact tables.

Thanks.

 

[nlim]

too generic a question. look into drill maps and your fact definitions.

MSTR also has good training classes that can help with this. see

http://www.microstrategy.com/Education/Course_Search/Search.asp

I think in places like China and India they have local distributors that also have training classes.

 

[AnaFlor]

but is it possible?

can you give me an example please?

 

[JaC74]

Does your fact tables 1 and 2 have a common attribute to join on? If so did you set the relationship in the Month attribute to have Day attribute as a child?

"How can I configure the hierarchy to say that when I have fact_table1 I cannot drill to day and when I have fact_table2 I can drill to day?"

You have to do this report by report. If you know of a certain report that hits fact_table1, then disable to drill to Day using the drill map editor.

MicroStrategy does not have a feature to dynamically set a drillmap based on the fact table the report will hit during execution.

 

[AnaFlor]

No, my fact tables have no common attribute.

and yes I have ser a relationship between month and day.

the problem is because my fact tables have different information not always the drills are the same.

I will try to do this report by report.

Thanks

 

[wolf5370]

Can you outline the structure of these agregate and the ref tables. I assume something like this:

Attribute: Month - Table Ref_M
Attribute: Day - Table Ref_D

Ref_M:
A_Month_ID
A_Month_Desc

Ref_D:
A_Day_ID
A_Day_Desc
A_Month_ID
A_Month_Desc (Don't actually need thuis one, but can save join if both descriptions needed).

Fact_Tab1:
A_Month_ID
A_something_else
F_Sales

Fact_Tab2:
A_Day_ID
A_something_else
F_Sales

By making A_Month_ID the parent of A_Day_ID (their associated attributes of course - Month, Day), then the drill should work fine.

If the Drill report is to show more information than the Month report did, you may want to consider drill to template or separate reports all together.

To limit the drill use the Drill Map Editor (as said above), and also as said - it is specific to the template, not an aggregate or fact. Might be able to do something clever with drill to template in this respect - worth you playing with it perhaps.

 

[AnaFlor]

I already try the drill map editor.
But then, when I have documents, the drills dissapear.

Is there any possible reason?

Thanks

 

[JaC74]

Document Objects are not drillable because they are pure HTML when returned to the browser. Even the data set is a picture embedded in to the document object.

 

[JaC74]

It looks like the new versions of the document object actually structures the data set in to HTML table. Not sure which version this started from, but I do recall it used to turn the dataset in to a picture format. Regardless, it is undrillable.

 

[wolf5370]

Agreed. Original post did not mention Documents.

 

[kpescatore]

This probable isn't a solution for what you are looking to do but this may work if you want to drill from one report to a different report off of an html doc.

I have been able to drill from a report to a static Document Object report by adding the html call to an attribute def with an applysimple. This isn't what you are asking but if you use the link info in an xsl it should create the same effect.

The report in itself isn't very helpful unless you can pass a value to the report. There is a Tech Note for report services that shows how to achieve this. I believe that you will be able to apply this same logic in an xsl to simulate the same thing.

The tech note is TN5600-750-1059. From here it will send you to the Web SDK documentation. There is code that you will need to write.

I haven't attempted anything beyond showing that I can drill to a report Doc so I’m not sure how much effort is needed.

Hope this helps