Search Results for "Terry Bomberger"

Parsing Delimited Strings in a SQL Database

I often receive requests from colleagues needing to solve particularly thorny problems from within a SQL database. This article is intended for those who might find themselves needing to parse a delimited string inside a SQL database, those who find themselves in a position to provide solutions to this particular issue and those liking SQL challenges.
Recently a colleague posed a problem where the value in one column was a list of user property metadata. Each user property was a list of the information required to extract values from a second column in the same table, a list within a list with a common delimiter throughout. A key-value pairs table is a suitable design for this application; the designer chose a different path. The data and the metadata about that data were denormalized into separate columns. Our goal is to determine a solution…

Analysis Services Tabular: Dimensional Default Members

Introduction
In multidimensional SSAS cubes the default member of a dimension is the ‘All’ member. This can be overridden through the Default Member property to force a particular member to be the default choice rather than the ‘All’ member.
In multidimensional cubes there are at least 2 methods to construct default members in a dimension.

Set as property of the dimension
Declare the DefaultMember in the calculation script (method used in the Financial data layer)

By way of contrast, ONLY the ‘All’ member is handled by default in the Tabular model.  One must implement any other setting in DAX.  The following are several examples of constructing default members in a tabular cube using DAX to establish a default.
 
Use Cases
For a Parameter Table
In the Financial/Accounting data layer, a number of parameters drives the computation of the Amount measure.  The value chosen determines the amount column reported…

Analysis Services Tabular: Introduction to a Series

Introduction
This is the first in a series of blog posts related to work done at a global client operating in the reinsurance industry.  The object of this series is to describe the challenges posed and the techniques utilized in constructing several large-scale SSAS Tabular cubes at a global reinsurance company.  The term ‘Data Layer’ appears in many contexts in this and the following blogs.  One can think of ‘Data Layer’ as essentially a data mart in common usage.
We will look at techniques used to overcome some of the business and technical challenges posed:

SSAS 2016 Tabular Model inadequacies compared to Multidimensional Model
Excel 2010 as the desired reporting platform (yes this was in 2016); in Sharepoint – VBA not an option
Fact tables of ~170 million rows (Financial/Accounting); ~65 million rows (contracts); ~70 million rows (claims)
18 M:M relationships (at first count in Financial/Accounting data layer,…

\\\