There are many operations that we may run while working with OLAP cubes such as cube and partitions processing, executing administrative tasks, performing analysis and more… Three query languages that are used to run these operations: multidimensional expressions (MDX), data mining expressions (DMX) and XML for Analysis (XMLA). We can mainly write and execute these queries in SQL Server Management Studio, but many times we need to implement them within a flow that executes other tasks over other services (database engine, file system…)Read more »
An Online Analytical Process (OLAP) cubes are used as an analytical tool. An OLAP Cube is a multi-dimensional database that is optimized for reporting purposes.Read more »
One of the most asked questions is how to link an OLAP cube with a relational database, or in another way how to join the result of an MDX query with a table stored in a relational database.Read more »
Last month I ran two Business Intelligence pre-conferences in South Africa. A interesting request arose during the course of the preconference in Cape Town. The individual wanted an approach to extracting data from an OLAP cube that would avoid intensive utilization of MDX and more reliance upon T-SQL. His main concern was with filtering the data at run time, via the report front end.
In this “fire side chat” we shall do just that, utilizing the cube that comes with the new Microsoft database “WideWorldImporters” and we shall learn how we can get from thisRead more »
Ever since the early days of my career, SQL Server Reporting Services (SSRS) has been one of my preferred data visualization tools simply because end users and developers alike use it for free. Although a majority of my SSRS solutions have been based off a relational dataset that uses Transact SQL (T-SQL), I have also produced several reports that used Multidimensional Expressions (MDX) to connect and retrieve data from SQL Server Analysis Services (SSAS) multidimensional OLAP cube. Recently, I found myself having to refactor some of these SSAS based SSRS reports, particularly converting a single value SSAS-populated parameter into a multi-value parameter. In this article, I explore how you can go about making these changes using SSRS query designer’s design and query modes.Read more »
In order to build a SQL Server business intelligence solution one needs to:
- Design a de-normalized data warehouse
- Build and schedule an Extract, Transform and Load (ETL) package that will feed the data warehouse at regular intervals with new data from the OLTP database.
- Setup, personalize and process a cube based on the data warehouse.
- Add the processing step to the ETL schedule to ensure the whole chain is automated.
Read more »