Skip to main content

Logical Table Size the easy way to understand

 

Logical Table Size in MSTR

 Edit 0 2…

This is a number that represents the level of aggregation for a given table. Logical Table Size (LTS) is used my MSTR to identify the best table to choose when generating SQL. This is a number that will be used to identify which table is best fetching the data. For example, I have a report, with Category, Subcategory and profit metric. I have the following two fact tables in the data warehouse


  1. FACT1 - category,subcategory,profit
  2. FACT2 - day,category,subcategory,profit.
Which of the tables is MSTR likely to choose for the join ? MSTR uses an algoritham to find the best fact table for data retrival. It chooses the fact table with the lowest LTS. Lets look at how mstr calculates the LTS, but before that we need to see the hierarchy. You need to use the system hierarchy, since this hierarchy build on true parent child relationship defined for the project.

|| Category




Sub Category



Item



Brand


How many levels are there in this hierarchy ? 4
MSTR will assign a weight based on the level of the attribute in the hierarchy.
Category = 1/4 *10 = 2.5
Sub Category = 2/4 * 10 = 5
Item = 3/4 * 10 = 7.5
Brand = 4/4 * 10 = 10 (note that the lowest level attribute always have a weight of 10 )
Similary for a the date hierarchy, the values are calculated as below.


Year



Month



Week



Day


Year = 1/4* 10 = 2.5
Month = 2/4 *10 = 5
Week = 3/4 * 10 = 7.5
Day = 4/4 * 10 = 10

How to use the attribute weight and calculate logical table size ?

  1. FACT1 = weight for category + weight for sub category = 2.5 + 5 = 7.5 (rounded to 8)
  2. FACT2 = weight of day + weight for category + weight for sub category = 10 + 2.5 + 5 = 17.5 (rounded to 18)
The fact table with smallest of these values is selected for querying the metric. So in this case mstr will use FACT1 in the SQL join.
You might come across hierarchies where a child has multiple parent. for example .


Category


Sub Category


Item
Product


|| Brand
||
Here Brand is a child of both Item and Product. In this case the level of Brand is 4 (by using the level path from Item, here Item is at level 3) or 2 (by using the level path from Product, here product is at level 1)MSTR will consider the highest of these numbers when consideing the level for Brand. ie 4.

Comments

  1. Ergonomic office tableFebruary 9, 2022 at 3:55 AM

    I’ve been surfing on the web more than 3 hours today, yet I never found any stunning article like yours. It’s alluringly worth for me. As I would see it, if all web proprietors and bloggers made puzzling substance as you did, the net will be in a general sense more beneficial than at whatever point in late memory.
    Visit links
    Ergonomic office table
    comfortable working chair
    linear work station

    ReplyDelete

Post a Comment

Popular posts from this blog

HyperIntelligence Training Videos

HyperIntelligence  Training Videos           Design and build hyper cards Optimizing Datasets for HyperIntelligence Using the HyperIntelligence for Office Outlook Add-In Building HyperIntelligence Cards Using HyperIntelligence for Mobile on Android Deploying HyperIntelligence for Outlook Insights On-The-Go: HyperIntelligence for Mobile Building HyperIntelligence Profile Cards Designing Custom HyperIntelligence Cards Using the Calendar with HyperIntelligence for Mobile
4 comments
Read more

Customers Who Live in the Same City as Call Centers

Customers Who Live in the Same City as Call Centers Your new utility company has call centers located throughout the country, and your recent surveys indicate that customers who live in the same city as a call center are particularly satisfied with service due to extremely rapid repairs during power outages. To begin your new advertising campaign, you want to generate a list of Call Centers that coincide with Customer Cities. The following steps create an attribute-to-attribute qualification filter that generates the list of desired cities. To Create an Attribute-To-Attribute Qualification that Compares the Call Center and Customer City Attributes In MicroStrategy Web, log in to a project. Open any folder page (for example, click Shared Folders on the home page). Click the  Create Filter  icon  . From the Object browser on the left, select the  Customer City  attribute from the Customers hierarchy and drag it to the filter pane on the right. Change  Qualify...
1 comment
Read more

Derived metric based on attribute values

Derived metric based on attribute values Here is how could create and display data correctly on using below simple steps.  Create a report with Category, Subcategory and Revenue. Create New Metric in a report or VI.  Case((Category@ID = 1), Revenue, 0) Booksand Name it as Revenue for  where 2 is Category ID for "Books"  Report will display result as below.  Result for new metric is blank. Now to fix this create a new Derived metric on Category attribute first with formula as  Max(Category) {~ }  and calling Books Now Edit the "Revenue for Books metric and Replace Category@ID with this new Books metric formula would looks like this  Case((Books = 1), Revenue, 0).  Report result would now display as expected as shown below
2 comments
Read more

MicroStrategy URL API Parameters

MicroStrategy URL Structure The following table summarizes the root URL structure used for every request to MicroStrategy Web. Environment Main Application URL Administration URL J2EE http://webserver/MicroStrategy/servlet/mstrWeb http://webserver/MicroStrategy/servlet/mstrWebAdmin .NET http://webserver/MicroStrategy/asp/Main.aspx http://webserver/MicroStrategy/asp/Admin.aspx Every request sent to MicroStrategy Web calls a central controller. Parameters are appended to  Main.aspx  or  mstrWeb  (in a .NET and J2EE environment, respectively) to indicate to the controller how the request should be internally forwarded and handled. The following examples show a URL for accessing a MicroStrategy folder when the user does not have an existing session. The URL contains not only the parameters needed to connect to MicroStrategy Web, but also the parameters needed to log on and create a session. J2EE environment: <a href="http:...
2 comments
Read more

Case functions Microstrategy

Ca se functions Microstrategy Case functions return specified data in a SQL query based on the evaluation of user-defined conditions. In general, a user specifies a list of conditions and corresponding return values. Case This function evaluates multiple expressions until a condition is determined to be true, then returns a corresponding value. If all conditions are false, a default value is returned.  Case  can be used for categorizing data based on multiple conditions. This is a single-value function. Syntax Case ( Condition1 ,  ReturnValue1 ,  Condition2 , ReturnValue2 ,...,  DefaultValue ) Example Case(([Total Revenue] < 300000), 0, ([Total Revenue] < 600000), 1, 2) sum(Case (Day@DESC in (“Sat”,”Sun”), Sales, 0) {~+} Sum(Case(Category@DESC In("Books","Electronics"),Revenue,0)){~+} CaseV (case vector) CaseV  evaluates a single metric and returns different values according to the results. It can be used to perfo...
3 comments
Read more

Personalizing file locations, email and file subscriptions using macros in Microstrategy

Personalizing file locations MSTr allows to dynamically specify the  File Location  and  Backup File Location  in a file device using macros.  For example, if you specify the  File Location  as  C:\Reports\{&RecipientName}\ ,  all subscriptions using that file device are delivered to subfolders of  C:\Reports\ . Subscribed reports or documents for each recipient are delivered to a subfolder with that recipient’s name, such as  C:\Reports\Jane Smith\  or  C:\Reports\Hiro Protagonist\ . The table below lists the macros that can be used in the  File Location  and  Backup File Location  fields in a file device: Description Macro Date on which the subscription is sent {&Date} Time at which the subscription is sent {&Time} Name of the recipient {&RecipientName} User ID (32-character GUID) of the recipient {&RecipientID} Distribution Services add...
Post a Comment
Read more

Microstrategy Custom number formatting symbols

Custom number formatting symbols If none of the built-in number formats meet your needs, you can create your own custom format in the Number tab of the Format Cells dialog box. Select  Custom  as the Category and create the format using the number format symbols listed in the table below. Each custom format can have up to four optional sections, one each for: Positive numbers Negative numbers Zeros Text Each section is optional. Separate the sections by semicolons, as shown in the example below: #,###;(#,###);0;"Error: Entry must be numeric" For more examples, see  Custom number formatting examples . To jump to a section of the formatting symbol table, click one of the following: Numeric symbols Character/text symbols Date and time symbols Text color symbols Currency symbols Conditional symbols Numeric symbols For details on how numeric symbols apply to the Big Decimal data type, refer to the  Project Design Guide . ...
3 comments
Read more

Administration standards to optimize network data transfer speeds

Optimization standards to help developers work with the System Administrator to optimize network data transfer speeds: Place all server components in the same segment to minimize latency between the Intelligence Server, Web server, data warehouse server, and metadata server Expand bandwidth and minimize latency between servers and clients to ensure that bottlenecks do not occur when data is requested over your network Use HTTP compression between the Web servers and Web clients to achieve maximum throughput between these components Configure a Web proxy server to handle caching and reduce the load on the Web server
1 comment
Read more

Bursting file subscriptions Microstartegy

Bursting file subscriptions: Delivering  parts of reports across multiple files: Large MicroStrategy reports and documents are often broken up into separate pages by attributes. In a similar way, with Distribution Services, you can split up, or burst, a report or document into multiple files. When the subscription is executed, a separate file is created for each element of each attribute selected for bursting. Each file has a portion of data according to the attributes used to group data in the report (page-by axis) or document (group-by axis). Ex:, you may have a report with information for all regions. You could place Region in the page-by axis and burst the file subscription into the separate regions. This creates one report file for each region. As a second ex:, if you choose to burst your report using the Region and Category attributes, a separate file is created for each combination of Region and Category, such as Central and Books as a report, Central and Ele...
1 comment
Read more