Problem We have an ad-hoc reporting system that implemented in .ASPX and MVC. This website retrieves data through MDX queries. However, we are having a problem when we want to modify the MDX queries, we must re-build and deploy our application. It requires a long downtime period in our system. Approach To avoid the issue, … Continue reading How to execute a MDX query in SQL store procedure
Scenario The client provided a large file of 5 million rows which is example raw file. In production environment, the file would be larger. I have to think about how to load it with minimal timing possibly. Solutions In order to load this file in good performance, it requires many factors. I have been thought … Continue reading How to run Parallelism Data Flows with SSIS Balanced Data Distributor Transformation
TempDb is one of the critical databases of Microsoft SQL Server. It is used for many purposes Temporary user objects Table variables Temporary tables Cursor Temporary procedures Global temporary tables Intermediate results, work files and work tables Sort Rebuild Index Group By, Order By Hash Join and Hash Aggregates Spills to tempdb Temporary LOB storage … Continue reading How to configure TempDb effectively?
Microsoft SQL Server provides a lot of statements to find what data is existed in A set but not in B set such as NOT IN, LEFT JOIN, EXCEP and NOT EXISTS. The question is when we use these statements and which is the best performance in case we execute it on a large data set. In … Continue reading How to use NOT IN, LEFT JOIN, EXCEPT and NOT EXIST effectively
Problem A couple of days ago, my client sent historical data of sales to me through a zip file. I tried to unzip it and loaded with SSIS package. However, the zip file contains a lot of sub folders and files in and a sales file is also zipped within .gz extension. It requires me … Continue reading How to load data through folders and files in SSIS
A couple of weeks, my friends asked me the best suitable approach to move data out a large table. I was interested in this question because there was no right answers. Depending on the situation, environment even database structure to determine what approaches. Actually, there are a lot of techniques you can apply how to delete data … Continue reading TSQL: How to delete large volume data quickly
Scenario SQL Server Analysis Service (SSAS) supports only Window Authentication mode. If you want to connect SSAS remotely from your local machine using some client tools such as SQL Server Management Studio (SSMS), Tableau or others... you will encounter an issue of connecting to SSAS. This is because these tools run under you local account … Continue reading How to connect Microsoft SQL Server Analysis Service remotely?