We have moved

We have moved the blog to own domain: http://www.microstrategy101.com

Change your Feedburner and Google Friend Connect.

Also the MicroStrategy Forum can be found here.

Comments are blocked on this blog now.

Manual mapping

In a previous post I’ve warned about the not so automatically behavior of the automatic mapping. However, I can’t blame the manual mapping for the same degree of inconsistency. I mean, boy, that’s as manual as it gets!

Take a manually mapped attribute and have a look at its source tables. Now drop the attribute from one of these tables (on database and Catalog level) and try to run a report which used that table.

On the one hand, MSTR knows from the attribute definition that it can include that table into the SQL. On the other hand, it should know from the table definition, as updated in the Warehouse Catalog, that the column is no longer there, but such a small detail doesn’t have enough force to deter the SQL Engine. So it simply writes the SQL as if nothing had happened, and the only error you get is from the database (i.e. a11.attribute_id does not exist).

So, if you aren’t doing this already, don’t forget to inform the manually mapped attribute that one of its source tables decided to discontinue their relationship, next time you indulge in such data model changes.

MicroStrategy 9 GA Features

Dashboard Features:
 Easy to add Tabs and report layouts(Panels)
 Default templates available to choose the number of reports per tab
 Tab adding is just clicking plus sign(+)
 Drilling is possible in AJAX or DHTML mode
 All, Total features are introduced and bugs are fixed
 Many graph issues in 8 are fixed in 9

Prompt & Filter
 Prompt and Filter can be created in WEB
 New web options for prompt values display, Search in list view, drop down box and many new web options for filter and prompt
Total, Sub Total
 Advanced totaling, Display options (What ever available in Desktop, same features are now available in WEB

Derived Elements
 After report execution; kind of consolidation and partial custom group is possible (but actual cons and cus grp) and derived element can be saved and reuse the same.
 Example: Jan+feb or making Jan& Feb row together as ‘Winter’ and or total of MAR+APR
Threshold
 Threshold is possible in WEB on the fly. It’s just slider bar…you can set many thresholds with different color just on the fly.

Document Report
 Reports can be placed parallel in document report

Back & Forward button
 In MSTR-8, if you click on IE back button, session will be expired. But in MSTR-9, inside the MSTR-9 web there is option for back & forward.
 Suppose you applied some total, formatting, formula on the fly and if like to switch to old one, you can just click on back button

Preferences, Notes, Screens
 Extra preference like web colors, etc
 Different screen in MSTR 8, like menus and icons
 Folders/objects can be viewed in tree view (expand like windows explorer)

Delivery Service in WEB (new feature like Narrowcast server):
 It looks in the upcoming MSTR version, Narrowcast component won’t be there
 Delivery service: you can subscribe reports for email or folder, or you email reports directly.
 You add many email IDs, subscription
 Everything in WEB
Note: you cannot import LDAP or SMTP server emails from server, its has been raised as enhancement request, may be in 9.x we can expect.
Note: Dynamic subscription not possible in delivery service, next version may have (Not sure)

CUBE (New very big feature)
 Cube is kind of Cognos Cube. You can pull many attributes and metrics into metrics as cube report. And when you link other normal reports with cube report, it will fetch data from cube report.
 Cube report can scheduled
 Cube object deleted, reports linked to cube report won’t work.
 There cube at user level, grp level and many things are there…you can find more details in website or help in MSTR-9.

CUBE Advisor: (Another big feature)
 If I have to say in one line: it will advise you to make how many reports can be converted to cube link report

Admin:
 Many new features in MSTR 9 Admin
 Special one: Users, Grps, Sec.Filters can be created in WEB under Admin login.

Architect: (New component in MSTR DESKTOP)
 Like BO, OBIEE tools here you can make table level relationships.
 No need to add key attributes as a child/parent.
 Its good component, this is what we were looking from MSTR.
 Still doing many testing, investigation on some of the scenarios, so will post more once I complete the analysis.

Multiple Sources:
 Multiple source can used, differentiae the tables with colors……
Metric name can be removed (no more “Metric” name in reports)


Other features;
 New data types (like NCHAR, Binary data type)
 New SQL tuning properties
 New OLAP functions
 New forecasting functions
 Temp table indexing
 Data mining functions
 Linking websites in dashboard
 Banding
 Portlet to portlet (web linking feature)
 Multi select rows, columns on the fly and do manipulations.
 Watermark in documents, reports background
 Linking report from dashboard
 Personalized Prompt Answers
 Metadata and Data can be in same DB
 Importing delivery service from NCAST to Delivery service
 64 bit-Windows supported
 64 bit web server supported
 Command manager, Object manager, Project merge, desktop license are separate now.
 Eclipse can be linked to WEB files and easy to customize the MSTR WEB 9
 Now Caching can be done at EXCEL, WORD,XML, HTML CSV format (means when you export report it can be cached at excel level)
 AND many more new features……will post when ever come across……

Email if any queries: senthil.raj.u@gmail.com

How to hide Metric column in MicroStrategy

For hinding Metric column only in Web: (assuming that English language was the Locale.)

1. On the Web Server machine, go to the default location of <>\Web ASPx\WEB-INF\classes\resources and open up the file called MessagesBundle_en_US.properties.
2. Go to the line 'mstrWeb.1158'. For English language, 'Metrics' is the value.
3. Delete the word 'Metrics' and leave one single space.
4. Save the close the file.
5. Restart Web server.

Note: If an environment is being accessed by users that may not be using the project default language, the administrator will need to go in and modify each MessageBundle properties file for that respective language and clear the mstrWeb.1158 Metrics reference.

-------------------------------------------------------------------------------------------------

For hiding Metric column in Desktop for Reports and RS documents.

1st method

1. Goto Grid menu nad check that Automatic Column width is disabled.
2. Place the mouse between the Metrics column and the first metric on the report. Then, drag the column to the left until it is no longer present on the report:.

2nd method

1. Open Options under Grid menu.
2. Open tab Columns and Rows.
3. Select radio button Fixed.
4. Select Metric from dropdown menu and set 0 as pixen as width.

MicroStrategy event in India

Finally, MicroStrategy Inc is going to host BI classes in India.

20th May: co-sponsored by Systech
22nd May: co-sponsored by InfoStep

Location are not specified, but looks like they would be hosted at Chennai and Mumabi respectively.

Search by ID is dead. Long live Search by ID!

It appears that someone at MicroStrategy was receptive enough to come up with the idea of including a search by ID function in version 9. It’s called… “Search by ID” (act surprised now) and it can be found under the Tools menu of the “Search for Objects” window.

This, and the new “Translate” function in MicroStrategy 9 render my “Search by ID v2” application almost obsolete. I refrain from calling it completely obsolete for the sole reason that it still offers the direct and recurring dependents export to Excel function. To the date I still haven’t found a recurring dependents search function in MicroStrategy Desktop.

By the way, since I was confronted with a case when this tool was not able to find an object, I feel compelled to add a small tech note. If the user you log in with doesn’t have the right to “see” a particular object, then of course this object won’t be found by Search by ID v2. This may happen if the object you are looking for is placed in a colleague’s personal folder, or if that object has some kind of “Deny all” permission set for your user/usergroup.

MicroStrategy license is Absolutely free now

Not within a month after the release of Free Reporting Suite (that's what MicroStrategy, Inc calls the free BI product), MicroStrategy, Inc. is giving away the must have features finally absolutely free. I user named rich Arriaga pointed out the same to me. First, I disbelieved, but I see that there are lot of change on the site itself. But one image showing that you need to pay for must have features still exist (check the image above). I thought I must have had done a mistaken in missing to read NOT INCLUDED during initial launch of Reporting Suite, but the site/license policy is indeed changed.

Also, now MicroStrategy explictly states that that technical support cost 18%, which I had posted earlier. Something that was missing from MicroStrategy, Inc site.

This will certainly help the MicroStrategy professionals community a lot and MicroStrategy clients in long run in easily hiring MicroStrategy professional readily and at good rate.

Filtering Compound Attributes

Scenario:
Attribute1 is a compound key attribute with a ginormous number of values. In fact, there are so many values that when you attempt to see all its elements, you get a spool space error.

You also have a list of a few hundred elements (ID1 and ID2) belonging to this attribute and you somehow must build a report that would only show results for these elements.

Deal breaker:
As mentioned before, manually selecting the elements is out of the question. Not only does the spool space error occurs, but just imagine handpicking hundreds of values.

Having two filters, one for each ID, in which the appropriate values are entered using the “In List” option is also a bad idea. You will get much more than you expect, regardless if there is an “AND” or an “OR” operator between the two filters.

Solution:
As far as I tested, qualifying on multiple IDs of the same attribute is not possible in MicroStrategy, not even in version 9. Theoretically the element qualification is what we are looking for, but as stated before there are certain limitations.

The only solution that I can think of is to create a FreeForm SQL Report. Writing the WHERE block should be no problem, as you can generate it in MS Excel based on the two columns… something like =CONCATENATE("OR (a11.Attribute_ID1 = ",A1," AND a11.Attribute_ID2= ",B1,")").

If you know of better/faster ways, please share.


Update:
A better solution was presented to me by AK.
Simply create an additional attribute DESC form as a concatenation between the two IDs, separated by a pipeline character. From here on it gets much simpler.

1CPU installation on multi-core CPU machine

This is wrt Windows

As per the MicroStratey license, product can not be installed on a machine having more number of CPUs then licensed. OS system sees each core of CPU is seen as separate CPU. don't know what's the case with MicroStrategy. (As per the definitions document of MicroStrategy license)

But tech notes are very contradicting. MicroStrategy Intelligence Server is limited to the number of CPUs purchased, even if the machine hosting MicroStrategy Intelligence Server has more than this number of licensed CPUs. For example, if two CPUs have been purchased, even if a machine where MicroStrategy Intelligence Server is installed has four CPUs, MicroStrategy Intelligence Server will only use two CPUs on that machine.

The good thing is that tt is possible to configure four-processor machine to only use one processor. Likewise, a license for more than four CPUs would allow users to allocate from one to four processors on a four-processor machine.

If you have any information about the same, please share.

Free MicroStrategy license, should you really "Buy" that

I would recommend you NOT jump from you seat by reading the word FREE. If you are a person who is a stakeholder for purchase decision of a BI product, you must be knowing it well that Nothing comes free in life.

Remember following things:

1. The MicroStrategy Reporting Suite may only be used for new MicroStrategy projects. Thus, products downloaded as part of the MicroStrategy Reporting Suite may not be substituted for existing products in existing MicroStrategy installations. Also, MicroStrategy Reporting Suite Technical Support Services pricing is not available for Products in existing MicroStrategy installations.
2. Free Reporting Suite is one and only for client who do NOT have a currently valid license. So don't expect to upgrade your older version to v9 for free.
3. Recipient shall hold all Confidential Information received during the term of this Agreement in confidence during the term of this Agreement and for four (4) years thereafter. This so called Confidential Information is nothing but rates at which software is licensed. Big corporates gets too much of discount due to volume. The 4 years is the reason no one ever gets the idea about the rates which it was licensed for someone negotiating best rates to license of MicroStrategy. When I've got a chances with meeting people who decided to buy MicroStrategy licenses, they all told about the way they negotiated but not rates.
4. If you installed with 100 named user product key, you will have to purchase a 100 named user technical support even though your requirement may not be good enough for 25 named user. This is a very big headache for big companies specially.
5. On each Order, MicroStrategy will state the price of Technical Support Services for a period of one year commencing on the date of delivery of the Products. This is about 17% of the product cost (similar in line with SAP).
6. MicroStrategy Intelligence server send the license compliance information to MicroStrategy Server one a day. Today in the ear where each MicroStrategy machine is known by its unique installation activation, it is damn easy for them to find who is infringing on their licenses. I know in past they have gone suing client for non-compliance of licenses for a minor violation. The option to set time when to send license information is available in I-Server configuration. One can work around it by stop letting the machine access internet on which I-Server is installed. I some time really pity on those clients of MicroStrategy.
7. MicroStrategy's Roles based (named user based) licensing strategy is very tricky for clients.
8. MicroStrategy has time bomb within the Software that shall prevent use of the Software. This time bomb can be triggered by them based on the free license serial key. We don't know if these time bombs attached with free licenses. Sound funny. Read the full license agreement.
9. There is not guarantee of free upgrades anywhere. What if MicroStrategy ask for obscene amount for future upgrades.
10. MicroStrategy is notorious for arm twisting hooked customers with rigid pricing. I personally know of many companies who have drifted from MicroStrategy to other BI tools only because of licensing issues, very costly support, making vital "Named User" license exorbitantly costly. Even my current client is looking for shifting some application to other BI tool.

MicroStrategy Basic for Dummies!

5 very informative online videos to learn basics of MicroStrategy. Deserve a dekho.

1. Set Up and Configure the Server
2. Design Attributes and Hierarchies
3. Design Metrics, Filters, and Prompts
4. Add Users
5. Go Live on the Web

Check out all videos here.

There is a Chines proverb - A Picture can say 1000 words
and Indian upgrade - video can say 1000000 words ;-)

Last but not least, you can learn MicroStrategy of you own provided you have it installed and go though the online tutorials religiously. They rock! Real MicroStrategy Education USD 650 a day tutorials for free reading. If anyone know any method to store them offline, please share. Finally MicroStrategy is serious about having more number of MicroStrategy professionals in world. Hope to see the MicroStrategy books on Amazon too, not with USD 650 tag as charged currently by MicroStrategy Education.

Questions for Narrowcast Server Essentials

1. __________________ is responsible for the creation, formatting, transmission and administration of information services.

2. ________________ is responsible for the gathering of user profiles and preferences through a web-based subscription site.

3. Narrowcast Server information transmitters transmit services using _________ and _________.

4. Which of the following are the featured components of Narrowcast server –

1. Narrowcast Administrator
2. Delivery Engine
3. Portal Administrator
4. Subscription Engine
5. Subscription Portal
6. Alerts Engine

Answer Choices

i. a, b, c, d, e

ii. a, b, c, e, f

iii. a, b, d, e, f

iv. a, b, c, d, e, f

5. Match the following –

Module	Function
a. Information Source	i. Connect to local or remote systems such as SMTP, SMS, WAP servers to deliver formatted content
b. Information Transmission	ii. Transforms raw data provided by information source into formatted message, output formats supported being text, HTML and Excel by default
c. Document Formatting	iii. Used to retrieve personalized information from local or remote systems such as Intelligence Server.

1. a à i, b à ii, c à iii
2. a à ii, b à i, c à iii
3. a à iii, b à i, c à ii
4. a à iii, b à ii, c à i

6. A service can have different publications each supporting a different language.
1. True
2. False

7. The three different information to defined for an e-mail (SMTP) content are _____________________, ______________________ and ______________________.

8. ________________________ is used to view the log when a service is run.

9. Which of the following are true?

1. Content information object pulls dynamic data from an information source.
2. Document is a complete formatted object which can have both static content and dynamic content
3. Document can contain only one information object
4. Publication is a collection of documents specifying the content of a service
5. Publications specify information transmission module, locale and devices

Answer Choices

i. Only a, c and d

ii. Only a, b and d

iii. Only a, b, d and e

iv. a, b, c, d and e

10. Which of the following are true?
1. Information transmission module sets the rules for the types of documents supported by a publication
2. Locale specifies regional characteristics like Language, number, date and time formats
3. Device sets the formatting and transmission properties such as cell padding and embedding image support
4. The device specified in the publication MUST match the device in the subscription set for a recipient

Answer Choices

i. Only a, b and d

ii. Only a, b and c

iii. a, b, c and d

iv. Only b, c and d

11. A service must have at least one publication and at least one distribution set (a combination of subscription set(s) and schedule(s)).
1. True
2. False

12. Service Editor does not allow a user to specify multiple publications while Service Wizard allows a user to specify multiple publications.
1. True
2. False

13. ___________________ determines the number of recipients of a service.

14. ______________________ allows the results of a single report to be parsed and personalized for individual subscribers.

1. Personalized Page Execution
2. Personalized Report Execution
3. Personalized Content Execution
4. Both a and b
5. Both b and c
6. a, b and c

15. Which of the following is true?
1. In Personalized report execution, Narrowcast Server gathers separate prompted report result for each group of subscriptions with the same personalization.
2. In Personalized page execution, Narrowcast Server gathers report results from one report execution and applies personalization separately.
3. In Personalized page execution, Narrowcast Server gathers separate prompted report result for each group of subscriptions with the same personalization.
4. In Personalized report execution, Narrowcast Server gathers report results from one report execution and applies personalization separately.

Answer Choices

i. Only a and b

ii. Only c and d

iii. Only a and c

iv. Only b and d

16. In Personalized page execution, preferences are indicated by answering prompts while in personalized report execution, preferences by selecting a specific page of the content.
1. True
2. False

17. Event-based service can be achieved by
1. Having an event table
2. Having a temporary dummy table along with pre/post SQL statements
3. Both a and b
4. None of the above

18. Personalization of dynamic content exists in different forms in Narrowcast Server namely
1. Preferences
2. Authentication
3. Security
4. Local Selection

Answer Choices

i. Only a and c

ii. a, b, c and d

iii. Only a, c and d

iv. Only a, b and d

19. Subscription preference overrides default user preference for a subscription.
1. True
2. False

20. The answers to question objects set at subscription level can be reused but the answers to question objects at user level cannot be reused.
1. True
2. False

21. What is the object that determines the information from the information source a user is allowed to see?

22. What is the object that determines the information from the information from the information source that a user wants to see?

23. User can create a preference object without a question object.
1. True
2. False

24. A security object prompt must be present in both the question object and the information object
1. True
2. False

25. Scenario: Administrator adds a required security object prompt without default answer and additional required prompt object to the question object and information object. A user does not provide his preferences to this question object.
1. Information object executes fine
2. Information object fails since the required prompt objects go unanswered
3. Information object executes and retrieves the complete result set
4. None of the above

1. Delivery Engine
2. Subscription Portal
3. SMTP and SMPP
4. i
5. c
6. a
7. E-mail subject, E-mail body (HTML) and E-mail attachment
8. System Message Viewer
9. iii
10. iii
11. a
12. b
13. Segment Size
14. a
15. i
16. b
17. c
18. ii
19. a
20. b
21. Security object
22. Preference object
23. b
24. a
25. b

Metric Aliases

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.

Free MicroStrategy license

Finally MicroStrategy has woken up the truth and I must say my blog is definitely a contributor for them (MicroStrategy employees form a very good amount of user of this blog, specially the tech support).

One needs to create a MicroStrategy account before downloading. Account creation is 100% free, no need to have purchased MicroStrategy. (If you are a MicroStrategy professional, you can create a free account and access MicroStrategy tech support unlimited. You just need to use official email address. But don't think that you can migrate your account to your new employer)

After having created your account, click here for your free license key.

You can get free license for up-to 100 named user with 1 CPU restriction. Broadly, MicroStrategy have two types of licensing. Named user and CPU based.

Various support options are here.

But don't expect ability to create hi-fi reporting as this is a scaled down version which doesn't include Reporting Services (dashboard), in-memory BI, Multiple Warehouse and many more. To know instantly know what is in and what is out in this free license, have a look at this illustration.

I don't know what MicroStrategy plans by giving a half hearted version. Pretty frustrating. Why don't they think of SME license like SAP or country specific differential licensing. They are just interested in big corps and satisfying the reporting requirements of them. (MicroStrategy future development is mainly concentrated to top 50 clients. They decide and direct the development, thet's what they call or feel the world by beta program) and MicroStrategy, Inc thinks what suites them (beta program users) will suit all.

MicroStrategy is a very good employer and the most of top employees are like full time MicroStrategy veterans, but I think it a time they should get some fresh recruit to get some fresh ideas for a better future of them and mine too. On whole, I pity on this idea of free MicroStrategy because they have removed the juice out of MicroStrategy in free version. IT would take a quite longer time for compnaies to decide going for purchasing an add-on.
-----

I'm not well for last 3 weeks, hence no posts. Not fully recovered. Hope to get well soon and post periodically. So bad that I dozed off in office last Monday ;-)

You most significant role in the current MicroStrategy poll

I've created a LinkedIn poll to know what's the most significant role you are playing in the current MicroStrategy project you are working on. Poll here.

A Good comparision betwwen MicroStrategy and Cognos

Ashish Shah, a friend, has posted comparison between MicroStrategy and Cognos, on my MicroStrategy forum. Have a look.

ScanMD for MicroStrategy 9 launched

even if you don't have version 9 of MicroStrategy, just get it. ScanMD update which has come up after ages (not like frequent HF, SP or GA of MicroStrategy). It is toooooo good, even though I have't used yet. Have a look at the tech-note to downlaod new version.

The biggest problem with ScanMD is it would show you tons of errors even though there is just ONE discrepiency in your metadata. So, weeding out one error is a mammoth task. If you fixed a false error, you schema would go for toss forever and even MicroStrategy can't repair it.

Look at the last screen shot in tech-note which says just 4 errors. My God! Just 4. I have ran ScanMD on fresh metadata and found 100+ error. Don't believe it. Just install a fresh v8 and run the older version of ScanMD and you will see. Good job Engine team.

MicroStrategy 9 doesn't work

Installed MicroStrategy 9 just now only to seee that it doesn't work for some file compitability issue. Screenshot attached. :-(

Will post images when have this error rectified.

MicroStrategy Jobs

Join and Post only MicroStrategy Jobs related news, openings in this group

microstrategyjobs@googlegroups.com

Search by ID v2

So, what’s new?
You can now export direct and recurring dependents of an object. That’s all.

Now for some background…

Direct dependents are the dependents found in MicroStrategy by right clicking on an object and selecting “Search for Dependents…”. The main difference is that Search By ID v2 exports them to an Excel file, whereas Desktop only allows the export to a non user friendly txt file.

Recurring dependents are the objects that use the source object directly or indirectly. If a metric is being used by a prompt and that prompt is being used by a report, then the prompt is a direct dependent and the report is an indirect dependent of the metric. A similar kind of search is offered by the Object Manager, if only as an indirect consequence of its use. Search by ID v2 should be easier to use for this particular feature.

Please be aware that finding and exporting recurring dependents can be a lengthy process.

You can download Search by ID v2 here.

Complex graph in Dashboard creation in WEB

Complex graph like 'combination: Dual Axis - Bar - line' graph cannot be created in Dashboard in WEB.

To achieve the same, create a report in desktop and make as above mentioned graph and save.

In WEB, while creating dashboard, add this data set in dashboard by 'right click' data set and add with formatting option. Now the graph which cannot be done in web, can be done in this way.

Scan MD and MD Doctor

While working on metadata, there are chances that MD may get corrupt. Only reason for corruption of MD is MicroStrategy programmers NOT you (No way), unless you have manually fiddled with the metadata. One small change can messed the metadata forever, some time impossible to recover.

There are two types of discrepancies in MicroStrategy

• Logical
• Physical

ScanMD is used to recover from Logical discrepancies, where as MD Doctor is used for Physical discrepancies. Why these discrepancies creep????? Like any software application, there are bugs in MicroStrategy, dandling code (a situation never programmed), etc. such a thing is the main reason behind discrepancies.

Logical discrepancies: Using desktop you can create/delete several objects. Sometime deletion of the objects doesn’t get reflected in metadata and they get deleted in metadata but still available in front-end. Now such a mis-match in case of schema objects is simply killing, because it won’t let you update schema at all. Imagine not having ability to update schema. Using ScanMD is not an easy task. ScanMD shows 95% for false errors as discrepancies. Engine team of MicroStrategy, Inc. needs to update the tool. For one error, you may have to go through 100+ errors. You can verify the error easily by having project opened side by side. NEVER EVER FIX A FALSE ERROR.

Physical discrepancies: There are not just 10 DSS tables that make metadata, there are several more database objects that are created for metadata; e.g., triggers, views. If any of these objects are messed up, you have a physical discrepancy in your project. I’ve never worked on MD Doctor and look forward some information from you.

This blog is not exhaustive and 100% as I certainly do not believe the bugs in MicroStrategy are only source of discrepancies in metadata.

Sluggish Prompts

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”.

MicroStrategy Interview questions 101 - II

77. Can you update a table in Warehouse using MicroStrategy?

78. attribute role, Security role, security filter and access control ?

79. How will you resolve an issue when (by chance) a aggregate table at higher level has a higher LTS than an aggregate table at lower level? What happens if LTS is high for a hight level table?

80. What are the criteria used by the MicroStrategy SQL Engine to build the join tree of a report's SQL?

81. When a WH Primary key doesn't appear as keys in MicroStrategy table editor OR Can MicroStrategy logical primary keys and the true (warehouse) primary keys differ?

82. Oracle has a pseudo column named ROWID which is not "directly" exposed to frone-end?Can this table be used in MicroStrategy? Why one would do that?

83. Can you enable/disable the thresholds based on some condition/prompt answers?

84. In a Document, there are 5 reports and 3 out of them need to have a prompt so that 20 user can select first day of the week for which user want to execute the report. How many promts would be required to be made?

85. Can you make a floating window in MicroStrategy Web?

86. What's XML Cache?

87. Can you apply themes to MicroStrategy Web? How to create a one?

88. Can MicroStrategy be installed on Solaris or UNIX w/o Root access? Can such a server be (re)started?

89. If you have a Quad core CPU and MSTR license to use CPUS, can you set to use two particular cores dedicately for I-Server (As other core may have been programed for some other dedicated work)

90. What's a System call error? Have you seen Object variable error?

91. Drawbacks on 4GT switch?

92. Can you create Schema objects in 2 tier connection? IS it recomended?

93. What's a Bulk export report?

94. After a bulk export report is run, NCS puts it on an ftp location? Can this location be directly made available in MSTR web?

95. Can you cap the number of reports run at a time by users? What happens when this limit is breached?

96. What's the use of Monitor? What kind of error are captured in it?

97. Have you evre manualy edited the metadata? why?

98. Can you have a pronpt in free form sql report?

99. What will happen when the network connection between the webserver and one of the clustered I-server is lost? Will report run smoothly?

100. Can you priotorize report execution?

101. You have Clustered I-Servers? Can you some how disable Web users to use only one
I-Server?

More to come, but not before a 6 months gap.

MicroStrategy 8.0.x Support Expiration

Reminder: MicroStrategy 8.0.x Support Expiration

Support for MicroStrategy 8.0.x officially expires on April 2, 2009.

According to MSTR: If you are still running this version, upgrade planning and implementation services are available through MicroStrategy Consulting and can be requested through your Account Manager. If you are unable to get upgraded before the version expiration, please contact your Account Manager to determine your options for receiving continued support on the expired version.

Source: MicroStrategy websites

Remove from Grid/Report

You know what they say – you are seldom assigned to a new role, instead new roles are constantly being assigned to you. This is how it came into past, that my job description (being the hospitable chap that it is) was kind enough to make room for yet another topic – second level support.

All in all, this suddenly made my job even more interesting. It is an established fact that no matter the data model’s flawlessness, no matter the excellence of design and no matter the software, be it rain or snow, given enough time and creativity, someone is bound to find an ingenious way of bringing that software to its knees. Thus it is with great confidence that I am promising plenty of new articles on how things can go wrong, and hopefully on how to fix them.

One common problem that I encountered so far is an issue that it’s usually pretty well covered during the first training modules – the difference between “Remove from Report” and “Remove from Grid”. Basically what happens is that users tend to play around with various attributes while creating a report and then neglect to properly remove them from the “Report Objects”.

Having an alien attribute in the “Report Objects” can cause cross joins, and the bad thing about cross joins is that, unless someone has a look at the SQL or the report runs into a spool space error then it is kind of hard to spot. I have seen user created reports that ran with cross joins for years without anyone ever raising an eyebrow…

It would be unfair not to mention that sometimes we need to bring an attribute to the “Report Objects” although we don’t want to see it in the grid, precisely in order to avoid cross joins. How’s that for confusing? Well, let’s just say that in this particular case, the attribute I’m talking about is not an alien attribute, but rather a “connecting” attribute. I can’t think of a suitable example, but feel free to post a comment if you have one.

Know your EPF claim status

I don't know if it really works, but one nice step by EPFO to know about the claim status. Done by Chennai EPFO for its office and now entire India is covered. Pretty cool! Thumps up to EPFO.

(I'm waiting for Know your EPF balance)

Compound Key Attributes

A compound key attribute is an attribute that has two or more IDs. For instance, the attribute City may be defined in the data model as a combination of COUNTRY_ID and CITY_ID.
Quite often these attributes have automatic mapping for one ID and manual mapping for the other one. This design may look strange, but in most cases there are sound rationales behind it.

Now, here is the thing. Let’s say you need to create a new table that should contain the compound key attribute and a new fact. Obviously, you create the table, load it into the Warehouse Catalog, create the new fact and update the schema.
You then create a new metric based on the new fact and attempt to use it in a report, together with the compound key attribute. What happens next is you get an error stating that the new fact does not exist at the attribute level. Staggered, you have a look at the logical view of your new table and see that your attribute and fact are both present, and therefore there should be no call for the error.

Of course, there is a very good reason for the error, and that is because the compound key attribute knows about the new table only through its automatically mapped ID, which is not really enough. So just add the new table to the source table list of the manually mapped ID and it should work.

Common sense suggests that no one would run into such a problem. Nevertheless this belief is based on the idea that all the developers are familiar with each and every compound key attribute in the project, which may not always be true.

A good practice in this case is to maintain a list of attributes that have at least one connection mapping set to manual, and pay special attention to these attributes. Even an attribute with a single ID is liable to cause the same problem if that ID is manually mapped, except that in its case the logical view will not show the attribute at all, thus not fooling you.

Report filter, View filter and Report limit

Comparison of various types of MicroStrategy filters.



1 – Main reason to use Report Limit, else it doesn’t give any performance improvement compared to Report Filter (Metric based) in case of big data set to be SQLed.

SDK - DssType...

If you find yourself writing a program based on MicroStrategy’s SDK, sooner or later you will realize that although huge, the SDK documentation is not nearly sufficient.

Let’s say you are trying to write an IF/CASE statement on object.Type. Most of the time the type is self explanatory, like DssTypeAttribute or DssTypeFilter.

If object.Type = DssTypeMetric Then…

However, there are some objects for which no DssTypeXyz is defined, like Hierarchy or Transformation.
The good news is that it is quite simple to learn how MSTR stores these objects. Just go to C:\Program Files\MicroStrategy\Tutorial Reporting\TUTORIAL_METADATA_7200.mdb and open the table DSSMDOBJINFO.

Now go to the Tutorial environment of MSTR and locate an object whose type you want to learn, i.e. a Transformation. For this example I chose the Transformation “Quarter to Date”, whose Object ID is DB5E65D641D790631D8D7CA48FDB90FE. Simply search for this ID in the OBJECT_ID column of DSSMDOBJINFO and you will find that the OBJECT_TYPE is 43.

If object.Type = 43 Then…

Sometimes it pays to have a look at the SUBTYPE as well. Just to make sure your IF/CASE statement is correct, use the “Filter by Selection” button on the OBJECT_TYPE value that you identified and check if there is more than one subtype available for it.

If object.Type = 43 and object.SubType = 11009 Then…

Please note that there is no actual need for specifying the subtype for the Transformation, as there is only one available. The code above is just for emphasis purposes.

By the way, whatever you do, don’t modify anything in the TUTORIAL_METADATA_7200.mdb file, not if you still want to use the Tutorial environment…

Object Prompt in Report Document

Have you ever checked placing a report with object prompt in report service document? if so, then the object prompt wont appear in document.

Tips: 1) place all the attributes (which present in object prompt) in to the report
2) add as a data set into report service document
3) Drag and drop the report(grid) into the document
4) save and close the document
5) Now, Open the report; remove all the attributes and place the object prompt into the same place (col or row) where attributes are placed. Save and close the report
6) Now, execute the document. No wonder, it will ask you to select the attributes from the list.

Customize the 'Total' in MicroStrategy

How to customize the Total, for those present in the report?

Here is solution:

After placing required attributes and metrics in to report, run the report and once you get data go to Data Menu --> sub Totals --> Advanced --> New -- > Type '#A total' then do the normal total selection (like subtotal by row, etc, etc).

Now, the total appear as ' Attributename Total' for each total.

Some more tips on custom totals:

#A
The name of the attribute under which the subtotal appears.
#P
The name of the attribute to the left of, or above the attribute under which the subtotal appears.
#0
All the forms of the parent element.
#1
The first form of the parent element reading from left to right or from top to bottom.
#2
The second form of the parent element reading from left to right or from top to bottom.
#3
The third form of the parent element reading from left to right or from top to bottom.
#4
The fourth form of the parent element reading from left to right or from top to bottom.

Note: single Quotes to highlight, dont give the same in report :)

MSTR Object Tracking Tool

Dear All,
I have created MSTR Object Tracking Tool (beta) for easy preparation of tracking sheet in a Excel. you can customize by selecting date (creation, modification, from , to date, select number of object types, etc.) I'm looking for some one who like to test the same. Pls let me know if you are interested to to test the same, Thanks.

Email: senthil.raj.u@gmail.com

The depressing story of the renamed fact

The fact SALES_AMOUNT has been enjoying a quiet life for the last five years until, one rainy day, the new QA guy called Eureka! and proudly informed everyone that according to the naming conventions the fact name should be SLS_AMT. Of course, he took the liberty of checking the source tables and discovered that they too should be updated to match the foresaid conventions. Way to go, QA guy!

What do you do? You could swear the QA guy or, if you are more literary inclined you could write him a bitter limerick and post it on the Intranet…
Seriously now, how do you go about accommodating this change? I’ll skip the whole ETL part and go directly to MicroStrategy, right after the source tables were updated.

What you can’t do is go to the Warehouse Catalog and update the source tables’ definition. The catalog would just throw a number of dependencies that need to be solved before performing the update. You can’t delete the fact either, as it is being used by several metrics which in turn are being used in reports, and so forth.

The thing to do is to make the fact independent of any table. There are some ways this can be achieved, and one of them is to clear the fact definition and map it manually to “1”. You can actually write anything in the definition field, as long as it is not “SALES_AMOUNT”.

Thus the fact is no longer related to any of its source tables. Now it’s the time to update the tables’ definition in the Warehouse Catalog.
After that, just go back to the fact editor and map it automatically to “SLS_AMT” and perform a schema update.

A similar process can be applied to attributes. Rather than mapping an attribute element to “1”, you can try to temporarily map it to any other column in any other table. Most likely the relationships with other attributes will be lost because of this, but they can easily be restored when all work is finished.

Is MicroStategy BI implementaion recession proof

This question I had all the time I've been working on MicroStrategy, but with an exception that I had not seen recession.

Prima facie, the answer is Big NO. Most of my friends, who are working on different clients in varied domain, are not working on any important projects or not working at all (including me). All of a sudden the clients have deferred further implementation of MicroStrategy project(s). Around 14 friends of mine have changed job in last 6 months and none of them is actually working on a MicroStrategy project, so one can easily imagine the situation in developed countries, mainly USA.

TARP is yet to come into picture. Scene is going to be same for next 2 months, the time taken for TARP to show effect.

This time clients are simply trying to optimize the current BI not trying any new BI implementation. Few MicroStrategy or other core BI vendors are moving/thing to Open source BI.

MicroStrategy commands a loyal customer base but repeat business or sale of Orion will be something hard to see in near future.

Cease and Desist Letter received from MicroStrategy, Inc

I've just received a Cease & Desist letter for having registering domain names "microstrategy.co.in" and "microstrategy.in". The reason behind this is

1. Using companies trademark, illegally.
2. Domain names made in bad faith since you are not using domain names for any legitimate purpose.
3. abusive registration of domain names

Pretty much pissed off against MicroStrategy, Inc as I love the company for which I've worked (though outsourcer) and I receive this kind of letter.

microstrategy.in directs to microstrategy.co.in which is a MicroSteategy forum.

Can't comment legally, but I do not use anything that can be trademark violation. Site is fully operational and happening so nothing can be bad taste and I need to know what's an abusive registration of domain name.

If you have any legal/para-legal advice to let me know.

If they would have had asked politely or tried to contact though common acquaintances, I would have had given to them. But this D&C is torturous. Nothing is illegal on site or site itself is not illegal. I would contest the domain name ownership in Indian court under Indian IT Act 2000.
---------------------

Update 9th Feb 6:30 pm IST

Indian IT Act 2000 doesn't define anything like cybersquatter. Such case can be only fought in court of law or can be settled by Arbitrator of .IN Registry (which still can be challenged in court, extremely tedious and slow process).

One can check the list of domain that were awarded to the Complainant here. I've been though most of them and found that the Registrars were indeed cybersqatters and it was pretty easy for rightful owners to get back the domain (this includes monster.in, pizzahut.in, sonyericsson.co.in, gmail.co.in, morganstanley.in, youtube.in, kfc.co.in, franklintempleton.in).

If one reads the case document on the link above, one would come to know that there were people who had simply parked the domain or had opened shell/ad site not hosting anything actually. But I have a strong case and I would be fighting tooth and nail for the hard work I've done in setting up the forum and bringing some good people knowing MicroStrategy.

Getting vehicle NOC (No objection certificate)

During my employment I've worked in 3 different states and need less to say that I have always relied on my bike to commut to office. We all know the fear of being caught by traffic constable/inspector in the other state. But here is way, getting an No Objection Certificate (NOC) for your current registering RTO.

To safely (w/o being harrssed by cops) drive/ride vehicle registered in a different state, there are two method.

1. Carry fuel bills of a petrol pump located in your registering state. An ideal difference between two bills is 2 months. When traffic cops aks your bribe for outside vehicle first things to say "I can legally travel my bike fora year in another state without any NOC". I've said that got away, once I was caught. But even if he insits than show him the bills. He won't tough you.
(BTW, I was stopped 3 times by traffic cops and everytime got away but one thing was : all these cops noted down my registration number.) Those who don't know they actually report an outside vehicle to the head office so as to catch him after one year no-NOC grace period is over.

2. This is legal but tedious way, specially when you have a vehicle on loan. The cost of NOC is zero. yes. There is no fees and Police report is required if your changing the "state". After NOC you can apply for refund of tax (Form DT). In new state you need to file form No 33 for change of address to be recorded in the Certificate of Registration (RC). Once again, process is a bit tedious if there a loan. Agent cost is Rs 800 for NOC. One should forget refund of tax, as officers will make your run from piller to post (in case of intet-state transfer).

I would say, one should go for #1, a very simple and extremely cost effective way of living in different state. #2 for a car costs around 25 thousand (and for bike like 10K) and RTO can make your n number of rounds to office.

Happy driving/riding.

Multiple Intelligent Server on single machine

A common question asked (and twisted) and newbie are trapped if answered incorrect. :-)

Answer is Yes in case of All UNIX/Linux (AIX, Solaris, UX, RH Linux, Suse Linux) and NO for Windows.

One thing to be taken care of. Can't run both servers on same port number. :-)

Automatic mapping

Things aren’t always what they seem...

Automatic mapping is a great thing. It enables us to stop worrying about updating an attribute or a fact whenever we bring modifications to the Warehouse Catalog.

Well, my advice is to don’t rely too much on it. The following pictures show two instances of the “Customer” attribute and both of them appear to have CUSTOMER_ID automatically mapped.

However, one of them lacks the ORDER_FACT table, and that is because I mischievously removed it by clicking Modify and then unchecking it from the source tables list.
This ID’s mapping is still automatic, in that that it will use any new table that contains CUSTOMER_ID and it will remove from its definition any table that cease to contain this column. But as far as the table ORDER_FACT is concerned, it will not be used by the attribute until it is checked again in the source table list.

So, when debugging reports which don’t run because a fact appears not to be available at a certain attribute level, do yourself a favor and check if that particular attribute suffers from what I have described above.

In home town

Left FIC and now working at a new company at Mumbai. Very busy with shifting and setting up the life. No time for blogging or even checking important emails or replies. I shall be back on scene by Month end.
P.S. Wish you all a happy new year. (Couldn't personally reply to mails received)

Getting Started

Many a newbie MicroStrategy learning fellow gets a little bit confused when confronted with concepts such as attributes, facts, metrics and so on.

The next sketch is a little something that I use to help them map the new notions to their existing SQL knowledge:

SELECT
a11.country_id,
a11.country_desc,
SUM(a12.sales_amt) sales
FROM
TABLE_COUNTRY a11,
TABLE_SALES a12
WHERE
a11.country_id = a12.country_id
AND
a11.country_id = “15”
GROUP BY
a11.country_id
a11.country_desc

Marked blue you can see the “Country” attribute and its two fields – country_id and country_desc.

Green stands for the “Sales” metric, which is no more than a SUM of the “sales_amt” fact, depicted here in bold italic green.

In psychedelic pink you have the metric alias, as specified in the “Column Name used in table SQL creation” field from the “Metric Column Alias Options” menu.

The tables used by the report are shown here in red.

Lastly, I reserved the wonderful orange for the filter. Note that this filter may exist in the report as a standalone filter or as a result from a prompted filter. Either way, the SQL should look the same.

Compound Metrics and Data Type

All right, so this doesn’t happen too often, but when it does happen… it hurts.

Let’s say you are using a compound metric, something like (([Metric 1] / [Metric 2]) * 100) and the VLDB settings for the report are such that the SQL Engine uses intermediary tables.

What this means is that somewhere in the SQL there will be a “CREATE TABLE…” syntax and that your compound metric’s data type will be declared in it. Now, the million dollar question is - what will be the data type of your compound metric? Surely the SQL Engine will use the settings entered in the Values Formatting Properties window. Well, not really… Those settings are only used by the Analytical Engine for formatting the reports.

In fact, the data type of your compound metric will be the data type of the first metric in the formula – in this case, [Metric 1]. This is also the case when you are using a formula like ApplyAgg("sum(case when #1 = 1 THEN 1.0 ELSE #0 END)", [Fact 1], [Fact 2]). The data type will be that of [Fact 1], because it is the first one that is being referred. This is actually the best way of writing such a formula, as it ensures that the data type will be that of the result fact instead that of the condition fact.

Why is this so important? Think of what happens if [Metric 2] has a greater number of decimals than [Metric 1]. Most likely you will get one of those pesky red colored errors and the report will stop running.

Fortunately there is a simple way of avoiding this. Just set the data type of your compound metric to Float, with a hefty bit length of 48.

In the Metric Editor go to Tools -> Advanced Settings -> Metric Column Options: