Metadata Comparison – Microsoft vs. Cognos

Metadata has long been an essential part of every successful organization’s analytics strategy. In this article, I sat down with two of Ironside’s foremost experts on the topic to discuss its origin and value and how two of the industry’s leading vendors in the analytics space address this core capability in a bimodal analytics world.

Susan Ma and Suresh Edara are both long-time Ironside consultants with deep expertise in top BI toolsets: Microsoft and Cognos.

This article will be the first in a series, with future versions diving deeper into each vendor’s technologies.

1. What is metadata and why is it beneficial for analytics?

Susan: Analytics vendors typically implement Metadata or “data that provides information about other data” as a “semantic layer.” This semantic layer or model is a business-focused representation of corporate or operational data that helps end users access data autonomously using common business terms. A semantic model also maps complex data into familiar business categories such as product, customer, or revenue to offer a unified, consolidated view of data across the organization for end-user consumption.

Typically, these semantic models are modeled with one of two different approaches: tabular/relational or multidimensional.

A relational or tabular model draws its name from the relational databases against which they are commonly modeled. The word “tabular” is often also used as it refers to the common table structures of those same relational databases.

These models generally provide a means of securing the data they expose, either at row-level or object-level.

Suresh: Conversely, the multidimensional approach to metadata modeling is based on OLAP (online analytical processing) cube structures. This approach enables a hierarchical presentation of the underlying dimensions and facts. Like relational models, this approach also provides common business definitions, centralized calculations and business logic, predefined aggregation properties, standardized formatting for attributes and metrics, and data and security mechanisms. These multidimensional models are further defined by the various storage mode approaches available. These include ROLAP (R = Relational), MOLAP (M = Multidimensional) and HOLAP (H = Hybrid).

2. How relevant are metadata or semantic models in a bimodal world?

Suresh: A semantic model has traditionally provided a solid governance foundation and “single version of the truth” for enterprise reporting tools leveraged against data warehouses and other databases. It was a de facto way for report developers and business users to interact with data in Mode 1 Enterprise Reporting.

With the advent of Mode 2 data discovery tools like Tableau and Power BI, a majority of the initial implementations of these tools favored time to results over the creation of intuitive metadata models, primarily due to the reality that building well-designed and intuitive models can take significant time and effort.

Unfortunately, we’ve seen many instances where Mode 2 implementations were initially successful, but struggled to expand to a larger audience due to a lack of a solid semantic model. As the Mode 2 implementations are maturing and the line between Mode 1 for Enterprise reporting and Mode 2 for data discovery is blurring, semantic models continue to be very important. The ability to provide a single unified business view with common business definitions, calculations, data field descriptions, predefined aggregations provides a solid foundation even for Mode 2 tools and implementations.

Susan: The semantic model is vital for a business intelligence implementation. It transforms complex physical and logical data models for structured and unstructured data into a business-understandable view, provides the ability to blend data from various sources, and establishes a trusted foundation for business analysis. These models also support flexible security models to protect against improper data exposure.

Today, self-service data analysis is in high demand for many organizations, but this does not mean that governed metadata isn’t important in these situations. In Ironside’s experience, we repeatedly observe significant benefits from the use of a well-developed and intuitive semantic model.

These benefits include:

• Increased user adoption
• Faster time to value (e.g. Reduced time spent searching for the desired data elements, etc.)
• Improved performance

3. When considering the Microsoft and Cognos Business Intelligence toolsets, what tools are available for developing semantic models and how do they compare?

Suresh: When we consider what options these vendors offer, we like to approach the differences in the following way:

Tools & Technologies (high-level comparison)

• Relational tool differences
• OLAP tool differences
• Hosting options/cloud

As a traditional enterprise business intelligence suite, Cognos has supported the concept of semantic models for almost 20 years (starting in the 1990s with Impromptu). There are a variety of semantic model tools available within Cognos Analytics today.

• Relational Modeling: Framework Manager has long been the relational or tabular metadata modeling tool for relational databases. It is a Windows-based client tool, used to create and publish models to Cognos BI server in the form of Report Packages. These packages can then be leveraged to create reports, dashboards and stories or act as sources for OLAP models.

Given its long history (introduced in 2004 with ReportNet), it’s a mature tool that provides extensive functionality to develop models ranging from the very simple to very sophisticated.

Framework Manager provides functionality used by metadata modelers to create both relational and OLAP-style models (referred to as Dimensionally Modeled Relational or DMR). In Ironside’s experience, we most often see relational models created with Framework Manager, however DMR models can be leveraged for situations where multidimensional functionality is required (drill up/drill down, etc.) and the data refresh delays imposed by traditional OLAP cube/cache rebuild processes are deemed to be unacceptable.

In most situations however, we recommend the use of Dynamic Cubes or Transformer for better performing OLAP and approach the design of the solution in a way that minimizes any downtime.

• OLAP Modeling: There are several options within the Cognos suite that can be used to develop OLAP metadata models. The choice is primarily driven by the particular use case.
• Cognos Dynamic Cubes: Cognos Dynamic Cubes are in-memory ROLAP data structures sourced from relational data sources. To support Dynamic Cubes, these data sources need to be structured as star or snowflake schemas (generally Data Marts or Warehouses). The goal of dynamic cubes is to support high performance queries and interactive analysis (reports, dashboards, etc.) on large volumes of data. A utility named Cube Designer is used to develop these OLAP models and publish them to Cognos as a package.
• Cognos Transformer: Transformer is a legacy 32-bit OLAP modeling tool that’s been a core component of the Cognos BI suite since the mid-1990s. It leverages a MOLAP storage mode and creates file-based PowerPlay cubes. These cubes can subsequently be published to Cognos as a package and used to create reporting and dashboard content. Transformer is limited by a restriction of cube size to 2GB (for best performance) and as a result cannot be developed against very large row sets. However, many organizations have been successful in deploying PowerPlay cubes for narrower departmental or functional use cases. We know of many organizations who are still using the robust Transformer models and PowerPlay cube structures they developed more than 15 years ago.

Susan: Focusing now on the Microsoft BI stack, Microsoft SQL Server Analysis Services provides several options for creating a business intelligence semantic model: Tabular (relational), Multidimensional (OLAP), and Power Pivot for SharePoint.

For the metadata modeler, Microsoft offers a unified windows interface – SQL Server Data Tools (SSDT) to create either relational or OLAP models.

Introduced as part of SQL Server 2012, Tabular is the most recently introduced option for enterprise-class metadata modeling in the Microsoft BI stack.

Since then, Microsoft has continually enhanced its features, expanding the DAX (Data Analysis Expressions) and M scripting capabilities. Tabular models can be created of varying complexities, from simple to comprehensive.

While it’s straightforward to create tabular models with SSAS, Microsoft offers a simpler option in PowerPivot.

PowerPivot is an easy to use feature of Microsoft Excel (an add-in for Excel 2010 and 2013, native with Excel 2016), offering visual data modeling with server support provided via SharePoint. Additionally, PowerPivot models can be imported into SSAS to create tabular models of the same design. This feature supports a bimodal operating model where models created by analysts and end-users can be promoted to the enterprise tier for broader value.

Tabular SSAS models can be published either to cloud platforms (Azure Analysis Services) or on-premise. The Vertipaq (x-velocity) engine can be leveraged for in-memory columnar storage which provides excellent performance against large data sets. There is also DirectQuery option which provides a real-time querying capability with direct access to underlying databases.

In terms of OLAP modeling, Microsoft SSAS has long been a leader, first releasing OLAP services with SQL Server 7 in 1998.

SSAS’ OLAP modeling capabilities are very mature and scalable, supporting two main storage modes: MOLAP and ROLAP.

In MOLAP mode, source data is queried, aggregated and stored in the Analysis Services server in a compressed and optimized multidimensional format. This built-in compression technology allows the creation of cubes that are typically one third the size of the original data.

In contrast, ROLAP mode does not pull data from the underlying source to the server, instead querying the data source at runtime. To improve performance, aggregated data is stored as indexed views.

In addition, Analysis Services also offers a HOLAP storage mode. This model, as its name suggests, is a hybrid of MOLAP and ROLAP. Summary data is aggregated and saved in Analysis Services (MOLAP) while requests for detail-level data are sent to the underlying data source in real time (ROLAP).

Both relational and OLAP models are deployed as Analysis Services databases. They are accessible by various client tools (either statically or interactively) such as Excel, Reporting Services (SSRS) and Power BI. SSAS databases are also consumable by tools from other leading BI vendors.

For new implementations of Microsoft Analysis services, Ironside generally recommends the use of Tabular models. From our perspective, Tabular models are faster and more straightforward to design, test, and deploy; and are easier for end-users to leverage with the latest self-service Power BI tools and cloud services from Microsoft.

If you are interested in learning more about implementing either of these tools, or any other platforms we have expertise in, contact us today. The next installments in this series will discuss these technologies and capabilities more in depth.

 

Meet the Experts

Susan Ma

Susan is a Senior Consultant at Ironside with over 15 years of experience in business intelligence, database development, and web app development under her belt. Her mix of experience has made her into one of Ironside’s top experts when it comes to creating highly customized solutions, reshaping analytics systems, designing complex metadata and ETL workflows, and integrating analytics applications into other tools.

Suresh Edara

Suresh has worked in the data & analytics space for over 12 years and is one of Ironside’s resident experts in business intelligence and information management. He specializes in the design, development, and implementation of BI and data warehousing solutions using both proprietary and open source platforms. Regardless of where his engagements take him, Suresh always prioritizes leveraging his excellent communication skills, technical knowledge, and industry experience to provide all-encompassing, end-to-end analytics ecosystems for his clients.

About Ironside

Ironside was founded in 1999 as an enterprise data and analytics solution provider and system integrator. Our clients hire us to acquire, enrich and measure their data so they can make smarter, better decisions about their business. No matter your industry or specific business challenges, Ironside has the experience, perspective and agility to help transform your analytic environment.