In the analytical world, time is an important slicer. The ability to view data over time helps analyze measures in actionable steps. An Analysis Service (SSAS) database can help accomplish this important step. Business users are going to want the ability to see measures a like year to date as well as the period over period comparisons. SSAS enables Time Intelligence with 2 features: the date table and DAX functions.
Read more »SQL Server index structure and concepts
March 19, 2018In my previous article, SQL Server Table Structure Overview, we described, in detail, the difference between Heap table structure, in which the data pages are not sorted in any ordering criteria and the pages itself are not sorted or linked between each other, and Clustered tables, in which the data is sorted within the data pages and the pages will be also linked in a double linked list, based on the index key. In this article, we will go through the structure of the SQL Server index, itself.
Read more »An overview of the Database Migration Assistant tool provided by Microsoft
March 16, 2018This article gives the overview of the Database Migration Assistant Tool to access, plan and migrate the SQL Server from an old version.
Database migration is part of the DBA job. We can’t avoid it, and there are multiple options that can be taken:
- From SQL Server to SQL Server
- From another platform (Oracle Database, MySQL, PosgreSQL…) to SQL Server
- From SQL Server to another platform (Oracle Database, MySQL, PosgreSQL…)
Introduction to data science, data understanding and preparation
March 14, 2018Data science, machine learning, data mining, advanced analytics, or however you want to name it, is a hot topic these days. Many people would like to start some project in this area. However, very soon after the start you realize you have a huge problem: your data. Your data might come from your line of business applications, data warehouses, or even external sources. Typically, it is not prepared for applying advanced analytical algorithms on it straight out of the source. In addition, you have to understand your data thoroughly, otherwise you might feed the algorithms with inappropriate variables. Soon you learn the fact that is well known to seasoned data scientists: you spend around 70-80% of the time dedicated to a data science project on data preparation and understanding.
Read more »Automate documentation of SQL Server Analysis Server Tabular Model
March 13, 2018There has been an ever-growing discomfort on documenting things especially when it’s very dynamic in nature and I was at one time undergoing the same. We have been developing SQL Server Analysis Tabular Model’s, which were quite a many in numbers and documenting 50 or 60 models manually is a big effort. In addition, development always demands change or enhancements that incurs changes on the documentation that is a continuous process. Adding the changes on the documentation is a time consuming process and sometimes loses track or remains inconsistent with the code built which is a critical issue when the system is in production for years.
I decided to automate the documentation of the SSAS model, to ease the manual effort here as well as save time. In addition, help the end users to see and review the latest change on the models quickly without even bothering the development team.
Read more »Reporting and alerting on job failure in SQL Server
March 12, 2018SQL Server Agent can be used to run a wide variety of tasks within SQL Server. The built-in monitoring tools, though, are not well-suited for environments with many servers or many databases.
Removing reliance on default notifications and building our own processes can allow for greater flexibility, less alerting noise, and the ability to track failure conditions that are not typically tracked by SQL Server!
Introduction
At the heart of the SQL Server Agent service is the ability to create, schedule, and customize jobs. These jobs can be given schedules that determine at what times of day a task should execute. Jobs can also be given triggers, such as a server restart or alert to respond to. Jobs can also be called via TSQL from anywhere that has the appropriate access and permissions to SQL Server Agent.
Read more »SQL Server data security feature RLS (Row-Level Security) and GDPR
March 9, 2018Of late, there’s been a lot of noise around the term, GDPR. Chances are, some of us even had to go through learning sessions targeted at IT professionals to learn about what this new standard of data protection means. GDPR is primarily a European privacy law which sets a new bar, globally, on privacy rights, compliance, and security. GDPR is mainly about protecting the rights of every individual, providing the individual with more control over his personal data. It dictates how data should be handled, managed and protected going forward, the individual’s choice being the prime focus.
Today, data is widespread; many corporations handle part of the data on the cloud and part of it on premises. Our focus being SQL Server, we shall talk about what capabilities Microsoft gives us in order to be compliant with these laws that come into effect on the 25th of May, 2018. We would have to modify our data handling procedures keeping the focus on the security of the data processing.
Read more »SQL formatter tools
March 8, 2018This collection of content will provide an overview of SQL formatter tools, with product page links for each of the tools. If you noticed a SQL formatter tool we missed, please let us know in the comments below.
Read more »Replace bridge tables in a Data Warehouse with SQL Server 2017 graph database
March 8, 2018Just like in Santa’s Bag of Goodies, every release of SQL Server often has something for everyone – be it enhancements to DMVs for the DBAs, new functions for T-SQL developers or new SSIS control tasks for ETL developers. Likewise, the ability to effectively support many-to-many relationships type in SQL Graph has ensured that there is indeed something in it for the data warehouse developers in SQL Server 2017. In this article, we take you through the challenges of modelling many-to-many relationships in relational data warehouse environments and later demonstrate how data warehouse teams can take advantage of the many-to-many relationship feature in SQL Server 2017 Graph Database to effectively model and support their data warehouse solutions.
Read more »SQL Server table structure overview
March 7, 2018Microsoft SQL Server is a relational database management systems (RDBMS) that, at its fundamental level, stores the data in tables. The tables are the database objects that behave as containers for the data, in which the data will be logically organized in rows and columns format. Each row is considered as an entity that is described by the columns that hold the attributes of the entity. For example, the customers table contains one row for each customer, and each customer is described by the table columns that hold the customer information, such as the CustomerName and CustomerAddress. The table rows have no predefined order, so that, to display the data in a specific order, you would need to specify the order that the rows will be returned in. Tables can be also used as a security boundary/mechanism, where database users can be granted permissions at the table level.
Read more »How to upload multiple images to SQL Server
March 6, 2018Introduction
Sometimes we need to store information including photos in our database. For example, the photo of the product, the photo of the team members. But how can we store images in SQL Server?
We could create an application in .NET or Java, but if we do not have experience in those programming languages, we could use SQL Server tools to do it.
In this new article, we will learn the following tips that will help us to work with images including how to:
- insert one image into SQL Server
- store multiple files into a table
- verify that the images were inserted
SQL Server Query Execution Plans for beginners – Clustered Index Operators
March 5, 2018We have discussed how to created estimated execution plans and actual execution plans in various formats in my previous article SQL Server Query Execution Plan for beginners – Types and Options.
In this article we will continue discussing the various execution plan operators related to clustered indexes, and what they do, when do they appear and what happens when they do.
Read more »SQL Server documentation tools
March 4, 2018An overview of the process of SQL Server backup-and-restore
March 1, 2018In a manner of speaking, planning and implementing a SQL Server backup design is an art. Backup, Restoration, Recovery, Business Continuity Plans (BCP), and Disaster Recovery (DR) are different phases of data revolving around the discussions involving data backup. In other words, it’s about how we ensure to retain the business data through any sort of situation that’s thrown at us.
Read more »How to create advanced analytics using Power BI and R scripts
February 27, 2018In this article, we will discover how to find the associations and hidden patterns in a dataset. The tool we will use for this is Power BI and not use any statistical functions or calculation.
Explanation
Power BI is one of the most powerful business intelligence solutions in the market. A cloud-based, user-friendly interface and basic development abilities provide this solution to be more convenient for people to use.
As you may know, Microsoft now supports R script in SQL Server and Power BI. R script support opens magical gates of statistics world to Power BI. Custom Visual offers very useful and interesting visual components for Power BI. In this store, many useful visualizations have been created by Microsoft or the community itself. Some of these visuals use R script infrastructure and this feature allows us to create advanced visual analytic in Power BI. Only a Power BI account is enough to connect and download theses visuals. We will use these visuals in our examples.
Read more »Top 10 things you must document in SQL Server Reporting Services (SSRS)
February 26, 2018Documentation is never fun. I curse having to do it and I curse the person who didn’t do it. It’s a no-win situation.
Luckily I don’t want to tell you to write long documents for setting up or maintaining Reporting Services or creating reports etc. This is more about the main aspects of SSRS that you should be keeping a backup of or maintaining to make your life (and other people’s lives) easier in future. I’ll keep your typing to a minimum.
Read more »How to archive SQL Server data with scale in mind
February 21, 2018We manage data in a growing environment where our clients query some of our data, and on occasion will query past data. We do not have an environment that scales and we know that we need to archive some of our data in a way that allows clients to access it, but also doesn’t interfere with current data clients are more interested in querying. With the current data in our environment and new data sets will be using in the future, what are some ways we can archive and scale our environment?
Read more »Debugging stored procedures in SQL Server Management Studio (SSMS)
February 20, 2018Debugging is one of the most important but painful parts of any software process. To find some errors you have to run the code step by step to see which section of the code is responsible for the error. This is called runtime debugging.
Read more »How to create Intermediate Measures in Analysis Services (SSAS)
February 19, 2018The whole premise of Analysis Services (SSAS) is to place business logic into a central repository (a database). This central repository should be easy to understand from the average reporting user. Simplicity for SSAS databases starts with the Dimension and Fact tables in a data mart or data warehouse. These tables need to have as little additional computed columns as necessary. With this design, adding additional Measures to the SSAS database, both Multidimensional Cubes, and Tabular Models, can help with performance monitoring of the company’s indicators.
Read more »SQL Server table hints – WITH (NOLOCK) best practices
February 14, 2018SQL Server table hints are a special type of explicit command that is used to override the default behavior of the SQL Server query optimizer during the T-SQL query execution This is accomplished by enforcing a specific locking method, a specific index or query processing operation, such index seek or table scan, to be used by the SQL Server query optimizer to build the query execution plan. The table hints can be added to the FROM clause of the T-SQL query, affecting the table or the view that is referenced in the FROM clause only.
Read more »Impact of CLR Strict Security configuration setting in SQL Server 2017
February 13, 2018Every seasoned SQL Server developer will tell you that no matter how hard you try, there are just operations in SQL Server better implemented elsewhere than relying on native Transact-SQL language (T-SQL). Operations such as performing complex calculations, implementing regular expression checks and accessing external web service applications can easily lead to your SQL Server instance incurring significant performance overhead. Thankfully, through its common language runtime (CLR) feature, SQL Server provides developers with a platform to address some of the inconveniences of native T-SQL by supporting an import of assembly files produced from projects written in. Net programming languages (i.e. C#, VB.NET). I have personally found CLR to be very useful when it comes to splitting string characters into multiple delimited lines.
Read more »A walk through the SQL Server 2016 full database backup
February 12, 2018Every DBA, even a beginner, may walk through the SQL Server backup screen multiple times per day. It is mandatory that you know every single detail of every single option you have in the most repeatable task you could do as a DBA.
In this article, I will be discussing every option available in full backup screen of SQL Server 2016.
Read more »How to create charts from SQL Server data using PowerShell
February 8, 2018Introduction
Intended audience
This document is intended for application developers and database administrators who are willing to learn how to generate charts from data stored in Microsoft SQL Server using PowerShell.
Context
In previous article entitled Overview of PowerShell Charting capabilities we’ve seen some components of an assembly called System.Windows.Forms.DataVisualization assembly that is part of the .NET Framework and how to load it inside PowerShell.
Now, it’s time to use what we’ve learned so far.
Read more »Everything you wanted to know about SQL Saturday (but were afraid to ask)
February 7, 2018Description
Technical training is an important part of our careers. Meeting professionals, learning about new technologies, and making the most of the time and resources that we have can make a profound difference on the work that we do.
SQLSaturday provides a quality, inexpensive, and fun way to learn and get involved in the SQL Server community. It is a free, full day of technical presentations from experts that will travel from around the world to help make these events a success!
Read more »Overview of PowerShell charting capabilities
February 6, 2018Introduction
Intended audience
This document is intended for application developers and database administrators who are willing to learn how to generate charts from data stored in Microsoft SQL Server using PowerShell.
Read more »