Business Intelligence

I had previously created a SSIS package with a simple Process Full on the SSAS MD database, however, as the project has progressed this hasn’t been ideal. Its basically all or nothing. In order reduce the damage a failure can cause I’ve setup a Process Full for each dimension and cube so each is processed independently of each other. I’ve used the data from the Analysis Services Dynamic Management Views, or DMV for short, which I’ve used for the documentation to feed the foreach loop....

So today I went to run a SSIS package on my new laptop and bam, error message. Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The package failed to load due to error 0xC0011008 “Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.”. This occurs when CPackage::LoadFromXML fails. —> System.Runtime.InteropServices.COMException: The package failed to load due to error 0xC0011008 “Error loading from XML....

Power BI is Microsoft new cloud based Business Intelligence platform, one of the cool things about it is it built atop internet standards and leveraging open source libraries like D3.js for its visuals. Microsoft has continued on the path of open source-ness by open sourcing some of its codes allowing Joe Public to submit custom visuals to Power-BI, all via GitHub. Yesterday saw the introduction of 4 new visuals, one in particular looked very interesting – Hierarchy Slicer by Jan Pieter Posthuma....

Another day, another problem. Todays problem was incorrect column length which was causing a SSIS package to fail. Historically I use a freeware tool to compare the different environments schema, but lately I just don’t bother, I have continuous deployment so its easier just to hit the deploy button then it is to try and figure it out. Well today I decided I wanted to it old school – although I’ve been using the SSDT SQL compare in my deployments, I’ve not actually used it in terms of viewing the differences....

There are various types of Auditing in the Microsoft BI stack. There is auditing in SSRS, SharePoint, SSAS and not forgetting SQL has its own auditing. Today I am looking at the SSAS auditing – you can find out more about it on TechNet. Olaf Helper has published some TSQL code for querying the audit data -on the Script Center. But first, we need a table to store the data, here is a TSQL script for creating the table:

So eariler this month I switched from my Azure hosted TeamCity server to Appveyor, which freed up some much need Azure credit. This month, it looks like its going to be going on a Visual Studio Ultimate vm for some SharePoint development. Hopefully this won’t take too long to do as I really want to get on with machine learning after a demo from Ric Howe. I’m also looking at building a few test machines locally – old school I know!...

Just thinking out load (because its good to get feedback) Just to expand on my tweet. I’ve lot of SSIS packages for load and building my BI warehouse (SSAS cubes), now currently I have a SQL job that has multiple steps For example Load data from Source system into Staging Build ODS intermediate tables Build Warehouse tables And this works for DataMart where they are a denormalized copy of a single source system, but when the source system, or the intermediate tables, are used multiple times, you don’t want to run them multiple times....

I recently discovered the ability to reference other database project (I know, I know), anyway, this cause my lovely automate build\deployment process to fail. Investigating the error led me to, yes, you guessed it a Jamie Thomson’s blog (from 2012). Thankfully, I managed to figure out a clean solution, the DacPac solution on Octopus Deploy library has an option to pass Profile Name, which is the publish profile XML file. He is the cut-down version I’ve used, just the missing variables

I’ve previously blogged about how I was going to detail how I build and deploy my Microsoft BI projects and to be honest, I’ve been putting it off. The main reason is my work has been looking at Visual Studio Team Services, formally Visual Studio Online and I was looking at using the Build functionality that is included. This should make the whole setup process a lot easier for anyone else trying to replicate my setup....

Anyone working with SQL Server will be well aware of the mountains of training material, blog posts, videos, code snippets, events and other helpful resources that helps anyone who comes into contact SQL Server regardless if they are a beginner or a seasoned veteran (even MVPs!). One of the pools of knowledge is SQL PASS. is an independent, not-for-profit organization run by and for the community. With a growing membership of more than 100K, PASS supports data professionals throughout the world who use the Microsoft data platform....

Today, my pull request for the SSAS deployment script was merged into the Octopus Deploy Community Library. So what is Octopus Deploy and why should I care? Octopus is a friendly deployment automation tool for .NET developers. But don’t be fooled. Octopus Deploy isn’t just limited to .NET developers deploying hip new cloud based applications, it can do much, much more. I currently use Octopus to deploy Jasper reports to our 6 HR environments, I also use it to deploy our HR interfaces which are a series of SSIS packages and SQL objects which uses Microsoft SQL Server 2008....

My current thoughts on SQL2016/PowerBI as deploying SQL2014. Ok, so we’re looking at moving to pure SQL 2014 for our corporate BI solution. I say pure as we normally use SQL server as the backend then other tools on top, so we’re looking at enabling a lot of extra features we’ve technically had for years but just not enabled\setup\whatever. SQL2016 is on the way and we’re most likely going to upgrade pretty quick, lots of new features that look very useful....

Todays problem was brought to you by a highly customizable system with poor user validation and a user who doesn’t know the difference between data and formatting. First the system. The supplier has created a highly user customizable system, they can for example create a web form with zero coding – 100% user config. The problem is, technically, it creates a mess. So your nice new form with a number of questions stores all its answers in a single table with the data going into a nvarchar(max) column – which is fine for free-text columns, but not so good for integer fields....

For a while now I’ve been going to the Cambridgeshire SQL Server User Group, which I have found really useful especially as we are at moving towards a more self-service model across IT which requires IT to be a bit more cleverer. Beyond the informative speaker talks you get a really good networking opportunity. The group is run by Mark Broadbent (@retracement) and has recently merged with SharePoint Saturday Cambridge. Although Cambridge is about the same time on the train as London....

I’ve spent a while today trying to change the text direction (orientation) of a header on a SSRS report – the option was rather oddly placed in Visual Studio – the option is within the properties (for the text) Localization > Writing Mode > then change from Default to either Rotate270 or Vertical. I was hoping it would be within the Font options. Guess that would be too easy 😉

Todays problem, missing the reporting data toolbox in Visual Studio 2013 – once again, a different menu options. Open the actual report (.rdl) View > Report Data Alternatively press Ctrl + Alt + D

Stupidly today, I accidently closed the SSIS toolbox. After a bit of unsuccessful searching in the Visual Studio menus, I gave up and done the IT norm and Google it – hoping I wouldn’t have to reinstall. Luckily I found James Serra had already blogged about it, the only difference is Microsoft has changed the icon, its still in the same place at least So when is a Visual Studio Toolbox, not a Toolbox?...

One really annoying bug in SSIS when using Visual Studio 2013 / SQL Server 2014 is the object jumping, so when you drag an object like a sequence container down the object shoots to the bottom. Thankfully the fix is simple. Close Visual Studio and apply SQL Server 2014 SP1 to your machine.

One of the problems we have is documentation and how to keep it up-to-date. We tend to deliver a new batch of reports as part of a CR (Change Request) then make changes as part of BAU (Business as usual) unless its a particularly complex or time consuming change. The problem is often BAU changes happen rapidly often within a short time frame and time isn’t always allocated to updating documentation....

It appears IBM has moved the Cognos backup folder location from: C:\Program Files\ibm\cognos\c10_64\bkp To: C:\Program Files\ibm\cognos\c10_64\uninstall

Our new HR system uses SAP Business Objects for transactional reporting, as this was setup by our HR supplier we haven’t really got involved with it beyond writing a few custom reports, turns out we’ve written quite a few, so much so that the Business Objects CMS database has swollen to over 30 GBs. This, as you might have guessed, is causing a few performance problems. After a discussion with the supplier, we found out it was storing all the reports, even the ad-hoc one-off reports and we should have set a parameter to limit it....

Two new Azure features have caught my eye – the first is Logic App. In a nutshell this is workflow. You have connectors and you can add triggers and actions. So you could have a trigger on your HR system that when a new employee starts, it creates a user account then sends a welcome message to the existing staff informing them of the new starters. The other is Data Catalog, this basically allows you to generate meta data about your data sources....

So in my last blog post I asked, are you ready for IPv6? The post came about when I was looking at Schools MIS data, which Graham, Joshua and myself have being look at to see who are the big movers and shakers in the Schools administration software (MIS) arena. Data is collected by what software suppliers a school uses to submit the School Census (in England) which is requested under the Freedom of Information (FOI) from Department of Education(DfE) (saving having to FOI every individual school)....

I always find it funny when IT departments talk about BI for their company and they don’t actually have a BI solution in place for themselves. Surely you should practice what you preach? So why do IT departments need BI. Well lets look at Rosetta@home. Jul 30, 2014 We are aware of significant network slow-down between the subnet upon which our servers sit and the Internet beyond the UW campus. We are working with the UW’s Network Operations team to pinpoint the cause....

In my day job I’ve been looking at SQL Server 2012 recently (I know SQL Server 2014 out before you say anything!), I’ve had a developer license for a while but unfortunately other priorities have been consuming my time and I’ve only managed to get a dev server build, SQL 2012 installed and not much else up till a few days ago. Our current Business Intelligence platform is using SQL Server 2008 (not R2) for the database backend and SQL Server Integration Services (SSIS) BIDS for the ETL....