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…
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 – Stopping an Error from Propagating to its Parent (Enabling an SSIS Package to continue when an error happens)
Due to our environment and packages we create, we have to enable the packages to continue even if it fails for some reason (Cannot connect to a Server for example) so that the rest of the data will be captured. In the past we configured the MaximumError count to be 100, but this is not ideal, because if another component…
SSIS – Fastest data flow task item for transferring data over the network
My goal was to find out which combination of data flow Source and Destinations would enable me to transfer data over a network as quickly and as efficiently as possible. · Below is what the outcome of the tests are · And then finally I have only detailed, based on the fastest data flow items how to configure the data…
SSIS – Automating SQL Server Backups, copying files and checking that they were copied
I had an issue where I lost all my backups. So what I have now created is an automated SQL Server backup process, which will back up all the databases, verify the backups, copy them to the required location. And then finally email the backup files copied to ensure that they are all there. …
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…
SQL Server 2012 Integration Services Error Creating Catalog Database
This is the error that I got when trying to create the SSIS Catalog Database in SQL Server 2012. The certificate, asymmetric key, or private key data is invalid. Changed database context to ‘SSISDB’. (Microsoft SQL Server, Error: 15297) Just a quick background to how I got to the error when creating the SSIS Catalog database in SQL Server 2012….
DAX Function error – An argument of function ‘LEFT’ has the wrong data type or has an invalid value
Today I was working in SSAS Tabular Mode for SQL Server 2012. What my goal was, was to remove version numbers from Products. This was so that instead of having the following below in a report: Microsoft .Net Framework 1.x Microsoft .Net Framework 2.x Microsoft .Net Framework 3.x Microsoft .Net Framework 4.x I would have one name instead: Microsoft .Net…