Features of Narrowcast Server that do not Exist in Distribution Services

Features of Narrowcast Server that do not Exist in Distribution Services

Delivering a report to an email, shared drive or file server has always been one of the key requirements for Microstrategy business users. In earlier versions of MicroStrategy, Narrowcast component was the only option provided by MicroStrategy to deliver the reports. Narrowcast component is not completely integrated with MicroStrategy intelligent server. Narrowcast component should be installed and configured separately. Starting from MicroStrategy 9.x onwards, MicroStrategy introduced a new component for deliveries called ‘Distribution Services’ which is completely integrated with i-Server architecture.
MicroStrategy discontinued selling Narrowcast server to new customers and encourages existing customers to move as much delivery requirements as possible to Distribution Services, which includes several new features that do not exist in Narrowcast Server. However, existing customers continue using Narrowcast server in some cases because of the features that still do not exist in Distribution services Versions 9.4.x and 10. This blog details Key features of Narrowcast Services that are not yet available in Distribution Services.

Distribution Service & Narrowcast Architecture

Email/File Subscriptions Used Cases:

Used Case	Service Deliveries	Distribution Service Deliveries
Custom excel template to hold Multi-document	Custom excel template can be used in Narrowcast to export multiple documents/reports from various projects to custom excel template. Related Tech Note: TN18139: How to make new Excel templates with more than 10 pages in MicroStrategy Narrowcast server	This functionality is not available in Distribution services 9.4.x and 10.
Pre- and Post-service execution SQL	The Pre and Post Structured Query Language (SQL) statements are stored in the Subscription Set definition. This feature is useful for modifying or checking certain criteria that relates to the service execution to recipients. Related Tech Note: TN8972: What is the Pre- and Post-service execution SQL in MicroStrategy Narrowcast Server 8.x?	This functionality is not available in Distribution services 9.4.x and 10.
Order of Execution Related Tech Note: TN45393: Is it possible to control the order of execution for multiple subscriptions tied to the same schedule using Distribution Services in MicroStrategy 9.x?	Subscriptions tied to the same schedule can be prioritized using the service priority option	The subscriptions are processed in the order they are received from the metadata in DS 9.4.x and 10
Active/Inactive Subscriptions	Single subscriptions can be activated or inactivated.	Subscriptions can only be inactivated at the project level in metadata in DS 9.4.x and 10.The Subscription monitor has an “Unsubscribe” button, but that deletes the subscription.
Larger subscription base	For large number of subscriptions and alerts Narrowcast server is a better option, since it is independent of Intelligence server and does not increase the server load. Proper usage of options like Segment size, Execution timeout and work allocation settings give control to stabilize and improve performance.	Distribution services reside on the Intelligence server, hence large subscription base could decrease the performance of the overall Microstrategy tool. There are i-server options that can increase the performance of the Tool, but they are integrated with i-server.
Subscription List(Recipient List)	Subscription Lists can be used for multiple Services or subscriptions.	Individual users have to be selected for each subscription in DS 9.4.x and 10. As a workaround Email DLs can be created.
FTP	File Information transmitters can be set up for saving files to networked computers and remote servers using the Windows file system	FTP exists only for Bulk export in Microstrategy 9.4.x. MicroStrategy 10 provides the ability to schedule deliveries to FTP servers.
Deliver files to Microsoft SharePoint web folder	Ability to Deliver the report to SharePoint path through MicroStrategy Narrowcast 9.x. Related Tech Note: TN20390: How to deliver files to a Microsoft SharePoint web folder through MicroStrategy Narrowcast 9.x	This feature works when MicroStrategy Intelligence Server is on a Windows machine, but does not work when Intelligence Server in Unix/Linux platform. Related Tech Note: TN42796: Is it possible to delivery files to SharePoint folder via Distribution Services in MicroStrategy Intelligence Server 9.2.x and 9.3.0

Administration Used Cases:

Used Case	Narrowcast Service Deliveries	Distribution Service Deliveries
Bucketing	Bucketing is a concept of grouping all recipients who will receive the same content in one email. This feature will allow a recipient to reply-to-all users that received the original email. Related Tech Note: TN41340: What is the bucketing feature and how does MicroStrategy 9.x use this feature?	MicroStrategy Distribution Services 9.x and 10 doesn’t allow bucketing in subscriptions.Jobs executed for deliveries using the Distribution services feature can reuse Intelligence Server caches for previously executed jobs. However, a job will be created on the Intelligence Server for each email delivery and a separate email is sent to each recipient.
Error Handling Related Tech Note: TN19238: How to set up advanced Administrative notifications in MicroStrategy Narrowcast Server 9.x	Many a times Users complain that they have not received an e-mail. Looking at the log files and debugging the issue is the normal option. Narrowcast server has other methods to track. Transmitter can be set up to save a copy of every e-mail that is sends at a designated file location. Notification can be setup to provide detailed analysis of the error codes. Also has an option to include or exclude some specific error codes NClog allows one to browse the file logically. Predefined user alerts are flagged. Can also drill into a service and see only information related to the service of interest.	Distribution service also has the capability to set up customized e-mail notifications to administrator on failures with error codes included in the body of the mail, but cannot include or exclude any particular error codes from the notification.By default, there is no separate log for Distribution services, it writes to DSSErrors log ( i-server error log), hence making it more difficult to debug. However, a separate error log file can be set up for Distribution services by the administrator

Comparison of various types of MicroStrategy filters

Comparison of various types of MicroStrategy filters

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.

Report Filter Vs Report Limit 

The most common way of filtering a Report in MicroStrategy is via the Report Filter.  Anything placed into this filter appears in the SQL Statement that is sent to the database.  But what exactly happens to the SQL when you place a Metric in the Report Filter?  How does that compare to a Report Limit which also filters on Metric values?  Which is more efficient?  Today we’ll explore.

Since a Metric can have aggregation depending on the level of the Report Template, MicroStrategy resolves this as a Set Qualification, which means it will run a multipass query to first determine the set of Attribute Elements that should be filtered on in the WHERE clause.  This results in multiple hits to the fact table, which could hurt performance.

By comparison, a Report Limit is essentially a HAVING clause.  This would result in simply tagging on the HAVING command to the end of the query that’s already there, clean and simple.

Here’s an example from my system:

No Filter – 32sec – 452 rows

select a12.Attribute,
 sum(a11.Fact)  Metric
from FactTable a11
 join DimAttribute a12
   on  (a11.AttributeKey = a12.AttributeKey)
 join DimDate a13
   on  (a11.DateKey = a13.DateKey)
where a13.Year in (2011)
group by a12.Attribute

Report Filter – 3min 17sec – 242 rows

select a12.Attribute ,
 sum(a11.Fact)  Metric
from FactTable a11
 join DimAttribute a12
   on  (a11.AttributeKey = a12.AttributeKey)
 join (select a12.Attribute  Attribute
 from FactTable a11
  join DimAttribute a12
    on  (a11.AttributeKey = a12.AttributeKey)
  join DimDate a13
    on  (a11.DateKey = a13.DateKey)
 where a13.YearID in (2011)
 group by a12.Attribute
 having sum(a11.Fact) > 1000.0
 ) pa13
   on  (a12.Attribute = pa13.Attribute)
 join DimDate a14
   on  (a11.DateKey = a14.DateKey)
where a14.YearID in (2011)
group by a12.Attribute

Report Limit – 34sec – 242 rows

select    a12.Attribute,
    sum(a11.Fact) Metric
from    FactTable    a11
    join    DimAttribute    a12
     on     (a11.AttributeKey = a12.AttributeKey)
    join    DimDate    a13
     on     (a11.DateKey = a13.DateKey)
where    a13.YearID in (2011)
group by    a12.Attribute
having    sum(a11.Fact) > 1000.0

In this example, there is a pretty significant difference in performance.  Report Limits aren’t always the fastest, but in my experience they usually are for the reports I’m doing.  At the very least, they should be considered and tested for your report and environment.

Reports Limits aren’t just for breaking ranking ties!

Types of Caches in Microstrategy

Different type of Caches Available in Microstrategy

What is Caching:

Caching is the retention of a recently used elements, objects, or report results for the purpose of improving the query response time in the further requests. Caching enables user to retrieve results from stored files rather that executing queries against a database.

There are four types of caches in Microstrategy

1. Element Caches:  Elements of frequently browsed attributes are stored in the memory on the intelligent server and MSTR desktop machines.
2. Object Caches: Definition of frequently used metadata objects are stored in the memory on the intelligent server and MSTR desktop machines
3. Report Caches: Results of previously executed reports are stored in the memory or disks on the I-server machine.
4. Document Caches:  Results of the report services Documents are stored in the memory or disks on the I-server machine.

In Two Tier mode: Object and Element caches exist on the Client Machine for

each Project that the Client accesses.

 In three tier mode: Object, Element, Report and Document caches exists for each Project on the I-Server machine and Object and Element Caches exists on the Client Machines too.

 Note :  Caches are Created and stored at the Project Level, Therefore they are not shared across Projects.

 Element Caches:  An Element Caches is a recently used attribute element list stored in the memory of MicroStrategy Desktop and I-Server machines.

 The example below illustrated is how element caches work.

• A MicroStrategy Desktop User double-Clicks the Customer Attribute in the data Explorer in order to display a list of Customers.
• If no element caches exist n the MS-Desktop or the I sever memory, the element request is send to the ware house for processing.
• The list of Customers retrieved from the warehouse is then stored as an Element Cache in the memory of the  I-Server machine, and the MS-Desktop machine of the user who submitted the request.
• If the same user requests the same  attribute elements, the element cache on the local MS-Desktop machine is used to satisfy that request.
•  If a different user requests the attribute elements, the Element Cache on the I-Server Memory is used to satisfy that request.

Note:  When a user requests elements, Element cache ID’s are matched to the element requests in order to determine if a cache can be used to satisfy the request.

Object Caches:

Object Caches is a recently used objects definition stored in the memory of the MS-Desktop and I-Server. We can create object caches for both application and schema objects.

The example below illustrates how object caches work:

• A user opens the report editor.
• The collection of attributes, metrics and other user objects are displayed in the report editor that makeup the report definition.
•  If no object cache for the report exists in the memory of the MS-Desktop or I Serer machine, the object request is sent to the metadata for processing.
• The report object definition is retrieved from metadata and displayed to the user in the report editor.
•  An object cache is created in the memory of the I-Server and MS-Desktop machines.
•  If the same user requests the same object, the object cache on the local MS-Desktop machine satisfies the request.
• If a different user requests the same report the object cache in the I-Server memory satisfies that request.

 Report Caches:

The I-Server provides report caching functionality that reduce the number of user requests that query the data warehouse. When an user initially runs the report the I-Server caches the result set. Subsequently, when a different user runs the same report again the I-Server can retrieve the result set from the cache rather than having to query the data warehouse again.

The example below illustrates how report caches work.

• A user runs a report.
• The report runs against the data warehouse.
• The I-Server caches the result set and returns the result to the user.
• A second user subsequently runs the same report.
• The I-Server searches the report Caches.
• The I-Server checks the following variables.

1. Are the report and any objects it contain unchanged since the cache is created.

2. For the prompted reports did the second user choose the same prompt answers as the first user.

3. Based on the cache property and security settings, is the second user allowed to access the first user’s cache.

4. Is the cache still active i.e. Is it not expired

 If the answer to any of the variables is no, the cache is not valid, and the I-Server queries the datawarehouse. If the answer to all the variables is Yes the cache is valid and the I-Server retrieves the result set from cache

Document Caching:

Report service document can be cached to improve the system performance and maintain a low memory footprint of the I-Server. Document caches are created on the runtime or on schedule and behave in a similar to the report caches. At runtime the document caches are created only in MS-Web.

For example when you execute a prompted document in MS-Web, an XML document cache is generated. When you reprompt the document, a new XML cache is generated with the data reflecting the new prompt answers.

 You can configure the Document Caching properties at two different levels.

1.     Project Level

2.     Document Level.

Microstrategy Facts

 Fact is a numeric and aggregatable, these are the building blocks of the project. 

   Ex., Quantity, Price, sales etc.,

Different ways to create facts

-       The Fact Creation Wizard is a step-by-step interface that is typically used when you first create a project. It allows you to create multiple facts in a single creation process. This you might have seen in the Project Creation Assistant itself.

-       The Fact Editor, which is discussed in Creating and modifying simple and advanced facts, is used to add advanced features to facts that already exist or to create new simple or advanced facts as your project evolves.

-       Architect, which is discussed in Creating and modifying simple and advanced facts using Architect, is used to create and modify simple and advanced facts in a visually integrated environment.

—> With Architect you can:

         - Create simple facts

         - Create multiple facts quickly

         - Add a large number of facts during project creation

         - Create simple and advanced facts

         - Edit existing facts and configure additional schema-level settings

 —> With the Fact Creation Wizard you can:

             - Create simple facts

             - Create multiple facts quickly

             - Add a large number of facts during project creation

 —> With the Fact Editor you can:

              - Create simple and advanced facts

              - Edit existing facts and configure additional schema-level settings

In the Mapping area, select Automatic or Manual:

• Automatic mapping means that all of the tables in the project with the columns used in the fact expression are selected as possible source tables for the fact. You can then remove any tables mapped automatically or select other tables.

• Manual mapping means that all of the tables in the project with the columns used in the fact expression are located but are not selected as possible source tables for the fact. You can then select which of those tables are used as source tables for the fact.

 For example, suppose you have a column named Sales, which exists in both the Fact_Sales table and the Fact_Discount table. In the Fact_Sales table, the Sales column contains

revenue data. However, in the Fact_Discount table, the Sales column contains discount data. In other words, although the column name is the same in both tables (Sales), the columns

contain different fact data in each table. When creating the Revenue fact, you must select the Manual mapping method so you can select the Fact_Sales table as a source table for the Revenue

fact. When creating the Discount fact, you must select the Manual mapping method so you can select the Fact_Discount table as a source table for the Discount fact. If you use the Automatic

mapping method in both cases, the MicroStrategy SQL Engine may use the incorrect column for the facts.

Column Alias:

A column alias specifies both the name of the column to be used in temporary tables and the data type to be used for the fact.

By default, the data type for a fact is inherited from the data type of the column on which the fact is defined in the data warehouse. However, there are cases where you may need to change this.

For example, you can define a fact to be the difference between two dates to perform a calculation such as the average number of days between a start and an end date. You could create this fact using the following expression:

ApplySimple(“DateDiff(day,#0, #1)”, [Start_Date_Id],[End_Date_Id])

The data type for this fact is automatically set to a Date data type because the Start_Date_ID and End_Date_ID have Date data types. However, the result of the calculation, that is, the difference between the two dates, is an integer. This is used when a temporary SQL table needs to be created for the calculation. If you did not change the data type of the column alias, then the system uses a Date data type and tries to insert integer data into this column. This can cause an error for some database platforms. To avoid the possibility of an error due to conflicting data types, you should modify the column alias for the fact to change the default Date data type to an Integer data type.