Microstrategy Blog

Okay, so this is an old one, but I still find it useful.

If you can’t run a report because of a spool space error, and you can’t convince the DBA to give you a couple of gigabytes more, then you might want to go to the Report Editor, Data ->VLDB Properties -> Tables -> Intermediate Table Type and select True temporary table.

What this does is modifying the SQL so that instead of having a huge chunk of code (that would use up a lot of memory) it breaks the code into CREATE – INSERT bits that are far easier to handle. It also makes the SQL more readable by humans (this includes programmers).

By the way, this setting also comes to rescue when you get the “Database is terrified by the script and it can’t even begin to analyze it” error. I don’t have the specific error message at hand, but I know for sure that Teradata does this when the SQL code is all in one pass and is so large that it can be seen from the Moon.

Reports, Theory, Things I Learned, Tips and Tricks

How to find that elusive column in your SQL server.

Tip of The Day:

SELECT
name
FROM
sysobjects
WHERE
id IN (SELECT id FROM syscolumns WHERE name like '%COLUMN_NAME%')

All Things Data, Basic Series, Data Warehouse, Tips and Tricks SQL Column Search

Not every Logical Table has to come from your Data Warehouse, especially when you’re dealing with Views. Sometimes it’s just easier to make the view “inside” MicroStrategy, but there’s a few rules you have to follow.

First, navigate your way to the Tables folder under your Schema Objects. Create a new Logical Table. In the example, I’m creating a new Natural Account List where the ID is less that 100. Here’s the thing to note: See how right now the cursor sets to the right of “a11.” in the SQL statement? Unless you are creating a new ID or Desc, make sure that you don’t add them simply by typing. Drag the respective columns from the table that you’re using in the FROM statement, this way, MicroStrategy knows that they’re the same. Drag it into the SQL statement area, as well as the column object. Don’t simply click “Add” and then type out the information (I learned this the hard way), otherwise, you’re going to get “Natural_Account_ID (1)” when you review the table later, and your attributes won’t recognize this a being applicable to the base lookup table. When you drag the column titles, it forces MicroStrategy to recognize them as a match.

Logical Tables are an ideal way to make your Full Outer or Left Outer Join statements. A good use for this (or at least one I used) is if you’re mapping Purchase Orders to Accounts Payable times. Not every Purchase Order is going to have a payable, since some POs are still open. So here’s the rough SQL.

SELECT
a11.PO_ID
,COALESCE(a12.AP_ID,0) AP_ID
FROM
LU_PURCHASE_ORDER a11
LEFT OUTER JOIN
LU_ACCOUNTS_PAYABLE a12
ON
a11.PO_ID = a12.PO_ID

Basically, what I’ve made is a logical table that with the COALESCE statement, allows me to pull all items from the PO system, even those items don’t have a corresponding AP item. Since MicroStrategy usually defaults to INNER JOIN, I’m forcing a LEFT OUTER JOIN without having to tweak any of the VLDB settings. TIP: Make sure that your Accounts Payable table has a 0 record to join with that has “UNKNOWN” data in it, otherwise this whole exercise is useless.

In short, you can manipulate and create your own Logical Tables to make new lists, cloned lists, or union lists, without having to mess with views and tables in your Data Warehouse.

Architect, Logical Table, Tips and Tricks Architect, Logical Table

Under your Administration Drop Down Menu in Microstrategy is “Events”. What is this odd item, and what’s it used for?

Keeping Command Manager extras out of this…and sticking with the straight “Out of the Box” functions…let me tell you why this matters.

One of the advantages of MicroStrategy in the ability to subscribe to reports. When a person subscribes to a report…the project caches that report for a predesignated period of time, in my case 24 hours. These report caches are cleared at predesignated times (see here for that how-to); however, as any administrator who works with ETL processes knows, sometimes you get the managerial, “I need the ETL run right now so I can see X transaction that was just posted.” All fine and dandy, until you realize that all of the reports that he ran this morning, still have a cache in place? Check the “Cache Monitor” under your Administration menu to see what’s out there.

The way to solve this is to create an event like above called “Database Load”, and then create an administration event that clears the cache linked to the event “Database Load”. Then, run your ETL. When the process is done, go to your Event option under the menu again, and trigger the appropriate event. Every Scheduled Item linked to that event will then take place.

Users can create schedules that update reports not only at set time (midnight, 4 A.M.), but also based on a series of events. Need that Balance Sheet repopulated when the Books Close, or maybe, you need to Idle a project when you’re in production? Event manager is a simple way to organize what would normally be a manual process.

Administrator Event, Microstrategy

As posted by MicroStrategy on YouTube. Increased security, increased report creation = Win.

Excel, MicroStrategy 9.0, MicroStrategy Office, Video Presentations MicroStrategy Excel