MicroStrategy – Data Marts

MicroStrategy – Data Marts ”; Previous Next Data mart is a smaller form of data warehouse, which serves some specific needs on data analysis. It is usually derived as a small part from the bigger data warehouse. The main purpose of creating data marts is to achieve some analysis, which is difficult to achieve through the regular warehouse because of the different level of granularity of data in the warehouse or applying some complex calculations. In MicroStrategy, a data mart is created using the following steps. Step 1 Open a report in the edit mode. Choose Datamart → Configure Datamart. And the following window appears. Step 2 Choose the appropriate location from the database instance dropdown menu. Step 3 Choose the option to create a new table, if the table is to be re-created every time the report is run. Or you may choose to add to an existing table so that the data gets added to the result from the previous run. On successful completion of the above three steps, the data mart gets added to the report. Print Page Previous Next Advertisements ”;

Creating a Dashboard

MicroStrategy – Creating Dashboard ”; Previous Next A dashboard is made up of multiple visualizations. It shows many attributes grouped into separate visualizations. When we place some common attribute or metric in multiple visualization, it is easy to study the variations among them. In the following example, we will create a dashboard showing some common attributes among the visualizations. Step 1 Create a grid visualization using superstore.xlsx as an example data source. We drag the attributes product – Subcategory and Shipping cost – to the rows box. Then we insert the second visualization into the report as shown in the following screenshot. Step 2 Add all the above attributes as well as an additional attribute named unit price to the newly inserted visualization as shown in the following screenshot. Step 3 Finally, apply different visualization types to these grids. We apply pie-chart to the top visualization and heat-map chart to the bottom visualization as shown in the following screenshot. The result shows a dashboard with some common attributes used in the two visualizations. Print Page Previous Next Advertisements ”;

Heat Map Visualization

MicroStrategy – Heat Map Visualization ”; Previous Next A Heat Map visualization shows adjacent colored rectangles, each representing an attribute from the data set. It allows you to quickly grasp the state and impact of a large number of variables at one time. For example, heat maps are often used in the financial services industry to review the status of a portfolio. The rectangles show a wide variety and many shades of colors, which emphasize the weight of the various components. In a Heat Map visualization − The size of each rectangle represents its relative weight. The color of each rectangle represents its relative value. For example, larger values are green and smaller values are red. The large areas, represent different groups of data. The small rectangles, represent individual attribute elements. Example In this example, we will create a heat map visualization for product subcategory in terms of the profit they generate. Step 1 Create a blank visualization and choose heat map from the list of available graphs. As you can see it needs at least 1 metric and 1 attribute. Step 2 Let”s add product sub-category to the groupings tab and profit to the size by and color by tabs. This produces the heat map rectangles. The green color indicates a profit value of more than 50% while the red color indicates a profit value of less than 50%. The stronger the shade of the green color, the higher is the profit. Similarly, the stronger shade of the red color, the lower is the profit. Step 3 It is possible to add more attributes to the Grouping clause and it will produce large number of rectangles. In this example, add Customer segment and Product container. On hovering the mouse pointer on each rectangle, we can see the description of all the attributes that make that rectangle. Print Page Previous Next Advertisements ”;

MicroStrategy – Metric Comparison

MicroStrategy – Metric Comparison ”; Previous Next Metrics are the numerical values on which we can apply mathematical calculations and also compare them numerically. MicroStrategy desktop provides some functionality to compare the values of two metrics using the filtering functions. If required, we can also create a derived metric to make complex comparisons based on some specific calculation. Following are the steps to create a comparison between two metrics. Step 1 Create a visualization with the grid report using the superstore.xlx as an example data set. Next, drag the two metrics – Unit price and Shipping cost – under the filter tab as shown in the following screenshot. Step 2 Enter some specific values in the filter condition of both the metrics, so that we can compare their values within a range. The following screenshot shows the result after entering the values. Print Page Previous Next Advertisements ”;

Shortcut & Embedded Filters

MicroStrategy – Shortcut & Embedded Filters ”; Previous Next In MicroStrategy, we can create shortcuts to filters. For this, we have to use the results of an existing report as a filter for another report. The first report itself becomes a filter inside a new report. This type of filter is called a shortcut-to-a-report filter. This is a part of MicroStrategy server edition and we will take some examples from builtin data sets in MicroStrategy server. Following are the steps to create a shortcut-to-afilter. Step 1 Open the filter editor. Choose the filter definition area and double-click it. It will open the dialog box showing the option “Add a shortcut to a filter”. Step 2 On the next screen, a filter dialog box pops up. Enter the name of the filter, which we want to use or click browse and select the filter to use. Step 3 Finally, the following screenshot opens which has the filter name and filter definition which is now a shortcut-to-a-filter. Print Page Previous Next Advertisements ”;

MicroStrategy – Nested Metrics

MicroStrategy – Nested Metrics ”; Previous Next Nested Metric in MicroStrategy are the calculations in which one aggregation function is enclosed inside another. They are useful when in the data warehouse design, we do not have data stored at the required level of granularity. In such case, we create an inner formula and an outer formula. Combining them creates the nested metric. Example In this example, we aim to find the average sales for each sub-category as compared to the total sales under each category. Step 1 Create a report with Category and sub-category as its two columns. Next, right-click anywhere under the data source tab and near any of the measure fields. A pop-up appears which shows the create metric option. We create the first metric with the following formula and name it as sum_subcat_sales. Step 2 Next, we create another metric with the name Category_sales. In it, we write the inner formula for the sum of sales for each category and the outer formula giving average sales for each category, corresponding to the sub-category. Step 3 Finally, drag both the newly created metrics to the report to see the result. Print Page Previous Next Advertisements ”;

MicroStrategy – Refreshing Reports

MicroStrategy – Refreshing Reports ”; Previous Next The reports created in MicroStrategy Servers are accessed by the users repeatedly to find the new results from the additional data gathered in the report source. Hence, the data in the report needs to be refreshed both periodically as well as on demand by the user. The reports in MicroStrategy desktop version can be refreshed by simply reporting the data again. This is done by using the refresh button available in the menu. Example Let”s consider the All_sales report. Currently, the report shows the data as shown in the following screenshot. Let’s add some data to the source. We add the category aquatic animals. On clicking the refresh button, we get the new result as shown in the following screenshot. Print Page Previous Next Advertisements ”;

MicroStrategy – Graph Visualizations

MicroStrategy – Graph Visualizations ”; Previous Next MicroStrategy Desktop provides 10 standard graphs which are readily available to be plotted with a data source. Each of them gives a different view of the data depending on the number of attribute or metrics we are going to use. The coloring features in each of them will make it easy to understand the different chunks of data present in a single data visualization. Visualization Gallery In the right most window of MicroStrategy Desktop, there is a visualization gallery, which shows options for 10 different graph types. Grid − Represents data in the form of data grid as rows and columns. Heat Map − Shows rectangles of different colors showing a range of values. Bar Chart − Presents vertical bars of different length showing the strength of the parameter measured. Line Chart − Shows the lines indicating variation of value of one variable with respect to another. Area Chart − Shows areas of different colors corresponding to different values. Pie Chart − Shows the slices in a circle, with the size of the slice corresponding to the value of the variable measured. Bubble Chart − Represents many bubbles corresponding to the range of the value of the variable. Combo Chart − Combines Bar chart and Line chart into one visualization. Map − Displays data as map markers on an interactive map. Network − Used to identify relationships between related items and clusters of values. The following screenshot shows different Graph Visualizations. Print Page Previous Next Advertisements ”;

MicroStrategy – OLAP Services

MicroStrategy – OLAP Services ”; Previous Next Online Analytical Processing (OLAP) is a multidimensional analysis of business data. It provides the capability for complex calculations, trend analysis, etc. MicroStrategy’s OLAP Services is an expansion of MicroStrategy Intelligence Server. It uses the concept of Inmemory Business Intelligence. This helps the BI platform to extensively improve the performance and analysis. The various OLAP manipulations on the report uses features such as aliasing, banding, sorting, pivoting, page-by, and so on. These features do not cause the report to be reexecuted against the warehouse, and therefore have a much faster response time. Following is a brief description of various OLAP features available in MicroStrategy Desktop. Aliasing − This feature is used to rename any object on the report grid, such as attribute names, consolidation names, custom group names, and metric names. Banding − Used to color groups of rows or columns so that they form bands of data that are easy to locate and analyze. Page-by − It is a way to segment data in a grid report by placing available attributes, consolidations, or metrics on a third axis called the Page axis. Pivoting − Used to rearrange the columns and rows in a report to view data from different perspectives such as moving an object from the row header to the column header and vice versa. Sorting − MicroStrategy Desktop offers quick sort, advanced sort, and hierarchical sort of row or columns. Subtotals − It is used to add, remove, and edit the subtotals at different levels for metrics on the report. Thresholds − A threshold highlights data that meets conditions defined by the user. Following is an example of applying thresholds. Consider the Employee report created in the previous chapter using an excel file. In the report, we will apply threshold colors to various salaries using the following steps. Select Threshold Column In the employee report, click the salary column and choose the threshold from the drop down. Apply Threshold Option The next window provides options to choose the type of threshold. We choose the color based threshold with default colors and values. In case we want to explore other non-default threshold options, we can click the Advanced Threshold Editor, which shows the following additional options. Threshold Result The final result of the threshold is shown in the following image which highlights the different salary values as per the threshold color chosen. Print Page Previous Next Advertisements ”;

MicroStrategy – Report Objects

MicroStrategy – Report Objects ”; Previous Next Each report in MicroStrategy is built using some underlying objects which represent the business scenario. These objects together represent the set of data requested by the report user and also the relationship between the various data elements. To get the report objects of a report, open the report and click the report object icon as shown in the following screenshot. The above screenshot shows the report objects used in the report. In the current example, we have three report objects − Category − It is a report attribute showing the category of the products sold. Region − It is a report attribute showing the region of the products sold. Year − It is an attribute which contains two metric objects (profit and revenue). Report Objects are very important from report design perspective as they decide which fields from the data source goes into the report and also the calculations applied on those fields. Print Page Previous Next Advertisements ”;