Tallan's Technology Blog

Tallan's Top Technologists Share Their Thoughts on Today's Technology Challenges

Category Archive for "SQL Server 2016"

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

Mark Frawley

Introduction – Part 2

Part 1 of this post focused on the first category of how the Analysis Services Multidimensional (MD) duplicate attribute key error can arise. It reflects the perspective of an atomic attribute – an attribute having no attribute relationships other than with the dimension key attribute.
This post focuses on the second category of this error, which can arise when an attribute does have attribute relationships besides the (required) one with the dimension key attribute.
As is well known, creating attribute relationships is a best practice in Analysis Services MD for improving query performance. The most common reason attribute relationships are created is to support a natural hierarchy – so your data model has to have one for this to arise. The next most common reason is to support attribute properties, such as a sort order – i.e. when the…

Microsoft’s SQL Platform Continues to Lead the Market with Advanced Data Security

Tallan Partner

Securing customer data while maintaining the highest levels of privacy have always been top priorities for Microsoft and the SQL organization. As a result, SQL Server, which also powers Azure SQL Database and Azure SQL Data Warehouse, continues to be one of the most secure Relational Database Management Systems (RDBMS) on the market.[1]
At the RSA Conference last year, Microsoft talked about their commitment to security and privacy. Microsoft wants to share a few examples of industry-leading security features they shipped since then and update you on their plans to deliver the highest levels of security across the SQL Database product lineup.
Announcing the April general availability of Azure SQL Database Threat Detection for proactive monitoring and alerting of suspicious database activities and potential vulnerabilities.
Using machine learning, SQL Database Threat Detection continuously monitors and profiles application behavior, and detects suspicious database activities…

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

Matthew Gajdosik

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

Matthew Gajdosik

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

Matthew Gajdosik

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

Terry Bomberger

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

Terry Bomberger

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

Mark Frawley

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

Mark Frawley

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

dslattery

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…