Ensemble Modeling Forms (DWH Models)

This is a copy/paste post of Hans Hultgren‘s blog post about the forms of Ensemble Modeling:

Ensemble Modeling Forms: Modeling the Agile Data Warehouse


Anchor Modeling. Data Vault Modeling. Focal Point Modeling. To name a few. In fact there are dozens of data warehouse data modeling patterns that have been introduced over the past decade. Among the top ones there are a set of defining characteristics. These characteristics are combined in the definition of Ensemble modeling forms (AKA Data Warehouse Modeling). See coverage notes on the Next Generation DWH Modeling conference here (and summary here).

The differences between them define the flavors of Ensemble Modeling. These flavors have vastly more in common than they have differences. When compared to 3NF or Dimensional modeling, the defining characteristics of the Ensemble forms have an 80/20 rule of commonality.

All these forms practice Unified Decomposition (breaking concepts into component parts) with a central unique instance as a centerstone (Anchor, Hub, Focal Point, etc.).
Each separates context attributes into dedicated table forms that are directly attached to the centerstone.
Each uncouples relationships from the concepts they seek to relate.
Each purposefully manages historical time-slice data with varying degrees of sophistication concerning temporal variations.
Each recognizes the differences between static and dynamic data.
Each recognizes the reality of working with constantly changing sources, transformation and rules.
Each recognizes the dichotomy of the enterprise-wide natural business key.
From that foundation of commonality, the various forms of Ensembles begin to take on their own flavors.

While Data Vault is foundationally based on the natural business key as the foundation of the centerstone (Hub), both Anchor and Focal Point center on a unique instance of a concept where the business key is strongly coupled but separate from the centerstone (Anchor, Focal Point).
Both Data Vault and Anchor aim to model Ensembles at the Core Business Concept level while Focal Point tends to deploy slightly more abstracted or generic forms of concepts.
Data Vault and Focal Point utilize forms of attribute clusters (logical combinations) for context while Anchor relies on single attribute context tables.
And there are other differentiating factors as well.

There is one thing that we can all agree on: modeling the agile data warehouse means applying some form of Ensemble modeling approach. The specific flavor choice (Data Vault, Anchor, Focal Point, etc.) should be based on the specific characteristics of your data warehouse program.

* Learn more on Anchor Modeling with Lars Rönnbäck here: Anchor Modeling

More info about Ensemble Modeling: https://hanshultgren.wordpress.com/2012/11/20/ensemble-modeling/

Gartner’s Magic Quadrant for Business Intelligence and Analytics Platforms 2015

Today Gartner’s Magic Quadrant for Business Intelligence and Analytics Platforms 2015 spread across the internet.

Gartner’s Magic Quadrant for Business Intelligence and Analytics Platforms 2015

Here’s a comparison between 2013, 2014 and 2015:

Magic Quadrant for Business Intelligence and Analytics Platforms 2015 Comparison

Read the full report here:

The Microsoft BI and analytics product portfolio supports a diverse range of centralized and decentralized BI use cases and analytic needs for its large customer base. Organizations typically deploy SQL Server and SharePoint to support IT-developer-centric data management, reporting and administration requirements, while business-user-oriented, self-service data preparation and analysis needs are delivered by the Power BI components of the portfolio through Excel 2013 and Office 365. Business-user enablement is a clear focus of Microsoft’s product road map and business model evolution — as evidenced by its new “freemium” Power BI product offering (currently in preview), which can be deployed as a stand-alone solution for business users to author and share analytic content without the need for Excel 2013 or an Office 365 subscription.
Microsoft’s leadership position in the Magic Quadrant is primarily driven by a strong product vision and future road map, as well as a clear understanding of the market’s desire for a platform that can support systems-of-record requirements and deliver easy-to-use data discovery capabilities, with support for promotability of business-user content and governance. Power BI has gained some traction, but has yet to gain widespread market acceptance due to the complexity of on-premises deployments and the relatively limited functionality currently delivered through the Office 365 cloud; barriers which Microsoft is trying to address with the new Power BI offering currently in preview and due to be released later in 2015. As Power BI matures and cloud adoption grows, Microsoft is positioned to leverage its large customer base (and capitalize on the already pervasive use of Excel and the existing SQL Server Analysis Services footprint in the market) to expand the breadth and depth of its deployments in organizations and increase its overall BI and analytics market share, if it can increase its focus on BI sales and marketing and overcome customers’ structural barriers to adoption.

Overall cost of ownership and license cost remain the top reasons customers choose Microsoft (according to the survey). Microsoft has integrated good capabilities into Excel such as Power Query, Power Pivot, Power View and Power Map, which are included with existing enterprise license agreements. Additional cloud-based consumption and collaboration capabilities are currently available in Power BI through a subscription-based pricing model in Office 365. Microsoft recently announced a freemium license model for its new stand-alone Power BI offering, which includes Power BI Designer for content authoring, set to be officially released during 2015 and featuring a free tier for up to 1GB of data storage per user and a Power BI Pro option for up to 10GB available for $10 per user per month (significantly reduced from the Power BI version currently offered through an Office 365 subscription).
Many organizations already use Microsoft Excel extensively for data manipulation and presentation of information through spreadsheets, which gives Microsoft a strong foundation on which to build with Power BI and close the gap between it and the data discovery leaders. A key differentiator for Microsoft is its ability to deliver a range of business user capabilities, encompassing self-service data preparation with Power Query and Power Pivot, interactive visualization through Power View and Power Map and the ability to share with SharePoint and Office 365, which few vendors can claim without third-party support and partnership. Platform scalability is a strength of the Microsoft platform, which ranked highest for data volume accessed — with an average data size of 62TB, compared with an overall average of 14.2TB.
Microsoft reference organizations also report deployment sizes larger than those of any other vendor in this Magic Quadrant, with an average number of end users of 6,000 compared with an overall average deployment size of 1,554. With the release of the new stand-alone version of Power BI, business users will have access to built-in connectivity to on-premises SQL Server Analysis Services cubes, which will allow organizations to leverage existing data assets without having to move to replicate in the cloud and further unlock the value of existing multidimensional data structures.
Microsoft products scored well in its traditional areas of strength: BI administration and development, and integration and collaboration. They also scored highly on business-user data mashup — an area of investment for Microsoft with Power Pivot and Power Query.
Reorganization and new leadership at Microsoft appears to be positive for Microsoft BI. Since taking over, new CEO Satya Nadella has made support for Apple and Android devices, as well as cloud deployment, a high priority.

Microsoft’s product portfolio is complex and includes many components, which can cause confusion for customers evaluating purchase options. The fact that many of the newer capabilities that are important to buyers in this market require current versions of Office, SQL Server and SharePoint adds to the complexity and represents a barrier to adoption for many organizations that are on older versions and are not yet willing to buy Office 365 and deploy BI and analytics in the cloud. For example, the workflow between components such as Power Query, Power View and Power Pivot is not yet completely seamless. Moreover, the role of SharePoint dashboards and Reporting Services has not been clearly articulated; Reporting Services is not supported in Azure. While it is a work in progress, Microsoft is attempting to address many of these limitations in the forthcoming stand-alone version of Power BI, which does not require Office 2013 or an Office 365 subscription and can access Analysis Services structures and content without physically moving underlying enterprise data to the cloud.
Microsoft had the highest percentage of customer references citing absent or weak functionality (for example, no drill-through capabilities in Power View) as a platform problem. This is consistent with last year’s results; and while Microsoft is addressing weakness in mobility, analytic dashboards and free-form interactive exploration with Power BI, market awareness and adoption has been slow to materialize — as shown by minimal client inquiries since its launch in early 2014.
Microsoft’s sales model continues to be a pain point for customers, who rated Microsoft fourth lowest for overall sales experience. Customers have historically found it difficult to engage directly with Microsoft during the sales cycle, which was a major complaint from IT but is an even greater concern for Microsoft as it attempts to appeal to business buyers that have high expectations of simplified license models and purchase options.
Microsoft has a large network of implementation partners and developers that are skilled in most aspects of traditional centralized BI deployments. However, customers may have difficulty finding external resources with experience in the newer Power BI stack, which requires a different set of skills and expertise than Microsoft’s sweet spot of systems-of-record, developer-focused BI deployments.

Previous reports by Gartner:
Gartner’s Magic Quadrant for Business Intelligence and Analytics Platforms 2014, click here for the full report by Gartner.

Gartner: Magic Quadrant for BI Platforms 2013

Data driven subscriptions cannot be created because the credentials used to run the report are not stored,the report is using user-defined parameters values, or if a linked report, the link is no longer valid.

Well SSRS is stil bugging you huh?


There are a few things you may need to check:

1) Make sure that you’ve stored the credentials of the data source IN the report

2) Since the data-driven subscription is running without an actual user-context, the use of User!UserID is not allowed.

Start by adding this custom code to your report (under ‘Report Properties‘ -> ‘Code‘):

Public Function UserName()
Return Report.User!UserID
Return "System"
End Try
End Function

Now you need the find: User!UserID

And replace it by: code.UserName()

in the Code-view of the report.

SSAS: Color Expressions for your calculated measures (calculations) in your cube

It’s a typical thing on a Friday…being a bit distracted sometimes.
I was designing a new cube and suddenly noticed the ‘Color Expressions’ thingy at the bottom while creating a new calculation in the Calculation-tab in Visual Studio:


Haven’t tested it with different versions of Excel, but I think this is general client behavior if I may call it that:


Could be very useful I think, will discuss this option with the business….one day…next year…or probably not 🙂