Category Archives: Bryan Brandow

Bryan Brandow: Triggering Cubes & Extracts using Tableau or MicroStrategy

trigger-720x340

bryan-headshots-004Bryan Brandow (photo, right), a Data Engineering Manager for a large social media company, is one of my favorite bloggers out their in regards to thought leadership and digging deep into the technical aspects of Tableau and MicroStrategy. Bryan just blogged about triggering cubes and extracts on his blog. Here is a brief synopsis.

One of the functions that never seems to be included in BI tools is an easy way to kick off an application cache job once your ETL is finished. MicroStrategy’s Cubes and Tableau’s Extracts both rely on manual or time based refresh schedules, but this leaves you in a position where your data will land in the database and you’ll either have a large gap before the dashboard is updated or you’ll be refreshing constantly and wasting lots of system resources. They both come with command line tools for kicking off a refresh, but then it’s up to you to figure out how to link your ETL jobs to call these commands. What follows is a solution that works in my environment and will probably work for yours as well. There are of course a lot of ways for your ETL tool to tell your BI tool that it’s time to refresh a cache, but this is my take on it. You won’t find a download-and-install software package here since everyone’s environment is different, but you will find ample blueprints and examples for how to build your own for your platform and for whatever BI tool you use (from what I’ve observed, this setup is fairly common). Trigger was first demoed at the Tableau Conference 2014. You can jump to the Trigger demo here.

I recommend you click on the link above and give his blog post a full read. It is well worth it.

Best regards,

Michael

Bryan’s BI Blog: MicroStrategy vs Tableau

Readers:

Bryan BrandowBryan Brandow, has posted his second new post on his new blog, Bryan’s BI Blog and it is a doozy. Bryan does an in-depth comparison of MicroStrategy vs. Tableau.

Here is a link to the MicroStrategy vs. Tableau post.

Best Regards,

Michael

 

A Must Read: Bryan Brandow’s BI Blog on Evolving BI

Readers:

I have some good news to share with you.

Bryan BrandowBryan Brandow, a Data Engineering Manager, has started (or perhaps restarted) blogging again. His new blog is called Bryan’s BI Blog and the first post is a must read. It is titled Evolving BI and Bryan shares his latest adventures, his thoughts on the current state of BI as he has observed and experienced it, and where he feels we collectively need to go.

Bryan has also migrated all of his old MicroStrategy Blog posts to this blog site and I encourage you to read these as well.

Here is a link to the Evolving BI post.

Best Regards,

Michael

 

Bryan Redux #2: Removing Sections of a Report via URL API

Readers:

Here is another blog re-post from my friend, Bryan Brandow’s MicroStrategy site.

If you want to directly visit Bryan old site, the URL is http://www.bryanbrandow.com.

Best Regards,

Michael

Bryan originally posted this on July 22, 2011.

Removing Sections of a Report via URL API

WhBryan Brandowile working with the Web SDK to try to make a small customization, I stumbled on a pretty useful set of URL API codes that allow you to quickly modify the Report Page by removing various elements.  While such customizations are common, I wasn’t aware that they were available out of the box.  I would think that these would be very useful in doing simple linking to reports via an IFrame from another application, such as a Portal.

The trick to get the URL for a Report by right clicking it in Web, choosing Properties, and then copying the Link at the bottom.  Now, locate the src section of the URL:

ASP: &src=Main.aspx.4001 

JSP: &src=mstrweb.4001

and add in one of the transforms just before the .4001.

Available transforms that I’ve found:

reportNoHeader

reportNoHeaderNoFooter

reportNoHeaderNoFooterNoPath

reportNoHeaderNoFooterNoPathNoToolbar

Example:

ASP: &src=Main.aspx.reportNoHeader.4001

JSP: &src=mstrweb.reportNoHeader.4001

Unfortunately, these are actual transforms and not flags, so you can’t mix and match to fit your needs (for example, if you only want to hide the toolbar, reportNoToolbar will NOT work).  Those are the only 4 that you can use that I’ve found, but they may be handy in a pinch and best of all, not require customization work to use.

Update:

Another method of doing this that does let you pick and choose and supports documents as well:

&hiddenSections=header,footer,path,dockTop

Place that code in the URL, for example (Note: I have shown this on multiple lines. You should paste this in as one long line with no line breaks):

http://webserver/Microstrategy/asp/Main.aspx?Server=ISERVER&Project=PROJECT&Port=0&evt=2048001
&hiddenSections=header,footer,path,
dockTop&src=Main.aspx.2048001
&visMode=0&documentID=04AACFE445AD69198676C8AD56245118
&currentViewMedia=2

List of options (case-sensitive):

  • header
  • path
  • dockTop
  • dockLeft
  • dockRight
  • error
  • content
  • dockBottom
  • footer

Bryan’s Blog Entry Link:  http://www.bryanbrandow.com/2011/07/removing-sections-of-report-via-url-api.html

Bryan Redux #1: Left Joins in MicroStrategy

Readers:

I am occasionally going to re-blog posts from my friend, Bryan Brandow’s MicroStrategy site.

I consider Bryan one of the best in the business, but his passions lie in other areas these days.

I will denote these blogs by beginning them with “Bryan Redux.” If you want to visit Bryan old site, the URL is http://www.bryanbrandow.com.

Best Regards,

Michael

Bryan posted this on Tuesday, March 22, 2011.

Left Joins in MicroStrategy

Bryan BrandowAn interesting stance by MicroStrategy is that they really push you for proper warehouse modeling (or at least what they consider proper).  At the same time, the tool’s flexibility can really handle just about any model, and I’ve seen the SQL Engine come through in some amazing scenarios where other vendors laughed and walked out.  One commonly requested feature is the ability to left join two tables in a report.  That is, left join Dimension to Fact, not left joining multiple passes.  There are plenty of valid reasons you would need this feature, and for many years I would joke “MicroStrategy can do everything .. except Left Joins”.  Imagine my surprise when I discovered an extremely buried feature that does enable left joins!  I stumbled on this a several months ago and have no idea if it’s been there all along or was introduced recently.  Based on forum and friend activity, not many other people are aware of it either.  Today, I’ll show you the secret.

Build a normal report with Attribute1, Attribute2 and a Metric.  The SQL will come out like this:

select a12.Attribute1  Attribute1, a13.Attribute2  Attribute2, sum(a11.Fact)  Metric

from FactTable a11 join DimAttribute1 a12

on (a11.Attribute1Key = a12.Attribute1Key)

join DimAttribute2 a13   on (a11.Attribute2Key = a13.Attribute2Key)

group by a12.Attribute1, a13.Attribute2

But let’s say that you need to left join DimAttribute2 to FactTable.  Simply follow these steps:

Step 1: Edit the Attribute

  1. In the attribute editor, go to Tools -> VLDB Properties.
  2. Change the property Joins -> Preserve all final pass result elements to the third option, Preserve all elements of final pass result table with respect to lookup table but not relationship table.
  3. Update Schema.

Step 2: Edit the Report

  1. In the report editor, go to Data -> VLDB Properties.
  2. Change the property Joins -> Preserve all final pass result elements to the fourth option, Do not listen to per report level setting, preserve elements of the final pass according to the setting at the attribute level.

With those two options combined, the resulting report now generates this SQL:

select a12.Attribute1  Attribute1, a13.Attribute2  Attribute2, sum(a11.Fact)  Metric

from FactTable a11 join DimAttribute1 a12

on (a11.Attribute1Key = a12.Attribute1Key) left outer join DimAttribute2 a13 

on (a11.Attribute2Key = a13.Attribute2Key)

group by a12.Attribute1, a13.Attribute2

Conclusion Note that since you need to turn on a report level setting, changing the attribute won’t modify your entire system.  This is nice because you can choose to let some reports to left join on that attribute while not others.  One side effect I have experienced is that this attribute is no longer eligible for Intelligent Cubes. If you can live with that, this becomes a pretty handy trick.

Bryan’s Blog Entry Link:  http://www.bryanbrandow.com/2011/03/left-joins-in-microstrategy.html

Welcome – Part 2

MichaelWelcome to my MicroStrategy Tips & Tricks blog. I have been kicking around the idea of creating a MicroStrategy-specific blog for a few years now. With Bryan Brandow not being as prolific these days with his excellent MicroStrategy blog, http://www.bryanbrandow.com, I thought I would make an attempt to try to fill that void (personally, I think Bryan is not replaceable).

I will try to focus mostly on providing interesting and useful tips and tricks related to the entire MicroStrategy platform. That is soup to nuts or Intelligence Server to Mobile, so to speak.

Right now, my main areas of main interest are in developing interactive, dynamic reports for Web and Mobile (most notably iPad). I have a strong passion for data visualization and have a sister site you can visit at http://www.datavizblog.com.

My company is called Data Archaeology, but I am not currently actively seeing business or employment. I am working a fairly long-term contract in retail and hope I will be able to continue to do so as time progresses.

I have decided not to allow sponsorship of my site so I can be objective about MicroStrategy as well as all the third-party vendors who provide goods and services related to MicroStrategy. You will not see me beating up on the MicroStrategy platform or any of its executive leadership (well, maybe some scolding). I have met Michael Saylor, Founder and CEO of MicroStrategy, many times over the years and I agree with him sometimes and sometimes I don’t. But it is his company and vision and I need to respect that. Michael is very focused on Mobile and Security right now, but I will gently remind him here that a lot of us are still doing enterprise reporting with MicroStrategy and to not take his eye of that ball too.

I have met Paul Zolfaghari,  President of MicroStrategy, several times and think he is perfect for that position. Paul is affable, accessible, and truly wants his customers to be happy.

I am a Principal MicroStrategy Consultant with Data Archaeology (consider this blog and my data visualization blog my Web Data Archaeology, Inc.site). My mantra that I  preach to everyone (or anyone who will listen) is to get our business partners or clients excited about their data. Now the way to do that could come in many different forms: great data visualization, self-service BI, data on multiple devices, properly organized data that is easy to access, ask questions, and provide actionable insights.

If you want to contact me directly, please drop me an e-mail at michael@dataarchaeology.net.

For you folks who will be at MicroStrategy World this week, I will be there Wednesday and Thursday. See if you can match my avatar to my face and stop me and say “Hi!”

Thanks for visiting and I hope to see you often.

Best regards,

Michael

Welcome!

Readers:

I have been kicking around the idea for this blog for several years now. Last year, I started my data visualization blog and find it very fulfilling. However, I have wanted a blog focused on and devoted to the MicroStrategy platform. In the past, I have relied on Bryan Brandow’s excellent MicroStrategy blog, but I believe with Bryan’s work commitments and other things, he has been too busy to blog. I can only hope to produce the quality of blogs that Bryan did.

 

Follow

Get every new post delivered to your Inbox.

Join 119 other followers