Commenter Dejan said in a thread yesterday, “When I setup the Metadata in MSTR I was very surprised that I only found the Attribute forms “ID and DESC”
Yep…but, as with other MicroStrategy options…you can make your own. So let’s look at this as the followup to yesterday’s post.
Begin by creating a new attribute form in your Attribute Editor for an existing attribute. You should see the screen below.
Next, select Modify next to “Category Used”, and you should see the resulting menu below.
This is where you can create and adjust your own Form Categories. Using yesterday’s example, you’d enter SORT, along with a nice little description, something like “This is a Sort field”…redundant, but this is how we developers roll. Creating new Forms is great if you have consistent categories that you use like “SORT” or “SHORT DESC”.
This is a little “don’t” that I stumbled upon recently.
Don’t create metric aliases that contain operators such as “-“, “+”, and so long. Although MicroStrategy allows you to do so, the database application (Oracle, Teradata,…) will nag you about expecting “something between – and ,” and of course, the report will stop executing.
I’m putting this is a basic series, but sometime I don’t learn…
TIP: If you’re going to be leaving your desk for a 2 hour long meeting, make sure that you save any complex dashboard or reports you’ve been working on in Desktop…because once you try to run that report two hours later you’ll hate yourself when you find out you’ve been disconnected and everything you worked on for so long JUST DISAPPEARS!!
Last month Jeremy wrote an excellent article on how filters can return unexpected results. My colleague Oznur found a somewhat similar issue and he was kind enough to allow me to share it with you.
So, there was this tiny little report that used an element prompt, and that prompt contained a list of default answers. Nothing fancy so far. However, it has come to my colleague’s attention that this report had turned rogue… How else could we explain that it started returning result sets for attributes that weren’t even chosen by the user when answering the prompt?
Well, there is a simple explanation, though it did required a little bit of outside of the box thinking. MicroStrategy stores the prompt default answers (ID and DESC) exactly as they were the day the prompt was created or modified. But what if something untoward happens to the lookup table, something like modifying the lookup value for a certain ID?
What happens is that while you distinctly remember choosing “Books” you end up seeing the sales for “Beverages”, and that is because the SQL Engine will write the WHERE clause with its mind on the ID not on DESC field. Since you selected “Books” and this lookup value corresponded to “1001”, then the SQL will filter on “1001”. Then, in the last step it will perform a join with the lookup table and get the description of “1001”, which now is “Beverages”.
Ok, this might be amateur stuff here, but you have to start somewhere.
Sometimes the ETL updates conflict with the report cache…so how do you make sure that your users aren’t viewing old data? If your cache is setup to expire every 24 hours, this might create problems for people who subscribe to your reports. There’s the issue of user education (for instance, don’t subscribe to a report at 6 PM if your ETL runs at 11 PM), but the reality is, you’re the administrator, you’re supposed to be able to make the process as simple, painless, and accurate as possible.
Here’s a way you can help move that smoothing process along. Under the administration menu, right click on schedule manager to show the menu listed below.
Select Schedule Administration Tasks, and after picking your project in the next menu, select “Delete Caches” under the option “Choose one of the Following Actions”. Make sure to select which caches you want to delete under the right column “Option”.
It’s as easy as that. Now you can set your report caches to clear after your database load reliably. If the time you want doesn’t exist, then by golly, just make a new schedule.
Reader Atul puts expertness into the mix:
To fully additional automate it you can extend this one step further by
a) Tie an event to Clear the cache using the Microstrategy Command manger event trigger script. Use the ETL tool to trigger the script. Or try to trigger a script by Narrowcast job
b) Schedule the report using Narrowcast and trigger to job execution to recreate report cache.
Recent Comments