Tallan's Technology Blog

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

Category Archive for "Data & Analytics"

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…

Design-Time Properties for Custom SSIS Objects – Part 1 – Intro & Tasks

Matthew Gajdosik

Managing the editable properties of your custom objects in SQL Server Integration Services (SSIS) is a great way to improve their usability, but how can you? By properties, I mean the common fields exposed on the Properties tool window in Visual Studio, any time you’re working with a visual designer, and which you’ve probably used any time you wanted to make a precise change, or a quick change.
In SSIS, you can create your own class to handle all of the following types:

Data Flow / Pipeline Components
Control Flow Tasks
Connection Managers
Log Providers
For Each Enumerators (for the for each loop task)

For the most part, these custom classes can be directly modified when editing SSIS packages in Visual Studio, and so anything properties you expose on the class can be edited right from Visual Studio without opening up any new UIs. For simple objects especially, these can…

How Microsoft Treasury uses Power BI

Tallan Partner

 
The Microsoft Treasury group manages $158 billion in assets, including cash and investments, account receivable, equity, and other investments. They act as an in-house bank for operations in over 190 countries, monitor thousands of bank accounts, and provide just-in-time cash management on a global basis. With a preview this large, Microsoft Treasury relies on vast amount of data in order to make fast, accurate, and actionable decisions to keep their operations running smoothly.
In the past, Treasury used a combination of Excel, PowerPoint, and third-party services to analyze and present their data, but Corporate VP, Treasurer George Zinn saw the future in the integration of a different product: Microsoft Power BI.
Power BI is a cloud-based suite of business analytics tools that make it easy to combine data from multiple sources, analyze and visualize information, and share insights. It features easy drag-and-drop…

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…

Analysis Services Tabular: Displaying History and Slowly Changing Dimensions

Matthew Gajdosik

Historical reporting is common enough, but what are some ways to slice through your historical data in SQL Server Analysis Services (SSAS) Tabular? Tracking and including historical data or Slowly Changing Dimensions (SCDs) is common enough in data warehousing, and Business Intelligence as a whole, but putting it into an easily-digested form is always a new set of issues.
In this post, I will walk through some strategies we’ve used for integrating historical data into reporting and analytics solutions with SSAS Tabular, as well as some ways you can restrict this information to give your users a cleaner experience.

Driving Power BI Adoption in your Organization – Learn how Microsoft does this at Scale

Tallan Partner

At Microsoft leaders across the company have committed to fostering a data culture and are often asked how to drive this type of change. An internal program designed to drive adoption of Power BI internally has been at the center of this cultural shift.
Through a combination of training, a comprehensive communication strategy, and user-centric features and design, BI@Microsoft drives adoption of Microsoft data culture with Power BI. This program enables Microsoft employees to use data visualization, business intelligence and statistical analysis in their day-today jobs. Employees were previously limited by a mindset that they didn’t have the technical skills or time necessary to model data. Or they thought the data was not available or accessible. The BI@Microsoft program has proven that data driven decisions are possible at every level of the organization, while also creating loyal fans that influence their…

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…

Creating an Effortless Custom SSIS Object Installer Using WiX

Matthew Gajdosik

When I first wrote custom objects for SQL Server Integration Services (SSIS), distributing my DLL for use and for installation on servers seemed troublesome. Of course, I had to set up an installer. But after the deprecation of the normal setup/installer project included with Visual Studio, which tool could be used that would be easily shared and maintained, and still meet all of the needs for distributing the custom SSIS objects?
Ultimately, I chose the WiX Toolset, as it is already integrated with the Visual Studio gallery and makes a simple job of product versioning, Global Assembly Cache (GAC) / assembly installation, and install path selection. In this tutorial, I will walk through the construction of a skeleton custom SSIS Task and a corresponding WiX installer, that can locate the correct version of SQL Server, install to both the DTS folders and…

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…