Problem I need to build a report that compares total sales amount of top 10 Product Sub Categories from January to February, 2013 and the same period in last year. It means I need to build the report that is named as Year-over-Year of Calendar Year To Date ( CYTD YoY) Solution In MDX language, … Continue reading YTD function in MDX
In the previous article, I provided the simple example to start working on ADOMD libraries. The small application was implemented to execute the MDX file. The MDX query returns data from Jan to September in 2014 and the same period in 2013 (Year-To-Date report) Now, we need a flexible method that allows us to choose … Continue reading How to pass parameters to MDX script through ADOMD libraries?
In previous article, I introduced the solution to execute a MDX query by SQL store procedure. However, it has some limitation such linked server performance, user permission... There are other approaches to overcome those disadvantages of the store procedure. In this article, I will introduce ADOM.NET library which also support executes a MDX file. It … Continue reading How to execute MDX query with ADOMD libraries
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, EXCEPT 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