Tallan Blog

Tallan’s Experts Share Their Knowledge on Technology, Trends and Solutions to Business Challenges

Category Archive for "SQL Server 2016"

Fixing SQL Server 2012 Support for Custom SSIS Objects in Visual Studio 2015+

You’re using the new One Designer cross-versioning in SQL Server Integration Services, and everything breaks when you try to downgrade to SQL Server 2012. The little icon that indicates that everything has gone wrong shows up,
or when you try to interact with any custom components or tasks you get the following error, or something similar:

 
Now, there are three things worth checking:

Are your UpgradeMapping files set up correctly? They should point to a valid strong-named assembly, and use the same alias, for both versions of SQL Server that you’re attempting to deploy to. If not, fix this issue first and try again.
After migrating your custom objects, navigate to the UserComponentTypeName property (for PipelineComponents) or to the CreationName field of the corresponding DTS:Executable in the package XML.

These should contain either the alias (typically the qualified name of the class, i.e. Sample.SSIS.CustomTask),
or the strong-name associated with…

Analysis Services Tabular: Many-To-Many Relationships, Bridge Tables, and Blank Members

With bidirectional filtering in SQL Server Analysis Services (SSAS) Tabular 2016, it’s easier than ever to build many-to-many relationships into your model. But what are some ways to avoid trouble when building them? This post covers two topics: (1) a scenario that can cause Tabular to match completely unrelated groups across a many-to-many relationship, and (2) some strategies for automating your bridge tables (including where they might need some brains!). If you’re already comfortable with this topic, and just want to see some DAX, feel free to skip right to the calculated bridge tables.
Otherwise, let’s say you’ve built up your many-to-many relationship based on a bridge or relationship table someone generated in the database, and have measures based on one or both of the tables connected by the bridge. When looking at the results, you see that a number of the results…

Analysis Services Tabular: Factless Facts and Revealing Object Relationships

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…

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,…

Part 1 – Analysis Services Multidimensional: “A duplicate attribute key has been found when processing”…

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…

SQL Server Analysis Services Multidimensional vs Tabular

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…

SQL Server 2016 Stretch Database Using Filter_Predicate

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….
Read More.
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…

Working with Temporal Tables in SQL Server 2016 (Part 2)

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…

Introducing Temporal Tables in SQL Server 2016 (Part 1)

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.
Overview
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…

\\\