Tallan's Technology Blog

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

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

Gabriel Gomez

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…

Introduction to Power Apps (Part I)

Sean Keaveny

Microsoft Power Apps is a brand new release which allows the user to quickly create low or no-code business applications for the PC, tablet, or mobile devices. Microsoft PowerApps is an enterprise level software service created to connect, create, and share business applications. It offers the ability to connect to data from an on-premises server or SaaS services securely, as well as the ability to build cross-platform applications very quickly. By accessing an exposed connection to a repository, and using the templates provided to us from Microsoft, a developer can produce a sophisticated application in less than an hour.
In this blog post, I will show you how to:
1. Use PowerApps to connect to SalesForce, SQL, or SharePoint
2. Setting up a new PowerApps application
3. How to Design Your PowerApp Application
4. How to Add / Delete Entities
Microsoft PowerApps can connect you to…

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…

Create a Multi Column Display Template for SharePoint

Brian Feldmann

SharePoint Display Templates make it fairly easy as a developer to set how search results are rendered for the end user. Just download a copy of an out-of-box display template, modify the markup, add some JavaScript, upload it to SharePoint and you’ve got yourself a new “template” for rendering search results. If you’re new to display templates take a look at SharePoint 2013 Design Manager display templates on MSDN.
As a developer, you have complete control over the way end users see and interact with search results. This post focuses on how you can create a column based display template. The end result will render items in a two column view where items flow top to bottom, left to right.

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

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

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

SSRS Error After Cumulative Updates Applied

Brian Zebarth

After installing windows updates, SharePoint 2013 Service Pack 1 and the March 2016 Cumulative Update some users reported a problem with SSRS reports. They were receiving the following error message.
“Microsoft.ReportingServices.Diagnostics.Utilities.ItemNotFoundException: The item ‘http://sharepoint/sites/SiteName/LibraryName/FileName.rdl‘ cannot be found.”
The error was occurring sometimes as soon as they clicked the link to the RDL file and sometimes the report viewer would come up and the error appeared when attempting to run the report.
I reviewed the ULS Logs and found the following errors:
“Cannot find site lookup info for request Uri http://webservername:12345/{guid}/ReportExecution.svc.”
“Microsoft.ReportingServices.Diagnostics.Utilities.ItemNotFoundException: The item ‘http://sharepoint/sites/SiteName/LibraryName/FileName.rdl‘ cannot be found.;”
I did some research on the errors and found a few posts from people who had similar issues and they had success deleting and re-adding the Reporting Services Service Application. I decided I didn’t want to do that if I didn’t have to so I thought I would try…