In the previous post : Continuous Integration with SSDT and TFS I have described some techniques to allow continuous integration / deployment using SQL Server Data Tools and Team Foundation Server. The solution described works great for deploying to a single instance of a database.
Read more »
Over the past few months we have covered a lot of ground in our little “get togethers”. We have seen a few of my favorite tips and tricks. In today’s discussion we are going to have a look at a few gotcha’s upon which I have banged my head many times.
Read more »
In our SQLShack discussions over the past few weeks, we have dealt with a few of the more conventional SQL Server data manipulation techniques. Today we are going to be a bit more avant-garde and touch upon a subject dear to my heart.
With Power Bi becoming more and more important on the business side within major industries worldwide, it is not surprising that sooner or later every SQL programmer is going to have to learn and be able to ‘talk’ DAX.
In this article we are going to have a look at the a few of the more important ‘constructs’ and produce production grade queries for data extraction and for reports; enabling the reader to ‘hit the ground running’.
Read more »
As a person who has always enjoyed finding new and innovative ways to perform tasks more efficiently and effectively with SQL Server, I have endeavored to document some of the more ‘innovative’ ways and means of doing these things in our SQLShack ‘get-togethers’.
Today, we shall be looking at one of my favourites, the ‘Data Access Layer’, not to be confused with ‘Data Access Layers’ from the Visual Studio world.
Read more »
A few months back, I encountered an interesting challenge at a client site. For those of you whom have read my previous article entitled “Excel in loading multiple workbooks into SQL Server“, you will know that the challenge centered around loading the data from multiple spreadsheets into our SQLShack financial database.
Now, one of the enterprises business rules was that the loading of this data was NOT to occur before that last of the daily spreadsheets arrived in the common data repository.
Read more »
Far too often we encounter clients that are really too keen to establish all inclusive reports for decision making purposes. While this is super (in principle) oft times these folks will inform you that all the data that is within the tables should be present within the report, and this is not always feasible nor practical.
After much thought I came up with an alternative to permit these folks to have their ‘cake and eat it’ and yet not render a cluttered report.
Read more »
One of a database designers’ worst nightmares is having to design a database for business analysts and data stewards whom insist upon creating their own reports, using Excel as a GUI. The reason that I mention this is that user created reports often open up “Pandora’s box”; with many of these folks creating their own ‘miss-information’ due to a lack of understanding of the underlying data. A few weeks back I had the ’fortune’ of working on such a project, which prompted an ‘ah-ha’ moment. I decided to design the backend SQL Server database using the Business Intelligence Semantic Model (BISM) and to employ the super set of tools provided by Microsoft Power BI, with Excel as a GUI. The end results were wildly accepted by the user community and once you see how easy this is to apply, you will be ‘chomping on the bit’ to employ the same techniques on your own user driven projects.
Read more »
This Article is for people with experience using T-SQL and SQL Server Management Studio, but without experience in SQL Server Integration Services (SSIS).
In this article, we will introduce you to the SSIS world and then we will show how to combine different SSIS task to create combined backup tasks. Read more »
A few months ago, I was working on a few SQL Server reports for a client. The one request that I had received (from this client) was to ensure that the finished reports were as ‘all encompassing’ as possible, as they wanted to conduct a considerable amount of Business Analytics, via the reports.
Knowing this, I decided (where possible) to attempt to construct the reports so as to enable the firm to do their ‘what if’ scenarios with a minimal amount of time and effort.
The screen shot below is a sample of the final report AND we are going to look at the steps necessary to create this report. Stay with me!!!
Read more »
This is the third post in the series about database development and testing using SQL Server Data Tools and Team Foundation Server.
Post 1: Continuous Integration with SSDT and TFS
Post 2: Unit testing with SQL Server Data Tools
You can run test in Visual Studio to test the quality of your build. In VS 2012 and 2013 there are 5 kind of tests that are available for the user Read more »
A year or so ago, I worked for an online web grocery software house located in the northern United States. At that time I had my ‘baptismal’ exposure to ‘genuinely dirty data’. Granted most of the data entry was done manually and many times from offshore. The point being that I could not fathom just how many ways there were to spell the brand name of a major cereal manufacturer. Why is this such an issue? The answer is fairly straight forward. Imagine the scenario that you are trying to ascertain the dollar value of breakfast cereals sold in the country from the local supermarket standpoint all the way up to national sales. Imagine this utilizing a SQL Server Multi-dimensional cube. The ‘eagle – eyed’ reader will recognize that the results will not aggregate correctly should our aggregation attributes have a plethora of different ways of being spelt. Read more »
This article on SQL Unit Testing is the second part on the series about SSDT and database development
Part I: Continuous integration with SQL Server Data Tools and Team Foundation Server
In December 2012, a great addition was made to SSDT: The ability to do SQL unit testing.
Read more »
SQL Server Data Tools – a free standalone download or and add-in to visual studio comes in different flavors and versions. Although this blog post uses Visual Studio 2012 and SSDT stand-alone the principles are example are also valid on Visual Studio 2013.
Read more »
In order to build a SQL Server business intelligence solution one needs to:
© Quest Software Inc. ALL RIGHTS RESERVED. | GDPR | Terms of Use | Privacy