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

Prerequisite:

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


Internationalization

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:

1)  LIST ALL CONNECTION MAP FOR PROJECT "Project Name";

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

2) LIST SECURITY FILTERS GROUP  "group" FOR PROJECT "Project name";

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

3) LIST ALL PROPERTIES FOR DBCONNECTION "Connection";

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

4) LIST ALL PROPERTIES FOR ACL FROM SHORTCUT "Object" IN FOLDER "Path" FOR PROJECT "Project Name";

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.

Example:
------------ 
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.

Wednesday, 11 January 2012

Translation details for particular language


select a.object_id,a.object_name,b.translation from dbo.DSSMDOBJINFO a join
dbo.DSSMDOBJTRNS b
on a.object_id =b.object_id
where b.project_id='Project_id'
and b.Locale =16778249
and a.project_id ='Project_id'


you can use the query to get path from object id to get the path information along with this.

Tuesday, 10 January 2012

Useful Metadata Queries

Object ID in MSTR 9.2:
Object id in MSTR 9.2 metadata tables has been stored as Unique identifier. So we cannot use Metadata object id in MSTR Desktop and vice versa.

But we have a built in functions to convert this.

MSTR Metadata Object ID to Desktop Object ID:
dbo.fn_UniqueidentifierToCharMsTR ()

MSTR Desktop Object ID to MSTR Metadata Object ID

dbo.fn_CharToUniqueidentifier()


Metadata query to get user and group details Excluding Everyone group:

select GROUPNAME.object_name [Customer_group],USERNAME.object_name [User_name] from dbo.DSSMDOBJINFO GROUPNAME
      join
            (select object_name,OBJDEPN.depn_objid from dbo.DSSMDOBJINFO b
                   join
                    (select distinct Object_id,depn_objid from dbo.DSSMDOBJDEPN
                     where object_id in (select object_id from dbo.DSSMDUSRACCT where isgroup=0)
                     and depn_objid <> (select object_id from dbo.DSSMDOBJINFO where object_name ='Everyone')) OBJDEPN
             on OBJDEPN.object_id = b.object_id) USERNAME
on USERNAME.depn_objid =GROUPNAME.object_id
where GROUPNAME.object_name <> 'Everyone'
order by GROUPNAME.object_name


Metadata query to get all users:

select distinct login from dbo.DSSMDUSRACCT where isgroup =0

Metadata Query to get number of users:

select count (distinct login) from dbo.DSSMDUSRACCT where isgroup =0

Metadata Query to List all object which is having access to particular group:


 select Object_name,Object_id,Object_type from dbo.DSSMDOBJSECU
where Object_id in (
  select Object_id from dbo.DSSMDOBJSECU
    where trust_id = (select Object_id from  dbo.DSSMDOBJINFO where object_name like ('User GROUP'))
and project_id ='Project ID')


Metadata Query to get Object Path from Object_id:

create table:
--------------------------
create table #temp_object_id ([PROJECT_ID] [uniqueidentifier] NOT NULL,[OBJECT_ID] [uniqueidentifier] NOT NULL, object_path varchar(max));

insert records:
--------------------------
EX:

insert into #temp_object_id (PROJECT_ID,OBJECT_ID) values( 'PROJECT_ID','OBJECT_ID');


how to do :
--------------------------
play with excel


procedure to generate result:
------------------------------

begin

declare @temp_project_id uniqueidentifier

declare @temp_parent_id uniqueidentifier

declare @temp_object_id uniqueidentifier

declare @orig_object_id uniqueidentifier

declare @temp_object_name nvarchar(max)

declare @object_name nvarchar(250)

declare @Count Int

declare @loop_check int

declare @LoopCount Int

declare @sql varchar(max)


Select Identity(int,1,1) ID, PROJECT_ID, OBJECT_ID into #Temp from #temp_object_id

Select @Count = @@RowCount

Set @LoopCount = 1

select * from #Temp

While @LoopCount <= @Count
Begin


Select @temp_project_id=PROJECT_ID,@temp_object_id=OBJECT_ID,@orig_object_id=OBJECT_ID from #Temp Where ID = @LoopCount

--print '@temp_project_id=' + cast( @temp_project_id as varchar(250))

--print '@temp_object_id' + cast( @temp_object_id as varchar(250))

set @loop_check =1

--'00000000-0000-0000-0000-000000000000'

set @temp_object_name = ''

While (@loop_check <> 0)
Begin        
      
      
       select @temp_parent_id=PARENT_ID,@object_name=OBJECT_NAME from DSSMDOBJINFO where OBJECT_ID=@temp_object_id and PROJECT_ID=@temp_project_id
      
     
      
      
       --print '@temp_parent_id=' + cast( @temp_parent_id as varchar(250))
      
    --print '@object_name' + @object_name
   
    if ( @orig_object_id != @temp_object_id )      
         set @temp_object_name = @object_name + '/' + @temp_object_name
      
       --print @temp_object_name
      
        set @temp_object_id = @temp_parent_id
      
       if (@temp_parent_id = '00000000-0000-0000-0000-000000000000')
               break
      
End

print @temp_object_name

update #temp_object_id set object_path=@temp_object_name where
PROJECT_ID=@temp_project_id and OBJECT_ID=@orig_object_id

Set @LoopCount=@LoopCount + 1
           
End

Drop table #Temp

End


result from temp table :
--------------------------


select * from  #temp_object_id