SQL, VB, MicroStrategy & Other Topics

I recently had a need to trigger Intelligent  Cube refresh based on a certain event. That certain event was ETL completion. My ETL writes begin and end times to a log. I could easily have a service run that would periodically monitor the log and refresh the Intelligent Cube. That seems to be too much of an overhead when I can simply call the Command Manager to execute the cube refresh from the ETL. Image Source: https://www.rubiks.com/solve-it/3x3 You will need to create a MicroStrategy Event, MicroStrategy Schedule, schedule Intelligent Cube Refresh, create Script file, create Batch file and that pretty much sums it all! Create an Event Select New, Event and name it – Events are an empty object - as in they do not do anything, it is just an empty shell. Call this event a proper name. In my case, it is aclled Batch Cube Refresh - Short Form. Create a Schedule that uses the new Event Select New > Schedule Name the schedule, in my case '...

Here is a pretty simple request from my client - " Our work week starts on Monday and ends on Sunday. Can you make sure that the calendar in date prompt also starts from Monday? " Me - "Sure why not, MSTR is pretty flexible". I was so wrong! There is no [easy] way to do this in MicroStrategy. One solution that 'kind of' worked for me: 1. In preferences, change the locale for date and numbers to European. This will change the display to show Monday first. Issue: Date format changes to DD/MM/YYYY and here in the US we need MM/DD/YYYY. 2. To address the 'side effect', based on TN 12819 ( https://resource.microstrategy.com/Support/MainSearch.aspx?tnkey=12819&formatted=1 ) I made appropriate changes to format_config.xml. This now changed the date format to US format when selected from Calendar Date prompt. But default values still showed up in European format. Furthermore, within the report the dates showed up as YYYY-MMM-DD and using Advanced ...

Have you wanted to do this? Very easy to so  with version 9.x. Accessible from Project Configuration > Project Definition > Communications.

See the screenshot below. Is that the issue you are facing? Incorrect Row Height when exported Instead you want it like this...? Expected Layout of Row Header If so, it is a pretty simple change. Within document design, select the document part where the grid is located (eg Detail Header etc). Go to Format > Properties (MSTR 9.x) Select Layout tab Under Excel section, select the checkbox  that says 'Automatically fit rows' Problem fixed. Please excuse the absence of data in screenshots for obvious reasons!

Error: SQLEngine got an Exception from DFC: [DFCENGINE] Engine Logic: Fact does not exist at a level that can support the requested analysis. Fact: "Visitor Count". Level: "Visitor ID, Visitor Center Name and ID". Error in Process method of Component: SQLEngineServer, Project VUE Reporting, Job 43595, Error Code= -2147212800. If you have worked with MicroStrategy, you have definitely come across this error at least once! First things first - did you recently change any attributes? If you did, did you update the schema? If no, then that is most likely your problem. Update the schema from MSTR Desktop > Schema > Update Schema or Ctrl+U. If that does not fix your problem, I may not be able to give you an exact answer but can guide you through troubleshooting the problem. Below, I will use my error mentioned above related to Visitors to guide you through troubleshooting. The steps mentioned below may be a little long for those of you who are already famil...

Here is a function that will return first day of the current quarter. This can be used within MicroStrategy via ApplySimple functionality as pass through function. CREATE FUNCTION dbo.udf_GetFirstDayOfCurrentQuarter (@getdate as datetime ) RETURNS DateTime AS BEGIN RETURN CONVERT ( DATETIME , convert ( varchar (2), case ( month (@getdate)%3) WHEN 0 THEN ((( month (@getdate)/3) - 1) * 3) + 1 ELSE ( month (@getdate) - ( month (@getdate)%3)) + 1 END ) + '/1/' + convert ( Varchar (4), YEAR (@getdate)) ) END

Many people have hard time understanding the Level Metrics in MicroStrategy - including me. Here is a quick cheat sheet I put together for reference. Consider the following data layout: Country, Regions, Call Centers Geographical Hierarchy: Country --< Regions --< Call Centers Total 6 call centers : Region1 (R1C1, R1C2), Region2 (R2C3, R2C4), Region3 (R3C5, R3C6) Report Filter : Call Center in (R1C1, R2C3, R2C4) 'The Cheat Sheet' TARGET ATTRIBUTE FILTERING GROUPING Revenue Summary Outcome Region STANDARD STANDARD Apply Report Filter to Call Center output on report and SUM all Revenue for Call Centers displayed on report and Group By Region Region1: R1C1 Region2: R2C3 + R2C4 Region STANDARD NONE Apply Report Filter to Call Center output on report and SUM all Revenue for Call Centers displayed on report and do NOT Group By Region1: R1C1 + R2C3 + R2C...

When I take a break from work, I pretty much just find a personal project to get busy with! This time it was the Network Widget in MicroStrategy 9.3.0 that got me busy. I have been working on my family tree using Legacy Genealogy software. Being of the Indian (East) ancestry, I wanted to represent the relationships using terms from Indian languages. For example, mother's brother in English is Uncle  but in Gujarati/Hindi is Mama . Father's brother in English is Uncle  but in Gujarati/Hindi is Kaka  and Chacha  respectively. I wanted to make a 'dashboard' of my own that would represent these terms and allow me to filter by family members and individuals. So I started with a database. Since this is just a proof of concept (to myself), I decided to keep the design pretty simple. Database Design The design comprises simply of 3 tables. First and foremost are two 'primary' tables - tblRelationships and tblFamilyMembers. tblRelationships defines relationship nam...

Your business may see a need to hide recipient names in the emails generated by Narrowcast. This can be achieved by selecting appropriate setting in Narrowcast Administrator > Administration > Information Transmitters > E-mail (SMTP) properties > Show Advanced Properties > Recipient Field tab .

Behavior : Within MicroStrategy 9.x in Web environment, Hierarchy prompts time out and throw 'Request timed out' error. Solution : There are two issues to address here: 1. See the underlying query for prompts and make sure it is well optimized. A prompt should not take more a few seconds to load. Underlying SQL query can be obtained from Jobs monitor or a tool like SQL Profiler. 2. If all is well under the hood and no further optimization is possible, increase the request time out. This could be achieved by changing the value for "timeout" property in FrameProcessor.xml file on the web server. This file is generally found under C:\Program Files (x86)\MicroStrategy\Web ASPx\WEB-INF\xml\blocks. Remember, the value represents time out in milliseconds. <!DOCTYPE block SYSTEM "../dtds/blockLibrary.dtd"> <!-- HTTP Processors --> <block name="FrameProcessor" inherits="BaseModel"> <definition...

I have a linked server from Microsoft SQL 2008 to a DB2 server. Today when I tried to run a SELECT statement based on the linked server, I hit this error, "cannot create a column accessor for OLE DB provider "ibmdasql" for linked server". Earlier in the day, we had restarted the SQL Server Service. Running the following script on the 'affected' sql server should fix the issue. USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[usp_enum_oledb_providers] AS exec sp_enum_oledb_providers GO sp_procoption 'usp_enum_oledb_providers', 'startup', 1 Restart the sql server service after running above script.

I am back after a while! I have accepted a new position which means more responsibilities. I now lead a Business Intelligence initiative. This opportunity is exciting and interesting. I get to do what I love to do! So first task at hand was to be able to access data from the AS400 system for ETL to MS SQL 2008. Simple, right? Simple? I thought so, but got stumped for the first few minutes and thought many others might have the same issue! If you do not see IBMDA400, IBMDARLA and IBMDASQL as in the above screenshot, you are mssing some drivers. Issue at hand - cannot find proper drivers on MS SQL installation to connect to the AS400 system. What you need to do is install IBM Access iSeries for Windows software on your SQL Server box. http://www-03.ibm.com/systems/i/software/access/windows/index.html . After installing this software, logout and log back in. Once that is done follow these steps. 1. Navigate to SQL Server Installation > Server Objects > Linked Servers ...