MicroStrategy Best Practises – part 1


Deprecated: Function get_magic_quotes_gpc() is deprecated in /home1/microoat/public_html/blog/wp-includes/formatting.php on line 3964
Architect, Database, Education

First installment of best practices for MicroStrategy focusing on data warehouse. Thumb rule of best BI implementation,n not just MicroStrategy, is the optimized for setting up the data warehouse correctly. Here are few from me, few from MicroStrategy tech notes lost in deep. I shall post the best practices for MicroStrategy soon.

1. Data warehouse design should be snowflake schema.

2. Don’t give direct access to tables. Create view and provide access to them. Don’t create custom views.

3. Login/Password used for creation of DNS used to configure the MicroStrategy should be secret. DBA must not tell them to anyone and should be present to input L/P when MSTR Admin is configuring the I-Server.

4. Unique key of table should not be dependent on another other column of same table irrespective of such a column part of unique key to not.

5. Everyone of MicroStrategy team must have individual access to an interface to run SQL queries against the DEV WH with create access.

6. Everyone of MicroStrategy team must know the PK, FK and composite key of each table in OLTP (source) system.

7. All leading and training white spaces in table cells must be cleansed. Using SQL in MSTR (for same) is not a great idea. White spaces decrease SQL speed and it is visible for large warehouses.

8. Only fact tables should be partitioned.

9. To keep the ETL simple, you might want to build your re-normalized structure first, and afterward “generate” the higher level look-ups from the lowest-level look-up table.

Look forward for addition and criticism for above.

Late Edit: Every statement above has a blog post to justify. [:)]

IBM India’s Cognostrategy


Deprecated: Function get_magic_quotes_gpc() is deprecated in /home1/microoat/public_html/blog/wp-includes/formatting.php on line 3964
3rd Party, MicroStrategy

This is some what’s insider information, but may be something that MicroStrategy re-sellers should be knowing or should find out in due course of time. This is specific to India. MicroStrategy can not compete any BI tool to a the most price sensitive market of world, India. Even free is not something that people would easily go for, due to extremely low awareness about MicroStrategy BI in India. Sales is India doesn’t really affect much to MicroStrategy in the sense, it is not directly involved in selling the product here but only and only through authorized resellers, that to with a division of region of sales among them. But so far from last 7 years, sales of MicroStrategy is dismal. An recent consolidation in BI landscape has made things very tough for re-sellers, though I fail to understand why any damn company would go for Cognos/BO, even though they can’t beat MicroStrategy in any aspect, but price.

But how much the high price is hurting to adaption of MicroStrategy, I came to know while speaking with a friend only last week. First, MicroStrategy has around 30 clients in India, one of them having HQ right across my apartment. 😎 . Low penetration is mainly because of selective marketing by resellers but MicroStrategy, Inc has tried their hands. Now IBM is exploiting the high license cost of MicroStrategy in sales like anything. These guys (sales representative of Cognos) admit in front of client that MicroStrategy is highly scalable and better analytic capable than Cognos, but why to pay such a high price when you can have Cognos for BI and SAS for analytic, and is working great for them (IBM Cognos). Seriously a high price paid by MicroStrategy.

MicroStrategy really can’t do much about this evil marketing. Though I must say having two different tools would increase the cost of “experienced professionals” to handle them. Something that MicroStrategy reseller can counter argue. But after having lost Netizza, SPSS, Unica, etc. it is going to a tough market for them. also new kids on block like TableU are also giving them tough challenge in department BI sales, not here in India though.

All I can advice MicroStrategy is that you are the best BI. You can be the best BI, winning all accolades for the blah blah BI report, Nigel etc, done by independent authorities, but your scalability, features would be still used by least %age in industry due to very high cost of license and high cost of processionals (and tough search of them too). Just bring down your prices and see yourself sweeping the market.

Critical part of this blog is based on discussion with person who taught me MicroStrategy.

Connect with MicroStrategy 101


Deprecated: Function get_magic_quotes_gpc() is deprecated in /home1/microoat/public_html/blog/wp-includes/formatting.php on line 3964
MicroStrategy

Best way to connect with bloggers on MicroStrategy 101 would be registration. Rest assured, you are assured 101% privacy. You will not get any spam from me and your information is safe. Currently there are more than 150 registered users. :) To solve your issues/share your ideas you can join the forum too. Unfortunately, currently blog and forum registration are not in sync, and you would need to register separately. I’m working on its integration.

But you can find us on lots of other social networking sites too as follows:

MicroStrategy at linkedin MicroStrategy at twitter MicroStrategy 101 at facebook

Various types of Tables in MicroStrategy


Deprecated: Function get_magic_quotes_gpc() is deprecated in /home1/microoat/public_html/blog/wp-includes/formatting.php on line 3964
Architect

Most of you would be working on it w/o knowledge. Pretty nifty.

  1. Base table: Provides source data for metrics. In a given SQL pass, the base table(s) may be fact tables or previously calculated intermediate tables.
  2. Lookup table: Provides attribute IDs and descriptions. A table functions as a lookup table if its columns appear in the SELECT, WHERE or GROUP BY clauses.
  3. Relationship table: Includes at least two attribute ID columns to join attributes. A lookup table can serve as a relationship table, or it could serve as both a lookup and a relationship in the same query.
    A table functions solely as a relationship table if it appears only in the FROM clause.
    A transformation table is a special case of relationship table.
  4. Partition mapping tables: MicroStrategy warehouse and metadata partition mapping use pre-queries to determine the physical tables to use for a particular metric. Warehouse partition mapping depends on physical tables in the warehouse, which will appear at the top of the FROM clause in the pre-query.

In Star schema, there is virtually no difference between Base Table and Lookup table.

Please let me know if there is any other type of MicroStrategy table.

GIS tools for MicroStrategy


Deprecated: Function get_magic_quotes_gpc() is deprecated in /home1/microoat/public_html/blog/wp-includes/formatting.php on line 3964
3rd Party

This was probably long pending, but I had first hand information only about MapInfo, now called Business Insight (such a boring name) and Visual Crossing. Among MapInfo and VisualCrossing, former is lot cheaper. My ex-employer or rather client, LoanPerformance, have used both for trail and finally settled for Visual Crossing, due to the depth of GIS information, as they wanted to show the data at MSA level not just District or pointers for city. This was obvious for a company dealing into selling best and vast history of the real estate prices in USA. If you need to show the data at a higher level, MapInfo should suffice. FYI, VisualCrossing has been co-founded by MicroStrategy alumni, who has worked in MicroStrategy SDK for a decade and its integration is extremely seamless. With these tools you need to have a GIS Server that would have geo-coding information. Integration of VS after installation takes just 5 minutes and you are good to change all geography grids to maps. AFAIK, VC supports only USA and Canada where as MI have much larger support in terms of countries.

With permission from Ehsan Hoque:

  1. Visual Crossing was created by Andrew Wiggemore. He also created the MicroStrategy SDK for web. So as you can imagine, his stuff has the tightest integration with the product and hence the most rapid time to deploy.
  2. Google/Yahoo/Microsoft integration. I have done this for a few clients including a POC with Bank of America, when I was there. While the onus of maintaining the data is on Google or others, you pay them per click or per tile depending on the pricing scheme you sign up for. Now if your website is public (which I cannot imagine it is) then you do not pay them but you are limited to 50K transactions.
  3. A little known solution is Oracle Spatial. If you have Oracle, then you have Oracle Spatial. The API is very much AJAX and you can replicate Google Maps like functionality. Dan Abougov (I think I spelt the name right) at Garmin (now Nokia) will sell you all the maps and spatial data you need. The advantage of this and #1 is that you do not need a round trip to an external entity.

From Massimiliano Parcaroli

Maps4Strategy. Until now the maps are only available for Italy, but soon we will implement Europe and USA Maps. We introduce this product on November 2009 at the National Symposium Microstrategy in Milan Italy.
If you feel interested, watch the video to understand the simplicity of use and the perfect integration whit MicroStrategy Platform.
http://www.giano-solutions.com/maps4mstr/

This arguments, brings us to the conclusion that not just the tool but the depth of GIS data sources is equally important, but this depth may or may not be concern of every client. Please share your experience of using GIS tools and source data.