Defining relationships is central to data analytics, but how can you use SQL Server Analysis Services (SSAS) Tabular to keep them from being overlooked? Often, you can run into the situation where you really just need to say what is missing from a data set or report. Usually, you can work around this conflict of expectations versus reality by building a factless fact. This type of fact table is really just a list of relationships, where the “fact” is really the existence of the row.
Typically, this type of table is built directly into a data warehouse, and helps to materialize calculations that would otherwise be too expensive or awkward to calculate. Think about the steps that you might need to take in order to highlight gaps in data (such as a student missing class), if you were not provided a tidy checklist of…
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.
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…
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,…
Introduction – Part 1
The most common and dreaded error that may occur when processing a dimension in Analysis Services Multidimensional (MD) is undoubtedly “Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: …. “ ‘Common’ because of both the poor data integrity frequently found in cube data sources, and improper modeling of the data in MD. ‘Dreaded’ because a number of distinct situations all give rise to this error and it can be difficult to diagnose which one is the actual problem. There is enough to explore around this error that this is Part 1. There will be a Part 2 subsequently.
The full error message looks like “Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: ‘TABLENAME’, Column: ‘COLUMN’, Value: ‘VALUE’. The attribute is ‘ATTRIBUTE’.” TABLENAME will…
It is no secret that Microsoft is moving away from the original “Multidimensional” (MD) flavor of Analysis Services, instead focusing all efforts on the new “Tabular” (Tab) flavor. This drives the standard advice that Tab should be the basis of most new cube projects. Another idea driving this is that for 60-70% of projects either technology likely will work fine – so it is prudent to go with the newer one which is much earlier in its lifecycle.
However, there are many differences between the two technologies. In particular cases, one or the other is clearly the best fit, but without understanding these differences it is difficult to even assess whether your project is in the 60-70% where it doesn’t matter, or the 30-40% where it may. Even if you would elect Tabular regardless to stay earlier in the product lifecycle, you should be sure you understand the tradeoffs.
Since MD is…
If you’ve ever been faced with the need to archive SQL Server data in order to save database space and/or improve query performance, you found that this would not be a simple task. Especially when the archived data must still be available to existing applications….
Previously. this could not be done without either database structure changes or application code changes or both. Potential solutions could involve partitioning the database table, which could help with performance, or changing the application queries to access different databases/tables in order to get current vs. archived data. Both potentially time consuming and intrusive remedies.
The Stretch Database functionality introduced in SQL Server 2016 uses Microsoft Cloud Services to make data archiving that is seamless to your applications possible. I have worked with Stretch DB in a lab environment and found that it has potential to solve…
In my previous post, I introduced the concept of temporal data, and explained at a high level how SQL Server 2016 implements temporal tables. This post dives into the details of exactly how you create and query temporal tables.
Let’s start with an ordinary table, and convert it into a temporal table. So I’ll create the Employee table, and load it up with some data.
To convert this into a temporal table, first I’ll add the two period columns and then I’ll enable temporal and set dbo.EmployeeHistory as the name of the history table.
Note that because we’re converting an existing table, this must be done in two separate ALTER TABLE statements. For a new temporal table, you can create it and enable it with a single CREATE TABLE statement. Also, and because this is an existing table with existing data, it’s necessary…
SQL Server 2016 introduces System Version Tables, which is the formal name for the long awaited temporal data feature. In this blog post (part 1) I’ll explain what temporal is all about, and my next post will walk you through detailed demos on temporal.
Temporal means, time-related, and in the case of SQL Server, this means that you get point-in-time access to a table, allowing you to query not only the table’s current data, but data as it appeared in the table at any past point in time. So data that you overwrite with one or more update statements, or data that you blow away with a delete statement, is never really lost. It’s always and immediately available simply by telling your otherwise ordinary query to travel back in time when looking at the table.
The mechanism behind this magic is actually…
Microsoft wants to make it easier for businesses to use their data. Otherwise, what’s the point? In SQL Server 2016 you’ll find a virtual tool chest full of features, all with one primary goal: unlocking your data and helping you create new ways of analyzing, visualizing and sharing it.
Building data-rich biz apps
The advent of machine learning and natural language processing made it easier to analyze unstructured data. The challenge was effectively integrating it with structured data, leading to more meaningful discoveries.
Enter SQL Server PolyBase, a feature in SQL Server 2014 that was specific to the Microsoft Analytics Platform System, through which you could access data in a Hadoop Distributed File System. With SQL Server 2016 we cut the strings, making it possible to query data in Hadoop, as well as Azure Blob Storage. Now you can combine the results of your…
Every new version of SQL Server is packed with new features, and SQL Server 2016 is no exception. In this blog post, I briefly describe the major new developer focused features introduced in SQL Server 2016, which just launched on June 1. I’ll cover many of these features in greater depth, in upcoming posts.
• Drop If Exists is a small but convenient language enhancement which helps you write neater T-SQL code, because you no longer need to test if an object exists before deleting it.
• SESSION_CONTEXT gives you a dictionary object that maintains its state across the lifetime of the database connection, so it’s a new easy way to share state across stored procedures running on the server, and even to share state between the client and the server.
• With Dynamic Data Masking, or DDM, you can shield sensitive information…