Uncover Tips to Gear Your Data Cleaning. One of the first tasks performed when doing data analytics or sciences is to clean the dataset you’re working with. The insights you draw from your data are only as good as the data itself, so it’s no surprise that an estimated 80% of the time spent by analytics professionals involves preparing data for use in analysis. You’ll learn techniques on how to clean messy data in SQL, which is a must-have skill for any Data Analyst or Scientist moreover I will discuss and demo different functions commonly used to clean, transform, and remove duplicate data from query outputs that may not be in the form we would like.

JSON (JavaScript Object Notation) is a language in depended, very popular, open standard format for data exchange between applications and services. On this session accompanied by numerous examples we should pass through the usage of JSON in MS SQL. Main topics: - Format MS SQL data in JSON format - Read JSON data in MS SQL - Modify JSON data using JSON_MODIFY - JSON functions, tips & tricks

Market basket analysis with T-SQL, R, Python, Azure ML and SSAS. You will learn how to recognize good products, products which raise the sales of other products, and toxic products, products which are usually sold alone or are the last product in the basket, thus finishing the shopping. Measures like support, confidence and lift, are explained in details. Sequential basket analysis is covered as well. Demos are developed step by step in T-SQL, in order to show how the calculations are made. Then you will see demos also in other languages and on other platforms.

L'auditing, cioè la necessità di sapere quando un dato è stato modificato, come e da chi, è molto spesso un requisito fondamentale per le nostre applicazioni. In questa sessione presenteremo gli strumenti che Entity Framework Core ci offre per gestire tale requisito.

Is it possible to figure out a world in which the Data predominance is made by Azure Data Explorer and other databases (Cosmos DB, Rest Based Data store services, Object Storages) ... and nothing Else? Why are there several Services on Azure in which data exploration is by KUSTO and not by using a classic SQL approach? a new way to interpret the word "Data Engineer" is rising up? which is the truth and which are the myths?

Azure SQL Database is a relational database-as-a-service (DBaaS) based on the Microsoft SQL Server Database Engine's latest version. You can build data-driven applications and websites in the programming language of your choice without needing to manage infrastructure. While considering Azure SQL Database, two of the most critical questions you need to answer are my options for business continuity and how to implement these options? In this demo intensive session, I will discuss five specific options and show you how to implement those. You will walk out of this session equipped with knowledge and scripts to implement appropriate High Availability and Disaster Recovery solutions for your Azure SQL Database.

Provisioning an Azure SQL DB is very easy, but are the default configurations good for you? How to ensure our database is really safe? Come to this session to discover this and a lot more

Come posso spostare i dati da e per il mio Datawarehouse utilizzando le feature disponibili in Synapse Analytics ? Come posso importare TB di dati in modo rapido ed efficiente ? E' sempre necessario farlo? Che strumenti devo utilizzare ? Parleremo di Data Lake, Data Factory, External Tables and many more.

More and more companies are looking with interest at distributed ledgers and Blockchain technologies in order not to have to depend on centralized control and verification entities. However, the sector is still evolving and the adoption of this type of approach could be excessive for many systems that are still centralized by nature, and whose rewriting would result in major investments. In this session we will see a data anti-tampering technology recently introduced in Azure SQL Database and available in the next version of SQL Server.

Unlike CI/CD pipelines for applications, we can't just delete the database and pop up a new one with each new version. Let's start with the production database, and get SQL Server content to developers in containers, and then flow schema and lookup data back into production with migration tools. You can bring the reliability and automation of CI/CD pipelines to Database DevOps with containers.

Concurrent inserts into a clustered table with an ever-increasing key become a bottleneck since Microsoft SQL Server must protect the corresponding pages with a page latch. There are several techniques like, hash partitioning, reserve bits, in-memory OLTP and the feature introduced in 2019 CTP 3.1 called OPTIMIZE_FOR_SEQUENTIAL_KEY. We will compare these features to understand their performance improvements and the effort required to modify your existing application.

Docker, Docker, Docker! Everyone talks about Docker! But how can you use Docker in combination with SQL Server? In this demo heavy session I will show you my approach how to use Docker to work in a Mac-based ecosystem with SQL Server 2019 without any dependencies to Windows. After attending this session you will have a better understanding about Docker, and the various use-cases it supports in combination with SQL Server.

Ask anyone managing a database and they will tell you there is a lot you need to track outside of the database. You need to manage server configuration settings, user access info, and documentation, just to name a few things. And then there are all the testing code snippets and other software tools and setting you to need to keep in order. It can get messy and complex quickly. The good news is that with a slight modification to your existing workflow, anyone managing a DB can track all the work that lives outside the DB using Git. At its core, Git is a DB for tracking changes in files, no matter what is in them. Not only can it help you keep track of your work locally, but it can also help you share your work and make onboarding new folks a lot simpler. In this session we will explore: - Implementing a basic Git workflow - To CLI or Not CLI? - Branching for separation of concerns - Time traveling through previous work - Sharing your work safely

In this session we will learn all about the different HA/DR options for SQL Server and Azure SQL (Azure SQL Virtual Machines, Azure SQL Database and Managed Instance) We will see how to setup a solution to have our SQL environment available even in a case of a mayor disaster in our own Datacenter or an Azure Region

There is no Business Intelligence without Time Intelligence. A good model, a strong date table and a little bunch of DAX functions can make you being able to travel time. In this session we deep dive best practices and shining tips about time intelligence

Having a production machine with SQL Server in your organization is no cheap feat. Significant costs could accumulate due to expensive hardware and licensing. How could we find whether our SQL Server machine is wasting your money? What are common pitfalls? How to detect them and how to remedy them? Learn all this and more in this session, based on real world experience. This session is relevant to both on-prem as well as Azure cloud environments.

Organizing and documenting the scripts we reuse regularly is challenging to many data professionals. Do you usually get requests like "Hey, can you please pull up the audit report and attach the results to this email, please?" You are frustrated by looking at how many scripts you need to search and run? Copy, paste over and over again as you keep getting those types of requests. I totally understand and your frustrations are completely valid. No more frustrations. Empty those word documents and start using the hassle-free SQL Notebooks! How? Notebooks can help you document the process along with executable scripts so you can save the results and share them with others. Reuse the notebooks and work effectively. Excited? Join this session to learn to create your first notebook!

A partire da SQL Server 2005, il database TempDB è stato sempre più pesantemente utilizzato, in molteplici situazioni, che vanno al di là della semplice creazione di tabelle temporanee. In questa sessione vediamo come va configurato e quali sono stati i miglioramenti nel tempo, fino alla versione 2022 in arrivo.

One of the most important tasks for a database administrator is taking (and testing!) backups. As databases get larger and larger, the amount of time it takes to perform a backup can grow as well, to the point where your backups take longer than your available backup window. There are several settings we can use to optimize backup performance, such as buffer counts, maximum transfer size, and the number of files, but trying every combination of settings on a single production-sized database could take weeks or even months. In this talk, we will apply data science techniques to the problem of backup settings optimization and look at different models for approaching the problem and analyzing data. Some statistics background would be helpful, but is not required; the big requirement is a desire to speed up backups.

There are two cornerstones to building optimized and low maintenance queries in Power BI. The first is making sure queries have been designed to refresh at optimal speeds. Primary topics for performance tuning include: removing columns and rows, applied step configuration and order precedence, query folding, managing variables, and tables vs lists for transformations. The other important cornerstone is intelligently identifying and handling errors as they occur during scheduled refreshes. Primary topics for error handling include: replacing vs removing errors, collecting error metadata, and configuring the refresh to either fail or refresh successfully when errors occur.

Diciamo che vuoi migrare un tuo workload SQL Server verso Azure, ed il tuo capo ti ha chiesto di trovare una soluzione tecnologicamente ed economicamente adeguata. Se trovare appigli dal punto di vista tecnologico può sembrare facile, lo è meno quando si tratta di far quadrare i conti. Vediamo insieme quali strumenti e quali tecniche utilizzare per effettuare la migrazione, e quali aspetti considerare in ottica di contenimento dei costi.

Dal 2015 gli utilizzatori di Power BI hanno potuto analizzare dati in real-time grazie all'integrazione con altri prodotti e servizi Microsoft. Con streaming dataflow, si porterà l'analisi in tempo reale completamente all'interno di Power BI, rimuovendo la maggior parte delle restrizioni che avevamo, integrando al contempo funzionalità di analisi chiave come la preparazione dei dati in streaming e nessuna creazione di codice. Per vederlo in funzione, studieremo un caso specifico di streaming come l'IoT con Azure IoT Central.

Dal 2015 gli utilizzatori di Power BI hanno potuto analizzare dati in real-time grazie all'integrazione con altri prodotti e servizi Microsoft. Con streaming dataflow, si porterà l'analisi in tempo reale completamente all'interno di Power BI, rimuovendo la maggior parte delle restrizioni che avevamo, integrando al contempo funzionalità di analisi chiave come la preparazione dei dati in streaming e nessuna creazione di codice. Per vederlo in funzione, studieremo un caso specifico di streaming come l'IoT con Azure IoT Central.

Parameter Sensitive Plan (PSP) è una delle nuove feature di SQL Server 2022 e si colloca nella famiglia di funzionalità note con il nome di Intelligent Query Processing che migliorano le prestazioni dei carichi di lavoro esistenti senza modifiche al codice applicativo. In questa sessione verrà descritto il meccanismo di salvataggio e riutilizzo dei piani di esecuzione attuato da SQL Server, verrà descritto il fenomeno noto con il nome di Parameter Sniffing che trova finalmente soluzione in SQL Server 2022 con Parameter Sensitive Plan.

Temporal tables were introduced in SQL Server 2016 as a feature for providing information about the data that was stored at any point in time, rather than just the current record. Using temporal tables is it possible to track all the changes occurred to the records, making it easy to "travel into the time" to figure out what has changed, when and why. During the session will be discussed some of the most common business scenarios for the introduction of temporal tables. All cases are based on real implementation to customers and on the experience gained on the field: - Data audit. What has changed and when. - Point in time analysis. Check the history of changes over the time. - Warehouse inventory stock. Review fluctuation for product quantity. - Anomaly Detection. Detect anomalies and identify trends. - Slowly changing dimension. Query valid data for a specified period of time - Reproduce financial reports invoices and statements.

Relational databases have been around for 40 years and they are a great choice for most data management needs. On the other hand, telemetry data has some properties that make it not exactly a great fit for a relational database. The last few years have seen the rise of time series databases, specialized for data that has a time attribute. Join me to see what a time series database is, how it works and how you can use it in your projects. I will demonstrate how this technology enables new possibilities and overcomes some limitations of relational databases. Are you working with IOT telemetry data or performance metrics? This is the session for you!

In this session we are going to analyse in detail the T-SQL PIVOT and UNPIVOT operators unveiling their characteristics and pros as well as drawbacks and pitfalls with the available workarounds. In addition, we are going to compare PIVOT and UNPIVOT operators with the standard SQL solutions pointing out differences in the execution plans, performance as well as in the code readability.

Le Durable Functions hanno sempre più spazio nelle soluzioni serverless per la loro capacità di coniugare la possibilità di creare workflow con le skill di coding che tutti apprezziamo. Una delle critiche che vengono loro mosse è che lo Storage Account, su cui basano la persistenza dello stato, potrebbe non essere sufficiente per le nostre "esose" soluzioni. Ma forse non tutti sanno che lo strato di persistenza è modificabile e possiamo usare diversi sistemi alternativi allo Storage tra cui SQL Server! In questa sessione scopriremo quali sono e come utilizzarli nelle nostre soluzioni.

Microsoft ha annunciato durante la Build la disponibilità della prima Technical Preview pubblica di SQL Server 2022. Vediamo da vicino le novità per gli sviluppatori, quali ad esempio la nuova clausola WINDOW o le nuove funzioni per manipolare JSON.

Azure Data Factory is a fully managed, serverless data integration service. It's super easy to start developing powerful pipelines to move data in and out cloud and on-premises services, but there're many features that could be useful to get the best out this tool that you may not be aware off. This, full of demo session, is a collection of lessons learnt and tip and tricks collected implementing real world scenarios in Azure Data Factory.

Transact SQL has been around for many years and most of us have been using it as a tool, a language to manipulate data. But what if we go a step away from these tasks and look on the funny side of T-SQL. Can we use T-SQL to create a game, a drawing and gameplay? Session will explore couple of possibilities of using T-SQL for fun purposes using T-SQL. This way is also a great way to explore and learn the capabilities of the language.