Ironside Group
  • Home
  • About
  • Ascent Solutions
  • Capabilities
  • Info & Events
  • Blog
  • Careers
  • Contact
  • Search
  • Menu Menu
Request a Meeting
Business Analytics, Technology Spotlight

Metadata Comparison – Microsoft vs. Cognos

metadata
January 22, 2019/by Scott Misage

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.

metadata

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.

metadata

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.

bimodal enablement assessment


 

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.

Tags: Analytics, Business Intelligence, Cognos, Cognos Analytics, Metadata, Microsoft, SQL
https://www.ironsidegroup.com/wp-content/uploads/2018/12/bigstock-Two-color-apples-kissing-each-52443403.jpg 350 750 Scott Misage https://www.ironsidegroup.com/wp-content/uploads/2018/03/logo-with-words.png Scott Misage2019-01-22 08:40:102019-01-22 12:42:15Metadata Comparison – Microsoft vs. Cognos

See 3 reasons why AWS analytics are well within your reach.

Recent Posts

  • Why outsourcing can make all the difference when it comes to successful data and analytics transformation
  • What’s keeping mid-market companies from leveraging advanced analytics and AI in 2023?
  • Learn Why So Many Companies Are Turning to
    a Hub-and-Spoke Data Model
  • Data governance presents big challenges in the mid-market
  • Mid-market Leaders Report Back from the Road

Categories

  • Awards & Recognition
  • Business Analytics
  • Data Management
  • Data Science
  • Featured
  • Technology Spotlight
  • Uncategorized

Newsletter

Ironside Follow

We make the world of business smarter. A trusted partner who turns data into valuable insights that drive positive business outcomes #Data #Analytics #AI #Cloud

IronsideGroup

Today's #Take30 #tbt dives into a use-case driven discussion of how #advancedanalytics integrates data from multiple sources to drive change in the eight areas of the #HigherEducation lifecycle. Check out the webinar with our partner @ibm here: https://irns.de/3Zdtdcz

Reply on Twitter 1639041567372775424 Retweet on Twitter 1639041567372775424 Like on Twitter 1639041567372775424 Twitter 1639041567372775424
Retweet on Twitter Ironside Retweeted

▶️ WATCH | Panelists at the CDO Midwest Summit 2022, share insights on accelerating innovation and using data and analytics as business drivers.
https://bit.ly/3IV0Vxm

@Rackspace
@BelcanGlobal
@SVB_UK
@MedOneInc
@IronsideGroup

✔️Check out our 2023 events here:

Reply on Twitter 1633195811294298112 Retweet on Twitter 1633195811294298112 1 Like on Twitter 1633195811294298112 Twitter 1633195811294298112

For today's #Take30 #TBT, we're revisiting our S.A.L.T. demo, exploring the powerful combination of #Snowflake, #Alteryx, & #Tableau and how it can be used to implement a #ModernAnalyticsFramework. Check it out here: https://irns.de/3leq2Dv

Reply on Twitter 1636480297045819396 Retweet on Twitter 1636480297045819396 Like on Twitter 1636480297045819396 Twitter 1636480297045819396

For today's #Take30 #tbt, we revisit our session on how the #Banking industry can utilize #advancedanalytics to help their business. Check out this clip to see the use cases our expert cover, and click the link for the full session! #DataScience https://irns.de/3L82JFS

Reply on Twitter 1633968390128664588 Retweet on Twitter 1633968390128664588 Like on Twitter 1633968390128664588 Twitter 1633968390128664588

We are happy to announce a new member of our #DataScience practice, Josh Angelchik. Bringing his experience from both the #consulting and #product sides at companies of all sizes, Josh can optimize existing data workflows and bring product ideas to life from scratch.

Reply on Twitter 1626644704887509005 Retweet on Twitter 1626644704887509005 Like on Twitter 1626644704887509005 Twitter 1626644704887509005

Ironside helps companies translate business goals and challenges into technology solutions that enable insightful analysis, data-driven decision making and continued success. We help you structure, integrate and augment your data, while transforming your analytic environment and improving governance.

LINKS

  • Home
  • About
  • Ascent Solutions
  • Capabilities
  • Info & Events
  • Blog
  • Careers
  • Contact

GET IN TOUCH

781-860-8840

GetInsights@IronsideGroup.com

Corporate Mailing Address
177 Huntington Ave Ste. 1703
PMB 89714
Boston, Massachusetts 02115-3153

131 Hartwell Ave
Lexington, MA 02421

Regional offices in Boston, New York City, Philadelphia, Austin, and Cincinnati

>>Managed Services Support

  • linkedin
  • twitter
  • youtube
  • mail
Also of Interest
  • PowerBI Archives
  • Ironside Tech Tip: Working with IBM...
  • The BI Tool Roundup: Finding What Fits
© 1999-2022 Ironside Group. All Rights Reserved.
What to Consider When Building Your Data Democratization Strategydata democratizationsnowflakeSnowflake: A Modern Cloud-Based SQL Data Warehouse
Scroll to top
Send this to a friend