Efficient data management keeps goods flowing smoothly in Denmark. Danske Fragtmaend, the country’s largest national transport and distribution firm, has been moving freight for more than a century. Today, Danske Fragtmaend delivers more than 40,000 consignments each day throughout Denmark, and businesses from small mom-and-pop operations to factories rely on its services.
The firm handles logistics in a central location, where 200 dispatchers keep an eye on the movement of thousands of trucks and their cargo. Both drivers and dispatchers need the latest information to operate efficiently, so they rely on a data platform based on SQL Server 2016. The storage system includes 160 terabytes of flash memory for fast I/O and high uptimes. Throughout the day, drivers continually scan transactions with PDAs and send shipping information including GPS coordinates to the data platform. Fast access to information is essential. Ulf…
When building applications with C# and SQL Server, it is often necessary to define codes in the database that correspond with enums in the application. However, it can be burdensome to maintain the enums manually, as codes are added, modified, and deleted in the database.
In this blog post, I’ll share a T4 template that I wrote which does this automatically. I looked online first, and did find a few different solutions for this, but none that worked for me as-is. So, I built this generic T4 template to do the job, and you can use it too.
Let’s say you’ve got a Color table and ErrorType table in the database, populated as follows:
Now you’d like enums in your C# application that correspond to these rows. The T4 template will generate them as follows:
Before showing the code that generates this, let’s point…
Upgrading your software can be daunting, Microsoft knows. The fast pace of business makes it easy to tell yourself, “I’ll do it later when I have time.” Microsoft gets it! But here are five key reasons to make time to upgrade to SQL Server 2016, which was named DBMS of the Year in 2016 by DBengines.com.
Seamless step-up without rewriting apps. Thanks to November’s SQL Server 2016 Service Pack 1 (SP1), SQL Server now has one programming surface across all editions. If you switch from Express to Standard, or Standard to Enterprise, you don’t have to rework code to take advantage of additional features. Time saved! In addition, the change brings access to innovative features across performance, security, and analytics not previously available in Express or Standard—a great reason to upgrade applications that run on those editions. The Enterprise edition of…
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…
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.
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…
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…
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…
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,…