Friday, 4 July 2014

How to get Security filter Mapping details using Metadata Scripts?

How to get Security filter Mapping details using Metadata Scripts?

select OI.Object_name,OI1.Object_name,OI1.project_id from DSSMDOBJINFO OI join 
(select I.Object_id A, I1.Object_id B,I.project_id C from DSSMDLNKITEM I
on I.Linkitem_id=I1.Linkitem_id
where I.Link_id in (select Object_id from DSSMDOBJINFO where object_type=52 and Object_name='MD Security filter Link'
and project_id in (select Object_id from DSSMDOBJINFO where Object_name in ('Project name 1','Project name 2') and object_type=32))
and I.project_id in (select Object_id from DSSMDOBJINFO where Object_name in ('Project name 1','Project name 2') and object_type=32)
And I1.Object_type= 34
And I.Object_type=58) LI 
on OI.Object_id=LI.B
on OI1.Object_id=LI.A
and OI1.Project_id=LI.C

How to get Connection Mapping details using metadata Scripts?

How to get Connection Mapping details using metadata Scripts?

select I.Object_name,I1.Object_name,I2.Object_name,I3.Object_name,I4.Object_name,
inner Join
WHERE a1.LINK_ID='Connection Mapping Link ID (Object ID)' AND 
a1.PROJECT_ID='0000000000000000000000000000000000000000' AND 
on I.Object_id=ID.A
inner join DSSMDOBJINFO I1
on I1.Object_id=ID.B
inner join DSSMDOBJINFO I2
on I2.Object_id=ID.C
inner join DSSMDOBJINFO I3
on I3.Object_id=ID.D
inner join DSSMDOBJINFO I4
on I4.Object_id=ID.E

Where it will be useful?

  • Connection Mapping is very critical element in a multi-tenant architecture
  • Using the above Script, we can create a monitor reports 

Tuesday, 29 January 2013

Bring All Subscription Along with Project Duplication

Subscription Setting in Project Duplication:

We have Deliver subscription preferences in duplication wizard to handle all subscriptions while doing duplication.

1) Include user delivery subscription
2) Include contacts and contact groups
3) Include contact delivery subscriptions


Before project duplication, we need to make sure all subscriptions are available in source intelligence server.
In case if we disabled, "Use Microstrategy scheduler" option in source I server then subscriptions may not available in source I server. In this case you can't find subscriptions option in delivery manager tab. In this case project duplicate will not bring any subscriptions to target project 

How do we handle this?

1) Enable "Use Microstrategy Scheduler" Option
2) Restart I server
3) Duplicate the project with respective subscription preferences

This will enable and bring all subscriptions to target project

Sunday, 27 January 2013

Microstrategy Issues and Enhancement options - My View


a.  Translation Wizard  - We are getting overflow error in translation wizard

b.  In MSTR, We don't have option to Map metadata language in group level  - if we have that option then it will be one time work

c.  Report name with translation

Whenever user mapped with some language creates any report then the report name applies to its translation automatically as well. In this case, when you login as administrator which points to default language then all report appears with the name of "Blank report"
Why report names applies automatically to its translation. Is there any way to avoid this?

d.   Language Mapping to user - In project Level

                           i.      We don't have option to modify this after our first configuration

e.   Issue with translation  

              For some reason, all translations got disappeared when we do translation for first time.  You can see all missing translation in one day time period

2.       Project Merge

a.  Folder Level conflict resolution in project Merge - Currently it is having option to apply conflict resolution Non Recursively. It would be great if we get option to do this recursively

b.  It would be great if we have some option to avoid few set of or few types of objects in project Merge
                                               i.      Ex: I don't want to bring any reports from source to target

c.  Currently project Merge is not supporting few configuration objects like "language", "Groups", "Data Connection" etc.  - It leads to manual effort in deployments process

3.       Command manager scripts

a.       No scripts available for few bulk changes like
                                i.      Changing subtotal function
                                ii.      Changing dynamic aggregation
                                iii.      Changing Advance option in condition and in level
                                iv.      Changing Formula join type  (Option is available to change only metric join type)

b.      No Command manager scripts to add language to the user

c.      No ACL Scripts for shortcuts

d.      Using Object IDs in command manager

e.      Import package using command manager - it supports only when package present in the same machine where intelligence server present

4.       Formula Join type behaviour

a. Ex: Consider a metric Definition Metric A =  Sum(sum(a) {~} * Metric B) {~}

                       i.    In this case, Metric join type for all the metrics are Outer and formula join type for all the metrics (Metric A & B) is inner

                       ii.    Here Formula join type is having NO Impact

5.       Issues Reported in analytical engine  

When we use both outer join and a VLDB setting "Consider both ID and Desc form in the intermediate Pass", Attribute descriptions are become blank in the report.
When we add Attribute ID then ID is visible

 Workaround given is - Remove above mentioned VLDB setting when you use full outer join

6.       Dimension table joins

a.     As default behaviour all dimension joins takes place in last pass. It would be great if we have any VLDB settings to change this and have dimension joins in the respective intermediate pass. It will be helpful for the design using full outer join (full outer join is generating null values and dimension joins are truncating those records)

b.     We do have a VLDB setting  (Consider both ID and Desc form in the intermediate Pass), but this is not working completely for all scenarios

7.       Issues in searches on shortcuts

a.       Issue 1

·   We have a collection of shortcuts in a folder (with \without subfolders)

·   Created a search (object type = Shortcut) to list all these shortcuts

·   Used the search inside the object prompt

·   When we use this prompt in the web, it is correctly bringing first 30 
     objects and then when we give "Next", it is not bringing any values       (time out in the background)

·   Same is working fine when we display all objects in a single shot instead of limited object window (display 30 at a time)

b.      Issue 2 

·   We have a collection a attribute shortcuts in a folder (with subfolders)

·   Created a search (object type = Attribute & option - Resolve shortcuts to target object ) to list all these shortcuts

·   Used the search inside the object prompt

·   When we use this prompt in the web, it is not displaying the object count accurately with respect to access

8.       Behaviour of "Move" Option related to access

a.    When we move object from one folder to another folder, object is not getting inherited with target folder ACL

                  i.      Ex: When user moves report from profile folder to shared reports folder and after that user expects that report should be visible to everyone

9.    Report Filter changes are not applying to when we use Report filter + custom expression filter with prompt 

1.   We have a report with one Report Filter Carrier = ‘A’ and one object prompt used in a custom expression.

2.  Ran the report and saved it with option “Keep Report Prompted”

3.  Then we Edited the report and changed the report filter to Carrier = ‘B’ and saved

4.   When we Run the report, it is still applying carrier = ‘A’ in the filter

10.   Support Column with special character and space

a.    By default MSTR applies square bracket for space

b.    But it is not applying square bracket for special character

c.    If we apply square bracket through VLDB setting then it is applying two square bracket in terms of column name with space               

11.   Update Schema Issue

a.  Sometimes, update is not impacting all objects in the project, Due to that, we are getting cross join issue or Fact doesn't exists error

b.  When we give update schema for two, three times then it gets resolved

12.   Object Manager

Object dependencies

For our cross subject area model, we have used hierarchies in metric level. Hierarchies consist of all attributes of a particular subject area.
With this model, when we try to move one or two metrics from one project to another project, objects manager is bringing all attributes in the dependencies because of the hierarchy (which is very costly and taking huge time)

Is there any option to avoid this when I know target hierarchy is integrated properly?

13.   Fact Extension

In Fact Extension, "Lower Fact Entry Level", we don't have any option to select relation table as like in "Extend Fact entry level". It asking only join attribute.

Consider the below scenario,

Using "Lower Fact Entry Level", we have extended the fact from one fact table to another fact table using join attribute. In this case, when we add attribute (lookup: dimension table, Source fact table) in the SQL, first fact table is trying to join with the dimension table of the attribute instead of fact table because dimension table is configured as lookup table.

In this case, we have to change the lookup table to fact table to achieve this but it won't support cross subject area.

In Extension, we have given all reference between two fact tables, so it leads to cross join

But in "Extend Fact entry level", we have option to select relationship table and in that we are selecting "fact table", so it is joining correctly

14.   Table arrangements & easy selection option

 In attribute creation or in fact creation, selection of source table is very hard with the huge list of tables with same prefix. It would be great if we have some option to search and select the table

Multi-Tenancy Project Configurations - Useful for Cloud environments

  1. Connection Mapping
  2. User Level Security filters
  3. Cache Setting - Connection Mapping level
  4. Translations -Metadata (Repository Translation Wizard)
  5. User Level Language Configuration
  6. Shared folder Configuration
  7. Customer filters

Useful Verification Scripts for Multi tenancy Project Architecture

Verify connection mapping:


From Connection mapping output, get all groups information and give that as input to step 2 - This will give security filter information for all group


From Connection mapping output, get all Connection information and give that as input to step 3 - This will give Connection and its DSN Information


Run Below Script on any one object which is available for all customers - To Make Sure ACL is applied for all customers and using correct group


Run below Script on any one object which is translated for all customers - to Make Sure Translation is applied for all customers and using correct Language

5) select LOCALE,translation from dbo.DSSMDOBJTRNS where OBJECT_ID =('Object id') and PROJECT_ID='Project id'

Friday, 27 April 2012

MSTR Query Performance

We have different options to make MSTR to write a better query and again it is depends on your business logic.

Here I am giving few useful and handy options which improves MSTR query and its performance.

Few VLDB settings which are helpful to write better query with respect to different scenarios:

  1. Select/Insert --> Attribute Form Selection --> change to select ID and other forms -this might prevent some of the lookup table joins if all information are already in the temp tables. Might not work if you have outer join settings in final pass 
  2. Select/Insert --> Attribute Selection Option --> change to select other attributes in join tree - this might again diminish the amount of joins in the final pass
  3. Query Optimization --> Additional Final Pass --> change to have one additional pass for lkp tables only - this might split your final join and make it easier for SQL server
  4. Lookup Table Join Order --> change to "Fully ..." - this might change the order to something more suitable
Another factor which is really important in terms of performance is query execution plan. Some cases, MSTR query would be good but still query performance is very bad due to query execution plan.

In this case, you have analyze the execution plan carefully and have to tweak using below options

1) Adjust any of the above VLDB settings as per the analysis
2) If necessary, use some SQL hint and we have VLDB options to implement that.

In one scenario, MSTR SQL is perfectly alright and still your last pass query is taking huge time.

We have done a different analysis like, 

  1. Change SQL join type to SQL 89 - This gives very good performance
  2. Changed Join order in the final pass, again it works

Then we took our SQL execution plan and analyzed then we came to know, with some cost or stats, execution plan is taking wrong join order and it leads to number of cross joins in the background.

Then we used a SQL hint to force the join order - Option (force order) and query ran in seconds.