I have recently gained the Permanent Resident status for Canada and I will be moving in Toronto next month. Hurray!!! 
So if you are one of our frequent readers from GTA and you just happen to know a place well suited for an experienced and innovative MicroStrategy developer, please contact me at ssorin@gmail.com or check out my full profile at http://www.linkedin.com/in/sorinsuciu.
While the merge with Object Tracking Tool is still in development, I am proud to introduce the new and improved version of Metadata Browser, which you can download here: MBsetupV2.0.exe
Improved Documentation Tool
New properties are now retrieved for common object types:
Change Journal Tools
This new function only works with MicroStrategy version 9.0 or higher and it either shows the Change Journal entries for a single object or for all the objects in a particular folder.
God Mode
If you have admin rights you can connect using “God Mode” and thus gain access to hidden system files. An interesting use (found during the test phase) is to navigate to System Objects\Columns and make dependency searches directly on Columns.
This is a read only application. Using it won’t cause any changes to your Metadata.
A while ago I had the opportunity to prepare a short training on the FreeForm SQL reports for some of my colleagues. I thought I’d better share this with you as well, so… here is a not-at-all-exhaustive list of things worth knowing about this type of report.
What is a FF SQL?
FreeForm SQL (aka FreeStyle SQL) is a type of report which allows the designer to define its own SQL code. Notice this is a rather different approach than what we are normally used to in MicroStrategy, where we would usually let the SQL Engine do the job.
When should it be used?
1. Whenever you tried everything (VLDB properties, Metric Levels, Attribute Relationships, Table Logical Size, Data Model changes…) and the SQL Engine still doesn’t perform that join the way you want it to.
2. Whenever you want to use a data source which does not exist in the Warehouse Catalog. That’s right, with FF SQL you can use almost any data source, including those tables you see in the left hand side of the Catalog.
How do I recognize it?
You don’t, at least not from the outside. You can either edit a report and see if you can spot the unnoticeable “Freeform SQL Definition” button, or you can right click on a report and make a component search. If it has no components, you found it.
So, unless you set up some naming convention for FF SQL reports you will very soon lose track of them.
How is it created?
Click on “New -> Report” and select “Freeform SQL” from the list (you need a special role to see this option).
Type your code in the upper right hand side frame.
Now you have to map the items from the “Select” section of your script to virtual Attributes and Metrics. To do this, right click on the lower frame and select either “Add new Attribute form” or “Add new Metric”.
For a script like “SELECT country_id, country_name, SUM(sales)…” the mapping should be defined as:
These are virtual objects. You may actually have a “Country” Attribute in your project, but please don’t make the mistake of thinking these two are the same thing.
Click OK and you are back on familiar grounds.
Prompts in FF SQL
You can either create or insert them into a WHERE clause, by using the two dedicated buttons. The Prompts will show in bold pink.
Now, here is the interesting part. Let’s say you want to make some changes to the script, and you copy it into an external editor. You will notice that the prompts were converted into object IDs. Now, paste the text back into the FF SQL editor. They are bold pink again. Too bad the report won’t work anymore…
The prompts will have to be inserted again, the “button” way, if you want the report to run properly.
Words of caution.
Being what I call a “manually defined object”, the FF SQL is not subjected to automatic Schema updates. That is to say, you won’t be warned if you delete a table which is being used by such a report. So please bear in mind that maintenance for FF SQL can be harsh.
Note: Part 3 includes the work done so far in Part 1 and Part 2.
You probably found out by now that the Type and SubType properties of an IDSSObjectInfo (or similar class) return a numeric value. Call me picky, but if I were to build an application I would rather use layman’s terms such as Attribute or Drill Map instead of SubType 3072 or SubType 14336.
This third part of our tutorial offers a great way to convert these not-so-intuitive numerical values into their English meaning, by using a custom made function called DSS2Name.
You can download the VB6 files here:
SDK Kit part 3.zip
In the first episode of this series I showed you how to connect to a MicroStrategy project using VB6. Time has come to look at three ways that can be employed to find an object.
Two of these methods (objSearch.Execute and ObjectSource.FindObject) are actually covered by Tech Notes on the Knowledge Base. Nevertheless, I thought to embed them in easy to use Functions.
The third method is the famous Search by ID, as I think is time for me to disclose its secrets… To be honest I never thought I would, but hey, it’s my 3 years within MicroStrategy community anniversary, so this one is on me
You can download the VB6 files here:
SDK Kit part 2.zip
Recent Comments