Before knowing/starting to work on creation of reports in MicroStrategy, i would suggest to learn/know about the basic terminologies that are used inside, which would pave you to know better as you progress your work/interest on it. So today i’ll be giving an overview on each buzz words/objects that form the base for report creation/generation.
They would be as follows:
As said earlier, Facts are the MicroStrategy objects on which metrics are based. A fact has two characteristics: it is numerical and aggregatable. Examples of facts include revenue, inventory, and account balances.
Note: There are some cases where a fact is not numerical or aggregatable, but these are rare.
Facts are stored in tables in the data warehouse. These fact tables comprise different columns, each cell representing a specific piece of information. You build facts in a MicroStrategy project that point to the columns. Metrics, which are business measures, are then created from the facts.
SQL aggregations, such as SUM and AVG, are performed on the facts in the database tables. For example, in the following SQL statement, the ORDER_AMT column in the warehouse might correspond to the Order Amount fact in the MicroStrategy environment:
SELECT sum(a21.ORDER_AMT) REGION
FROM ORDER_FACT a21
ON (a21.EMP_ID = a22.EMP_ID)
WHERE a22.CALL_CTR_ID in (5, 9, 12)
In this example, ORDER_AMT is the fact, whereas sum(a21.ORDER_AMT) represents a metric.
Attributes act as holders of information, allowing you to add context to your facts in a report. For example, if you had $10,000 in revenue, that number does not mean anything in a business sense unless you know the context, such as which region, the designated time frame for the sales, and the labor involved in the transaction. Simply put, attributes provide categories for the summarization of data.
Attribute elements are the data shown on the report. Think of them as a sub-level of the attribute. For example, if City is the attribute, attribute elements can include London, Milan, and New York.
In the data warehouse, attributes are usually represented by columns in a table, and attribute elements are represented by the rows.
Metrics are analytical calculations performed against stored data (facts) to produce results that can then either be read as status material or analyzed for decision-making purposes. They are similar to formulas in spreadsheet software. Metrics represent business measures and key performance indicators. A metric can calculate revenue, inventory levels, employee counts, or visits to a Web page.
A prompt is used to dynamically modify the contents of a report. With prompts, you can determine, during report execution, the objects to retrieve for the report and report filtering conditions. In addition, you can make different prompt selections each time you run the report.
For example, you can create a prompt for filtering criteria that qualifies on the Year attribute. When you run a report with this prompt in its report filter, you are prompted to select the year for which you want the report results. You can run the report the first time by selecting 2003 and then a second time by selecting 2004.
A report is a MicroStrategy object that represents a request for a specific set of formatted data from the data warehouse. Reports are the focus and goal of business intelligence. They allow users to gather business insight through data analysis.
The different parts of a report include:
– Facts and attributes from the warehouse
– Filters that determine how much data is used to generate the report
– Metrics to perform calculations on the facts
It also contains –> Advanced report objects such as level metrics, transformation metrics, consolidations, custom groups, and drill maps, among others, allow you to create more functional and informative reports, which would be covered as days progresses. You can also use your own customized SQL statements to generate reports from operational systems included in a MicroStrategy project. This is known as Freeform SQL. You can create predictive models based on existing and historical data to project potential outcomes for business activities and transactions –> But to be frank, nowadays people prefer to go for storing those informations as well in the DWH rather than predicting it from the tool, so it will be easy for them to generate reports from it. MicroStrategy Data Mining Services facilitates the development and deployment of these predictive models.
Hope that helps for the day!!
Have a good Day!!