Microstrategy Enterprise Manager –Extending Date Dimension tables

Hello,

Recently I have come across one interesting issue related to Microstrategy Enterprise Manager (Release prior to 10.x), I just thought it will be useful posting so that  if there  any such issue is in your environment you can take benefit of itJ, I also heard of same issue  from my couple of friends , so here goes the post!!

Issue Description:   There are no data is showing up in the EM reporting for the date later than 31^st Dec 2015 , while EM fact table is populating the data from the statistics table (as shown in example below)

EM Report shows no data later than 31^st Dec 2015.

While fact table do have data for year 2016.

What has caused the issue:

In MicroStrategy 9.x, the Enterprise Manager Warehouse database contains data only until year 2015 (December 31, 2015) for the following Date Dimension tables:

DT_DAY

DT_QUARTER

DT_QUARTER_YTD

DT_MONTH

DT_MONTH_YTD

DT_YEAR

 Also, the partitions on all the Statistics tables and Enterprise Manager Warehouse tables only exist till the end of year 2015.

As shown in the below example

When the first part of query is executed, we can see the data getting recorded up-to-date (as shown in snapshot #2 above),

with       gopa1 as

(select (((EXTRACT(YEAR FROM a11.DAY_ID2) * 10000) + (MONTH(a11.DAY_ID2) * 100)) + EXTRACT(DAY FROM a11.DAY_ID2))  "CustomColumn",

                                a11.IS_PROJ_ID  IS_PROJ_ID,

                                sum(((a11.IS_REP_JOB_ID * 0) + 1))  WJXABC1

                from      IS_REP_FACT     a11 

                group by              (((EXTRACT(YEAR FROM a11.DAY_ID2) * 10000) + (MONTH(a11.DAY_ID2) * 100)) + EXTRACT(DAY FROM a11.DAY_ID2)),

                                a11.IS_PROJ_ID

                )select  distinct pa11.IS_PROJ_ID  IS_PROJ_ID,

                a14.IS_PROJ_NAME  IS_PROJ_NAME,

                pa11."CustomColumn"  "CustomColumn",

                a12.DAY_DATE  DAY_DATE,

                a13.MONTH_OF_YR_ID  MONTH_OF_YR_ID,

                a15.MONTH_OF_YR_LDESC  MONTH_OF_YR_LDESC,

                pa11.WJXABC1  WJXABC1

from      gopa1    pa11

                join        DT_DAY                a12

                  on         (pa11."CustomColumn" = a12.DAY_ID)

                join        DT_MONTH        a13

                  on         (a12.MONTH_ID = a13.MONTH_ID)

                join        IS_PROJ               a14

                  on         (pa11.IS_PROJ_ID = a14.IS_PROJ_ID)

                join        DT_MONTH_OF_YR        a15

                  on         (a13.MONTH_OF_YR_ID = a15.MONTH_OF_YR_ID);

However when you run the whole query , no data is returned despite data exist in EM Fact table and further when you review you will see there is no entry in the date lookup tables for 2016 or later date so there is no matching data available hence not returning any rows.

Below is the sample you can see that  data in the date dimension table has entry only till 31^st Dec 2015.

How should I fix the issue:

Starting with MicroStrategy Enterprise Manager 10, the Enterprise Manager Warehouse date data has been extended until the year 2020.

If it is required to extend the dates and partitions on a pre-10 environment, the following scripts can be used on a MicroStrategy Enterprise Manager Warehouse version 9.x.

Important Note: Customers should make a backup of their Enterprise Manager Warehouse before running the scripts. 

The following script have been attached to the TN270405. Users should select the script based on their Enterprise Manager Warehouse database type and partitioning requirements.

Happy moment: The issue was fixed, once the Lookup table populated with data for 2016 till 2020.

Note: The script for extending Teradata partitions can be found in the following MicroStrategy Knowledge Base Technical Document:

TN219015: How to extend partitions beyond January 2015 for MicroStrategy Enterprise Manager 9.3.x - 9.4.x on Teradata 13.x or 14.x

Note: Users who downloaded the Oracle partition extension script before 1/5/2016 will experience a loss of data for DAY_ID Dec 1st 2015 and later. To fix this issue, users should roll back to a backup of the Statistics tables/Enterprise Manager and run the new scripts which have been posted to TN270405.

Happy Learning!! Hope it is useful!!

Regards,

Arun Gupta

https://in.linkedin.com/in/arunkrdwbi 

How to setup Usher in your MicroStrategy Web& Mobile applications

Setting up Usher in your MicroStrategy applications

Here are high level steps to setup Usher in Microstrategy Web & Mobile

1.     Download and install MicroStrategy 9.5s from https://download.microstrategy.com .

2.     Create your Usher identity network from link https://go.usher.com

3.  Add your MicroStrategy users to Usher, (You must export your MicroStrategy user database as a file with comma-separated values (CSVs), which can then be added to Usher. For steps to export your users follow the steps mentioned in the following Microstrategy Link https://microstrategyhelp.atlassian.net/wiki/display/MSTR95/Exporting+your+MicroStrategy+users+for+Usher 

4.     Add your MicroStrategy applications to Usher and distribute batch to users (Follow the link for steps to add your MicroStrategy applications to Usher and badge distribution) https://microstrategyhelp.atlassian.net/wiki/display/MSTR95/Adding+MicroStrategy+users+to+Usher+and+distributing+badges

5.  Integrate MicroStrategy with Usher (Follow the link for steps to Integrate MicroStrategy with Usher https://microstrategyhelp.atlassian.net/wiki/display/MSTR95/Integrating+MicroStrategy+with+Usher

6.     Once you have completed all the steps mentioned above , follow the steps explained as mentioned below to Set up Usher in your MicroStrategy Web/Mobile

To configure Intelligence Server for Usher authentication

To set up Usher in MicroStrategy Web

1. From the Windows Start menu, select All Programs, then MicroStrategy Tools, and click Web Administrator. The Web Administrator page opens, with a list of your Intelligence Servers.
2. For your Intelligence Server, click Modify . The Server Properties page opens.
3. Next to Usher Configuration, click Setup. The Usher Configuration page opens.
4. In the Connectivity section, in the OrgID field, type the Organization ID from Usher Network Manager.
5. In the AppID field, type the Application ID from Usher Network Manager.

6.     If you want to use Usher as a two-factor authentication system, select the Enable two-factor authentication check box. The Security token field is enabled. 

7. In the Security token field, type the Security Token from Usher Network Manager.
8. Click Save to save your changes. To save the Usher configuration, you are prompted for your MicroStrategy credentials. 
9. Type your MicroStrategy username and password, and click Login. The settings are saved.

Note: The MicroStrategy system administrator account (administrator) is exempt from two-factor authentication, and does not need to provide an Usher code to log into MicroStrategy Web. It is recommended that you use a secure password for this account, and use this account for troubleshooting MicroStrategy Web.

To enable Usher authentication in MicroStrategy Web Administrator

1. From the Windows Start menu, select All Programs, then MicroStrategy Tools, and click Web Administrator. The Web Administrator page opens.
2. On the left, select Default Properties. The Default Server Properties page opens.
3. In the Login area, for Trusted Authentication Request, select the Enabled check box. 
4. From the Project List drop-down list, select Show all the projects connected to the Web Server before the user logs in. 
5. From the Trusted Authentication Providers drop-down menu, select Usher. 
6. Click Save to save your changes.

Your can enable additional security for MicroStrategy Web by allowing your users to log into Web using Usher in one of the following ways:

Usher as a primary authentication provider: Your users log into Web by scanning a QR code that verifies their credentials using Usher, as shown in the example below:

·         Users scan the QR code using their Usher badges, and are authenticated automatically, without entering a username and password.

Usher as a two-factor authentication method: If you use Standard authentication, your users can log into Web by providing a username, password, and a randomly generated code, as shown in the example below:

     

Users type in a randomly generated Usher code that is generated by the Usher app

To set up Usher in MicroStrategy Mobile

1. You can enable secure access to MicroStrategy Mobile through Usher. When your users open MicroStrategy Mobile on their iOS devices, they can log into your MicroStrategy environment using their Usher badges, without needing to enter a username or password.
   You can also take advantage of advanced features, such as requiring users to access Usher using their fingerprints (on newer iOS devices), restricting access based on users' locations, and so on.

   To configure Intelligence Server for Usher authentication

   1.    From the Windows Start menu, select All Programs, then MicroStrategy Tools, and click Web Administrator. The Web Administrator page opens, with a list of your Intelligence Servers.
   2.    For your Intelligence Server, click Modify . The Server Properties page opens.
   3.    Next to Usher Configuration, click Setup. The Usher Configuration page opens.
   4.    In the Connectivity section, in the OrgID field, type the Organization ID from Usher Network Manager.
   5.    In the AppID field, type the Application ID from Usher Network Manager.
   6.    If you want to use Usher as a two-factor authentication system, select the Enable two-factor authentication check box. The Security Token field is enabled. 
   7.    In the Security Token field, type the security token from Usher Network Manager.
   8.    Click Save to save your changes. To save the Usher configuration, you are prompted for your MicroStrategy credentials. 
   9.    Type your MicroStrategy username and password, and click Login. The settings are saved.

   To enable Usher authentication in MicroStrategy Mobile Administrator

   1.    From the Windows Start menu, select All Programs, then MicroStrategy Tools, and click Mobile Administrator. The Mobile Administrator page opens.
   2.    On the left, select Default Properties. The Default Server Properties page opens.
   3.    In the Login area, for Trusted Authentication Request, select the Enabled check box. 
   4.    From the Trusted Authentication Providers drop-down menu, select Usher. 
   5.    Click Save to save your changes. 
   
   
   References:
   All the required information available at Microstrategy site https://microstrategyhelp.atlassian.net/wiki/display/MSTRDOCS/MicroStrategy+Help

Microstrategy Administration Tips-Maintenance Mode Feature

Though there are many good stuff are in Microstrategy KB , yet I believe this should be a good topic to talk about in my blog on "Maintenance Mode Feature in MicroStrategy".

Before Microstrategy release of v9.3.1 , when an Intelligence Server node in a cluster get a normal shut down, the node typically is also  removed from the cluster that means anything that is created on that node , for example Cubes or Caches or History messages . while in case of  abnormal termination of Intelligence Server process Microstrategy is built smart enough to support high availability seamlessly on a clustered node , that means when abnormal termination takes place , the resource are still available from the other node.

With the release of MicroStrategy 9.3.1 onwards ,  the Intelligence Server can be configured to keep the Intelligence server clustered resources available seamlessly, even if in the case of node is shutdown normally, this setting can be enabled or disabled from Registry entry , if you are on windows you can configure it under registry & On Linux this can be configured on Linux registry file called MSIReg.reg under installation directory , this setting is called  "Maintenance Mode".

Here is step by step instruction to configure this setting on Windows & Linux Implement the steps below to enable maintenance mode. These changes should be implemented on all the nodes of the cluster: 

1# Shut down MicroStrategy Intelligence Server(s) where the change needs to be done.

2# Open the Windows registry and navigate to the following location: HKEY_LOCAL_MACHINE\SOFTWARE\MicroStrategy\DSS Server\Castor. 

3# Create a new DWORD (32-bit)value and name the entry “MaintenanceModeEnabled” and set its value to 1.The registry location should look as shown in screenshot below.
 

4# Also, it is recommended for users to create the following two registry entries as well in MicroStrategy Intelligence Server 9.4.1 and later. 

These entries should also be created as new DWORD (32-bit) and the value of these entries should be set to 1. SkipClusterChecking Cluster Synch Check Duplicate Cubes , 

The final registry entries should look as shown in the screenshot below: 

Note: To disable maintenance mode, change the value of the newly created registry entry to 0 or delete these entries from the registry. UNIX/Linux Implement the steps below to enable maintenance mode.

 These changes should be implemented on all the nodes of the cluster:

1# Stop MicroStrategy Intelligence Server(s) where the change needs to be done.

2# Create a backup of the MSIReg.reg file found in the user's home directory. 

3# Edit the MSIReg.reg file, which is found in the MicroStrategy home directory ($MSTR_HOME_PATH). 

4# Find the section "HKEY_LOCAL_MACHINE\SOFTWARE\MicroStrategy\DSS Server\Castor". 

5# Add the following entries to enable the setting (just below the entry that reads "IgnoreAllExceptions"=dword:00000000: "MaintenanceModeEnabled"=dword:00000001 

6# In MicroStrategy Intelligence Server 9.4.1 and later, the following registry entries are also recommended to be added: "SkipClusterChecking"=dword:00000001 "Cluster Synch Check Duplicate Cubes"=dword:00000001 

7# Start the Intelligence Server for the change to take effect. 

 References: Refer to the following MicroStrategy Knowledge Base documents for information on the additional registry settings discussed in the above steps :

TN45399: Maintenance Mode Feature

TN221178: How does the registry setting 'Cluster Synch Check Duplicate Cubes' work in a clustered environment in MicroStrategy 9.4.1 and later 

TN47195: In a MicroStrategy 9.2.x - 9.4.x clustered environment, when one of the machines hosting the MicroStrategy Intelligence Servers is shutdown, the performance of other MicroStrategy Intelligence Servers in the cluster is affected.

Thanks for reading.

Regards,
Arun Kumar Gupta