Backed by popular demand, here is a more detailed segmentation tutorial / real world example.  The main application of this technique is for cases where you are narrowcasting very large files.  It is not limited to flash dashboards but can also be applied to excel and pdf services, and email and file publications.

The Situation

In our example scenario the client wanted to deliver flash dashboards to sales teams personalized to limit customer data to that specific team.  The dashboards would be written to file where each team member can access it from a web portal.  To achieve this we designed a generic dashboard with the Team attribute as the group by and created a personalized page execution service (basically this creates one dashboard per team).  However, because of the large volume of customer details, they found that 90% of the processing time was spent within narrowcast.  Even after horizontally scaling to a total of 3 execution engine much performance gain was not realized and service failures were rampant.

In this situation the only out is to redesign the dashboard and take out the details or to apply segmentation to render the dashboard into smaller chunks.  The former was not even an option as we had already redesigned the dashboard and reduced the data content as much as would be palatable to the business.  Now, it is key to remember that segmentation would mean multiple executions against the warehouse of the same query filtered by the segmentation boundaries. In the original design one warehouse execution would yield 60-80 files depending on the region (we had different dashboards for different regions i.e. emea,apla,na).  After studying the situation we decided that the stability and performance gain with segmentation far out weighed the costs of multiple warehouse executions.  For more on when to use segmentation read this post.

Segmentation Report and Segmenting Logic

When using personalized page execution the segmentation attribute is almost always going to be the Page By or Group By attribute.  In this case the Team attribute is the obvious candidate.  Its critically important that the segmentation attribute have an integer id form.  Unfortunately, the Team id form was made up of alpha numeric characters which we worked around by converting the column to its ascii equivalent and casted it as an integer type.

Now for the segmentation logic we decided to base it of the number of customers per team.  Remember that that the dashboard had customer level details for each team making it the highest cardinal data point.  The first step is to create a report with Team and Customer count as shown below:  *All images show only partial results*

As you can see each team has quite a bit of customers.  The total customer count was roughly 40k and we decided to break up the report into 10 segments with each segment processing about 4,000 customers.  NTiles to the rescue!  It’s a built in MicroStrategy function that segments data based on a metric.  However, because it works on bucketing each discreet data point in a non sorted fashion you cant apply it directly on the Customer Count metric.  As further clarification, the screen shot below shows NTile function applied to Customer Count with the formula NTileValueSize([Customers Count], 500):

In this example I used 500 which means buckets (segments) will be created in ranges of 500.  For example NTile 1 is roughly 0 to 500 and Ntile 2 is roughly 501 to 1000 and so on.  However, this wont work for our needs because the tiles dont allow us to bound ranges of Team Id’s.  As you can see the buckets are created in an unsorted fashion and it doesnt take into account the total number of customers in a segment.  As a result we cant bind ranges of Teams.

To deal with this we built a metric that does a running sum over customer count sorted by the Team attribute.  Essentially this gives order and magnitude to the NTile metric.  With that we defined the new NTile metric as NTileValueSize([NTiles of Customer Count Running Sum], 4000).  Segment value was increased to 4000 as the running sum changed the magnitude as seen below.

We now have sequential segments created for every 4000 customers ordered by Team Id.  We are not done yet.  The segmentation report should only contain  Team Id’s designating the beginning of each segment.  As shown above the NTile value is the same for all Team Id’s belonging to the same segment changing at segment boundaries.  To limit report result to segment start Team Ids, we created a moving sum metric that sums the current NTile value and the previous NTIle value and divdes the result by the current NTile value.  When ever a boundary is reached the value will always be less than 2.  In the example above the first boundary change occurs at Team Id 68694851 and the boundary metric is calculated as 2+1 / 2 which equals 1.5.  See the boundary change screen shot below:

Each boundary above designates the start of a segment.  In order to complete the report we must create a report limit that includes values less than 2, in other words, it should only include segment start Team Ids.  In our example the result looks like the following:

We ended up with about 12 segments when using a value size of 4000.  As an example the first segment consist of Team Ids greater than or equal to 65694849 and less than 68694851 while the last segment includes all team ids greater than or equal to 84824850.

If you made it this far congratulations, the hard part is over!  Before we go further we need to create a value prompt to capture the NTile value size.  This prompt will replace the 4000 number in our example above.  We do this because narrowcast will pass the segment size set in the service to the report during runtime.  By doing that it allows us to easily tune the segment size from inside narrowcast.  Therefore, the metric NTiles of Customer Count Running Sum was redefined as NTileValueSize([Customer Count Running Sum], ?NTileSizeValuePrompt).

Dashboard and Report Setup

This is actually pretty easy.  First create two value prompts one designated as Segment Begin and the other as Segment End.  Then create a filter with two conditions such as Team Id Greater Than or Equal to Segment Begin and Team Id Less Than Segment End.  To simplify report sql we created a report as filter with Team and the previously created filter.  This report was added to the report filter definition of every Team related dataset of the dashboard.

Next we created a subscription report that simply had Team in the page by.  This report can vary depending on the situation but in our case the personalization attribute and subscription attribute were the same. Furthermore, the segmentation attribute and subscription attribute must be the same for segmentation to work.

Told you this part is easy.

Narrowcast Setup

You must create an Information Source Module (ISM) where the Subscription ID is the same as your segmentation attribute ID.  In our example we created a new ISM where Team ID was designated as the Subscription ID.  Next you need to go through the normal process of creating a service.  When you arrive at the point of defining the document object you should enter the advanced properties and choose to segment based on segmentation prompts then select the appropriate prompts.  I wont go into the complete details of creating the narrowcast portions as they are pretty well documented in the MicroStrategy knowledge base.

Finally, in our example we set the segment size to 4000.  You can do that from the service editor by going to the general properties tab.  Remember this value will automatically be passed to the NTile metric.  At this point we played around with different segment sizes until we reached a satisfactory point of performance, stability, and scalability.

Wheew! That was a long post.  Hope this is helpful.

ajo NarrowCast segmentation