Problem Today, I received a special requirement from my client that they wanted to build a report for counting distinct Brand Name of Digital Media Type. For example, a data source as below And the client expected the output as below As you saw two above image, the client only wanted to count unique Brand … Continue reading Understanding SCOPE statement in MDX
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 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?
Create linked server to Analysis Services Server Create a T-SQL script to gather all CUBE(s) and generate XMLA script. There are two ways to gather CUBE(s) Use OPENQUERY to gather information via the linked server which we have just created Use a text file and BULK INSERT statement. The txt file contains all CUBE(s) nameDECLARE … Continue reading How to backup all CUBE(s) via T-SQL script