Relocating to Australia – BI Job opportunities in Queensland
I thought I would let you guys know that I am about to relocate to Australia from South Africa. I have had an amazing time in South Africa, and learnt a whole lot whilst working at my past employer. So this is a plug at anyone who has any lead or potential BI work in Queensland, Australia. I would really…
SQL Server Analysis Services (SSAS) – Updating Project with Partition information
I am sure that this has happened to someone else before. You are making a change to your SSAS cube, within your SSAS cube you have created your initial partitions. But on your production server you have programmatically added additional partitions. Now by mistake or just not thinking you deploy your project, and when it prompts to overwrite your current…
SSIS – Using Date and Time Cache Lookups for faster inserts into Fact Tables
What happened is that I found that when doing inserts on larger data sets, it would take a long time to complete the date and time joins. If I excluded this the data would be returned a lot quicker. So my solution to the problem was to use the Cache Transform and then the Loopkup Transform in the SSIS…
SQL Server 2014 Observations
Today’s post is not going to be about explaining something but more along the lines of my experience on SQL Server 2014. When I heard about all the new features in SQL Server 2014, the In Memory Tables, new cardinality estimator, new changes to how TempDB works, SSIS and Delayed Durability, I was excited to see how I could use…
SQL Server Integration Services (SSIS) – Using Send Mail Task for data validation checks
What I wanted to do was to be notified in my data if there were not enough rows of data, or if the financials were too low, indicating that there was a potential issue with the data coming in. And if it was too low to fire off an email. If it was correct then to not send an email….
SQL Server Integration Services (SSIS) – Re-run a specific Control Flow Item if it has an error
We had an issue where we only wanted to re-run a particular control flow item when it failed or had an error. We did not want to do this for our Entire SSIS Package but just for a specific Control Flow Item. Example: Inserting Data into a Staging Table and if it fails to then re-try the insert 3…
SSIS – Dropping Partitions in SQL Server Analysis Services (SSAS) with XMLA and Analysis Services DDL
Below are the steps that we have integrated into SSAS using SSIS so that we can then drop our old SSAS Partitions using SSIS and XMLA. Example: · We are going to drop our oldest partition from Measure Group called Fact InternetSales 1, which is in our Adventure Works cube. · The actual Cube partition name is called: o …
SSAS (SQL Server Analysis Services) – Getting all partition information from SSAS Database
I had a requirement where I wanted to find out and keep a constant record of exactly how my partitions were set up and created within SQL Server Analysis Services (SSAS). So below is a script that I found somewhere (If I find the source I will put it in hereJ) and how I inserted into a SQL Server Table…
Excel–Power Query–Automating SSIS job duration report
Following on from our query to get our SSIS Job duration information, I decided to start using Power Query and see if I could use it to facilitate the automation of my reporting requirements. Detailed below is how and what I archived. Example · We are going to use our SSISDB to see if there have been any changes in…
SSIS – Package duration time
Just a quick blog post today. I wanted to see how long all my packages were taking to run in SSIS on SQL Server 2012, due to there being some storage issues, which related to the jobs taking longer to run. So below is the query that I used to pass my parent package, so that I could then get…