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…
SSRS–Creating reports using Custom MDX or DAX to query SQL Server Analysis Services (SSAS) Cube both OLAP and Tabular (Not using the Wizard)
By default in SSRS you can query an SSAS Cube, but this is by using the drag and drop functionality. The issue with this is that it has quite a few limitations. One of the biggest limitations that I found is that you cannot use your own Custom MDX to create your data set. NOTE: You can use the same…
SSIS & TSQL – Geo- location from IP Address to Country
Below are the details firstly from getting your Geo – location information from a table which has your IP Address to country information. And then getting your IP Address information from your source systems and quickly getting this to a Country so that you can see where the people are coming from. Geo – Location information There are a…
Date Filtering in Excel from your Analysis Services Dimension
I recently had to set up another Date Dimension and went through the process of setting up all the properties in the date dimension so that when it is used in Excel, the end users would then be able to use the Date Filters built into Excel. The Excel Date Filters are very handy when creating Excel documents. As an…
OLE DB error: OLE DB or ODBC error: Invalid column name ‘xxx’.; 42S2
Yesterday I thought it would be a quick addition to add a new column to my Fact table. Then add the dimension to Analysis Services. And finally add the dimension to the cube. but I ran into the error below and after struggling for quite a while I found the issue. NOTE: This is possibly one of many solutions. I…
SSIS 2012 – Inserting data into a SQL Server Table from an MDX Query
The reason for this blog post, is I recently had a query if it was possible to get data returned from an MDX Query and then insert the data into a SQL Server Table. And the reason for putting it into SSIS was that we could schedule the job to run on a schedule. I have inserted pictures from my…
Date Filtering in Excel from your Analysis Services Tabular Model
Following on from how to format the date in Analysis Services UDM Model, today I found myself trying to do the same thing for the tabular model. Where Excel was viewing the dates as a label and not as an actual Date. And after some looking around it is fairly simple to implement so that you can then use the…
SharePoint – Power View URL Actions
This is related to using the PowerView reports in SQL Server 2012 using Analysis Services in the tabular mode. I thought it would be helpful to post some of the URL Actions that you can use with your PowerView Reports which will add more functionality to your reports and to your end users. So this will then enable you to…
SSIS – Process Cube with XMLA and Analysis Services DDL
Generating XMLA Script and data from Cube What I was looking to do, was to use XMLA and the Analysis Services Execute DDL Task in SSIS to dynamically process just a required partition that has already been created in SQL Server Analysis Services (SSAS). I found that doing this in SSIS was a quick and easy way in order to…