Microstrategy Blog

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