Reduce Intelligent Cube Size By Finding Intelligent Cube Objects Which Are Not In Use

If the i-cubes can potentially be reduced in size an audit can be performed on the cube objects to see which cube objects are not being used by any of the view reports, documents, or dossiers.

The below are examples for a few of the common metadata database platforms.

NOTE: To perform this audit, queries are run against the MicroStrategy metadata database. Ensure a metadata backup is taken prior to performing the below actions.

Steps:
1) Identify the object ID of the Intelligent cube to be audited by checking the objects Property window
2) Identify the object ID of the project this cube exists within by opening the Project Configuration

Sample Cube ID = CFAF1E9B4D53990698C42E87C7AF2EB5
Sample Project ID = B7CA92F04B9FAE8D941C3E9B7E0CD754

3) Run the below SQL against the metadata database by replacing the Cube ID and Project ID within the respective location.

The following is the syntax for a MySQL metadata:
select a13.OBJECT_NAME, a13.OBJECT_ID, a13.OBJECT_TYPE, a13.PROJECT_ID, a13.PARENT_ID from DSSMDOBJINFO a13 where Object_ID in (SELECT a12.DEPN_OBJID as Object_ID FROM DSSMDOBJDEPN a12 where a12.object_id = ('CFAF1E9B4D53990698C42E87C7AF2EB5') and a12.DEPN_OBJID not in (SELECT distinct a11.DEPN_OBJID FROM DSSMDOBJDEPN a11 where object_id in (SELECT OBJECT_ID FROM DSSMDOBJDEPN where depn_objid = ('CFAF1E9B4D53990698C42E87C7AF2EB5')))) and a13.PROJECT_ID = 'B7CA92F04B9FAE8D941C3E9B7E0CD754';

4) Review the results returned to quickly identify which attributes/metrics within the cube are not being used.

From these results it can be determined that the Month attribute is not being used within any of the view Reports, Documents, Dossiers that reference this Intelligent Cube. At this point, the Month attribute can be removed from the cube to reduce the publication time and size of the Intelligent Cube within memory.

Other metadata database platforms:

SQL Server:
select a13.OBJECT_NAME, dbo.fn_UniqueIdentifierToCharMSTR(a13.OBJECT_ID) as OBJECT_ID, a13.OBJECT_TYPE as OBJECT_TYPE, dbo.fn_UniqueIdentifierToCharMSTR(a13.PROJECT_ID) as PROJECT_ID, dbo.fn_UniqueIdentifierToCharMSTR(a13.PARENT_ID) as PARENT_FOLDER from dbo.DSSMDOBJINFO as a13 where Object_ID in (SELECT a12.DEPN_OBJID as Object_ID FROM dbo.DSSMDOBJDEPN as a12 where a12.object_id = dbo.fn_CharToUniqueIdentifier('CFAF1E9B4D53990698C42E87C7AF2EB5') and a12.DEPN_OBJID not in (SELECT distinct a11.DEPN_OBJID FROM dbo.DSSMDOBJDEPN as a11 where object_id in (SELECT OBJECT_ID FROM dbo.DSSMDOBJDEPN where depn_objid = dbo.fn_CharToUniqueIdentifier('CFAF1E9B4D53990698C42E87C7AF2EB5')))) and a13.project_id= dbo.fn_CharToUniqueIdentifier('B7CA92F04B9FAE8D941C3E9B7E0CD754');

Oracle:
select a13.OBJECT_NAME, a13.OBJECT_ID, a13.OBJECT_TYPE, a13.PROJECT_ID, a13.PARENT_ID from DSSMDOBJINFO a13 where Object_ID in (SELECT a12.DEPN_OBJID as Object_ID FROM DSSMDOBJDEPN a12 where a12.object_id = ('CFAF1E9B4D53990698C42E87C7AF2EB5') and a12.DEPN_OBJID not in (SELECT distinct a11.DEPN_OBJID FROM DSSMDOBJDEPN a11 where object_id in (SELECT OBJECT_ID FROM DSSMDOBJDEPN where depn_objid = ('CFAF1E9B4D53990698C42E87C7AF2EB5')))) and a13.PROJECT_ID = 'B7CA92F04B9FAE8D941C3E9B7E0CD754';

Comments

MicroStrategy URL API Parameters

MicroStrategy URL Structure The following table summarizes the root URL structure used for every request to MicroStrategy Web. Environment Main Application URL Administration URL J2EE http://webserver/MicroStrategy/servlet/mstrWeb http://webserver/MicroStrategy/servlet/mstrWebAdmin .NET http://webserver/MicroStrategy/asp/Main.aspx http://webserver/MicroStrategy/asp/Admin.aspx Every request sent to MicroStrategy Web calls a central controller. Parameters are appended to Main.aspx or mstrWeb (in a .NET and J2EE environment, respectively) to indicate to the controller how the request should be internally forwarded and handled. The following examples show a URL for accessing a MicroStrategy folder when the user does not have an existing session. The URL contains not only the parameters needed to connect to MicroStrategy Web, but also the parameters needed to log on and create a session. J2EE environment: <a href="http:...

MicroStrategy Developer Preferences options are expanded so big that some options are being cutoff. Show the hidden objects in the Microstrategy developer MicroStrategy Developer Preferences options are expanded so big that some options are being cut off. The steps below given in the MSTR article may not work. This can be simple handled by using the steps below: In the Microstrategy Developer go to Tools -> Preferences (Not my prefernces :) ) Under Developer category -> select Browsing on the browsing tab you see all the options like below: 3. Now using the mouse place the cursor on text box of 10000 which is next to 'Maximum number of monitoring objects displayed per page. 4. Then Hit Tab on Keyboard and hit another Tab on keyboard 5. Then press the space or down arrow on keyboard and click on OK or Enter. That will show the hidden objects in the Microstrategy developer Normal Version ...

Microstrategy Dossiers explained

Microstrategy Dossiers With the release of MicroStrategy 10.9, we’ve taken a leap forward in our dashboarding capabilities by simplifying the user experience, adding storytelling, and collaboration.MSTR has evolved dashboards to the point that they are more than dashboards - they are interactive, collaborative analytic stories . Ultimately, it was time to go beyond dashboards, both in concept and in name, and so the've renamed VI dashboards to ‘ dossiers ’. Dossiers can be created by using the new Desktop product or Workstation or simply from the Web interface which replaces Visual Insights. All the existing visual Insights dashboards will be converted to Dossiers With MicroStrategy 10.9, there was an active focus on making it easier to build dashboards for the widest audience of end users. To achieve this, some key new capabilities were added that make it easier to author, read, interact and collaborate on dashboards ...

Internationalization Design Technics

Microstrategy Internationalization Design Technics MicroStrategy supports data internationalization through two different techniques. You can either provide translated data through the use of extra tables and columns, or you can provide separate databases to store your translated data. These techniques are described below: You can support data internationalization in your database by using separate tables and columns to store your translated data. You can use various combinations of tables and columns to support and identify the translated data in your database. To support displaying the name of each month in multiple languages, you can include the translated names in a separate column, one for each required language, within the same table. Each column can use a suffix to identify that the column contains translated data for a certain language. The same LU_MONTH_OF_YEAR table with translated data for the Spanish and German langua...

Creates a report delivery schedule in Microstrategy

Case functions Microstrategy

Ca se functions Microstrategy Case functions return specified data in a SQL query based on the evaluation of user-defined conditions. In general, a user specifies a list of conditions and corresponding return values. Case This function evaluates multiple expressions until a condition is determined to be true, then returns a corresponding value. If all conditions are false, a default value is returned. Case can be used for categorizing data based on multiple conditions. This is a single-value function. Syntax Case ( Condition1 , ReturnValue1 , Condition2 , ReturnValue2 ,..., DefaultValue ) Example Case(([Total Revenue] < 300000), 0, ([Total Revenue] < 600000), 1, 2) sum(Case (Day@DESC in (“Sat”,”Sun”), Sales, 0) {~+} Sum(Case(Category@DESC In("Books","Electronics"),Revenue,0)){~+} CaseV (case vector) CaseV evaluates a single metric and returns different values according to the results. It can be used to perfo...

Microstrategy "Error type: Odbc error. Odbc operation attempted

"Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [HYT00:0: on SQLHANDLE] [MicroStrategy][ODBC Oracle Wire Protocol driver]Timeout expired" is shown when executing reports from Web When users are trying to execute some reports in MicroStrategy web in particular, they may receive the Error “SQL Generation Complete Index out of range” and “Timeout expired” error as shown below: Possible Causes: One possible cause is that the MicroStrategy Intelligence Server using a cached database connection that was already dropped by the RDBMS. To resolve this: Admin should delete the database connection caches and create a new DSNs in case they are sharing DSNs to connect to different databases. In addition, change the settings for the ‘Connection lifetime’ and the ‘Connection idle time out’. Follow the steps below to perform the mentioned changes and verify the report after each step and some of the settings require i-server r...

mstrio – Python and R wrappers for the MicroStrategy

mstrio – Python and R wrappers for the MicroStrategy REST APIs Connecting to MicroStrategy Create a connection to the Intelligence Server using Connection() and  connect() in Python and R, respectively. Required arguments for the   Connection()  function are the URL for the MicroStrategy REST API server, MicroStrategy Intelligence Server username and password, as well as the MicroStrategy project name. By default, the  connect()  function anticipates your MicroStrategy Intelligence Server username and password. LDAP authentication is also supported. Use the optional argument  login_mode=16  in the  connect()  function for LDAP authentication. Extract data from cubes and reports To extract data from MicroStrategy cubes and reports, use the get_cube()  and  get_report() functions. Use...

Best practices for scheduling jobs and administrative tasks in Microstrategy

Best practices for scheduling jobs and administrative tasks Below are the recommended best practices when scheduling jobs and administrative tasks in MSTR: Executing simultaneous reports can strain system resources. If you have many reports or tasks that need to be executed on the same time-based schedule, consider creating several similar schedules that trigger 15 minutes apart. For example, one schedule triggers at 8 AM every Monday, and another triggers at 8:15 AM. To prevent users from scheduling many simultaneous reports, you can prevent users from scheduling jobs using a schedule by editing the schedule’s Access Control List (ACL). To do this, in the Schedule Manager, right-click the schedule and select Properties , then select the Security tab in the Properties dialog box, and make sure that only users who can use the schedule have Modify or Full Control access to the schedule. Establish reasonable limits on how many scheduled jobs are allowed. For details on ...

Create an alert-based subscription in MicroStrategy Distribution Services

Create an alert-based subscription in MicroStrategy Distribution Services on Web Subscription to a report or Report Services document which will be executed when a certain conditional threshold is met based on another executing report. For example, a scheduled report executes which shows the Revenue by day for the past week. If the Revenue on any one day falls below a certain value, a subscription to another report or Report Services document can be triggered and delivered to a recipient. An alert based subscription can only be created directly on a report; however, another report or Report Services document can be delivered when the alert based subscription is triggered. Note: you need a grid report to create an alert and you cannot create if you want to create on a document with text boxes. The following example will walk through the basic steps on how to setup a subscription based on an alert like this: Follow the brief steps bel...

Microstrategy Analytics

Search This Blog