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 Goal – Unknown Member is result set
What happened was when I created my KPIs everything was looking correct. But when I dragged in the hour value, I kept on getting an Unknown in my results set for my KPI Goal. Below I will explain how this came about and how I got it resolved. 1. Below is what my KPI Goal looked like as…
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…
SSAS (SQL Server Analysis Services) Tabular – Error when Processing – OLE DB or ODBC error: Server: The operation was cancelled by the user ..
The above error is caused when processing an SSAS Tabular database. NOTE:This only applies to when using MDX and an SSAS OLAP Cube to get your data into SSAS Tabular. NOTE 2:The reason that I use MDX and the SSAS OLAP Cube to get data in is for 2 reasons. · The first is that as we know it…
Excel – Using a Custom MDX Query in Excel Table
I had a requirement where I wanted to use my own Custom MDX Query within an Excel Table. And from the output of the table I then wanted to Pivot this data. I do know that you can do this in Power Pivot, but the requirement was to not use Power Pivot. Below are the steps and it is a…
SSAS – Getting a negative value for measure that has been used in a SUM
When you are browsing data from a cube, you see the grand total or a value in a column it has a negative value. This is while you know that it does not have any negative values so this must be wrong. The reason for the value being negative is due to the fact that data type in your cube…
SSIS–Conditional Split based on Date
Below is a quick blog where I am using a conditional split based on your Date Column, so that you can then output your data to different tables or destination data flow tasks. I also found that there are bits and pieces of information as to how to use the Expressions within the Conditional Split. So this might help…