Category: SQL Server Analysis Services OLAP

SSIS – Getting Partition Names for the past 12 months

I had a situation where I was using a conditional split, so that when data was coming in from my source tables it would dynamically go into the correct partitioned table.   Below is the script that I used, as well as for reference the conditional split   — ================================================================================================================== — What we are doing here is to create a…

SharePoint 2013 – Creating a data source to an SQL Server Analysis Services (SSAS) OLAP Cube

Below are the details when using SharePoint 2013 when you want to collect to an SQL Server Analysis Services (SSAS) OLAP cube, instead of only being able to use the SSAS Tabular.   Example: ·         In our example we are going to connect to our Adventure Works DW 2012 SSAS   1.       Go into your SharePoint 2013 website and then…

DAX – Comparing a specific Product Average against the average of ALL products

I had a situation when one of my fellow workers wanted to create a report where they wanted to compare one product’s sales and how it was doing when compared to the average of ALL the product sales.   In our example below we want to extract from the AdventureWorksDW2012 Analysis Services Database   You can get a copy of…

Power View – Using MDX, SSAS Tabular and Power View to display potential issues based on past trends in data

What I am going to explain below is using the above MDX, SSAS Tabular and Power View to display potential issues based on past trends within our data.   For our example we are going to using the example of SCOM data. Within our example we are looking for systems where the available memory has dropped more than 50% of…

SCOM (Systems Center Operations Manager) Cube and Data warehouse

What I have done is I have found that it is very handy having all our SCOM data in a data warehouse and then having it in a SSAS (SQL Server Analysis Services). Within the zip file which can be located here below it has all the details for the following: ·         SCOM Data Warehouse and OLAP Documentation.docx o   This…

Data warehousing – Bringing down data twice but avoiding deleting on the data warehouse Fact Table

This is better explained with our example below.   Example: ·         We had a situation where we were getting data down, but due to the nature of the data we were getting is we were missing data. ·         Also another note was the fact that we did not store the granular data, but aggregated the data, so due to this…

SSAS (SQL Server Analysis Services) – KPI – Comparing Date and Hour for the Previous Week or Multiple Values in your KPI Goal and Trend Expressions

What I wanted to do was to use a KPI in SSAS to compare values going back one week. But I wanted to get the same hour for the previous week. I could not find an example were it had used multiple values in a KPI Goal or Trend expression. All just used one value.   Example: ·         For my…