Getting active with Active Directory

FitbitSO reality hit yesterday, we needed to import the data from all a clients Active Directories – not many by any standards and all 2008 I still have nightmares about uncovering forgotten NT4 domains that are “business critical” – anyway you can use Power BI Desktop to easily import multiple Active Directories and then merge them into a table for reporting, that all works really well and is frankly a breeze. You say you want to use an Active Directory, give it some credentials and then select where and what you want to import. If you need to combine data then you just merge your queries.

So why are you wasting our time by making us read this?

It all falls apart when you start to set it up into an App ready to publish it. The Enterprise Gateway (On-Premise Gateway) doesn’t support active directory connections, so you have two options

  1. Hire someone to manually refresh reports all day – This is a great fun job and I’m sure your staff retention will be sky high
  2. Use a single Windows account for everything (that needs Windows accounts) including logging into Power BI and all your data sources – I can hear the panic in anyone with a security background reading this

Well neither of those options work for my company! So we chose not to choose naff, we chose life instead

Read more

My Ultimate Date Dimension! at least until I figure out a better one….

As you progress in Power BI (and Power Pivot), you quickly find you need a date dimension, the problem is that date dimensions are difficult to build and even more difficult if you want them to be more than just a list of dates.

“Stop I’m confused… how can a list of dates be more?”

The role of a good date dimension is indeed to be more than just a list of dates, there are functions in Power BI to produce a list of dates, the issue is that you want something that is more. I’ll be honest at this point before we dive into the queries, I use, I cannot remember where I got the original source, I found three or four different date dimension SQL queries initially and then started pulling them together and adjusting them to fit my needs. For me we were going to have the majority of our data coming from a SQL server so it makes sense to have a SQL date dimension, based on what we did and after doing some benchmarking within Power BI I would recommend housing it on a Database, putting it in Power BI or excel won’t stop things working, but I have got best results from this method.

The important thing about the first bit is that there are many, many scripts out there to do what you need. Typically they have the same format.

I’ll make these source files available to download

  1. Define Table
  2. Populate Dates Dimension
  3. Deal with 1 character days and months
  4. Set Fiscal Year Months – where I work we use August to July so I needed to add a case statement here, case statements sound more complex than they are
  5. Set Fiscal Year
  6. Set Fiscal quarter
  7. Set Fiscal week

Read more

A plan forms

A plan forms

Like many companies the company I work for has concerns about the cloud, like most we’re certain that if any of our data ever touches the cloud then childen will be sacrificed and hostile states will take ownership of all our secrets. Maybe there’s some truth in that or maybe they’ve been watching too much “Mr Robot” wait does that mean I work for Evil Corp?

I’m sure the truth is somewhere between the two extremes no matter. The argument is a common one and it is one that really Microsoft entered the market place I believe unprepared to really deal with – sure the late data aquesition of DataZen kind of went some way to showing there was an intent at on-premise and of course Piramid (still never actually seen this up and running or working, could it be a myth (am I talking to you? Do you understand me or is this just a lame attempt at 4th wall interaction). Mind you Microsoft’s own book kind of spells things out the best I’ve seen so far. My personal version is that SSRS was losing ground to other ERP solutions SAP and Oracle really pushing thier own solutions, SQL based Data Warehouses were starting to really suffer, they’re cumbersome and the cost of ownership has increased as SQL licence requiremenrs have increased,  six figure sums just for SQL licences for solutions make them costly and the benefits are hard to realise. Then everything changed… my guess is around 2010 challengers started to come up. Suddenly it was possible to do things without the established vendors I remember in 2007 writing an Access Database that services circa 10’000 users. I’m not going to claim it was particularly good or that it was massively transactional, but when compared to the traditional view at the time of “buy a SQL licence” was just wrong. Some work and some thought and it was invalidated. That was just my personal experience. Looking at the market out there, solutions becan to use SQLite or MySQL or whatever the customer was running, mean while the ERP giants of Oracle and SAP continued to expand the application usage within their customers again as people consolidated their applications and looked to maximmise their investment.

2012 (or there about) cloud began to pick up, yes it still took another couple of years but the cloud solutions started to come up in 2012, Enterprise level organisations started to discuss private cloud and what that would mean for them. Into this environment and at this time Microsoft decides to reinvent it’s BI strategy, but quietly (SQL Enterprise licences are still a great seller, why risk that too soon and of course risk your business until you’re sure). So quietly Microsoft launched what I always think of as the “Power Suite” for Excel, PowerPivot and PowerMap initially, then PowerQuery and Power View were added. The purpose of this was as will tell you, a new way of dealing with data was needed.

Wowsers Ross stop prattling on …is that another 4th wall interaction?

Ok so where are we? Yes the paranoia of a business these days is around putting data in the cloud so PLAN A: is to not use the cloud storage – “Eat my shorts Dark Army” – our data will remain in our data centres BUT all queries will have to travel across the WAN. PLAN B is to use Microsoft Cloud Storage, but having the end customers demand the cloud storage is better than me suggesting it.

The first draft of my 3rd Data Model is already in play and goinART3.0g through testing, so far it’s not too bad. Direct Query to SQL seems to be ok with Power BI Desktop – just awaiting our clearence for Power BI Pro to do true gateway testing via the cloud. So far our Incident Managment, Request Fulfilment and Satisfaction Surveys are able to be reported against. Make no mistake using Direct Query requires much more planning, but the rewards are there. I’ll keep posting more as I get further into the whole Direct Query rabbit hole!

Keep on developing folks!

Too many Dates?

Real world item often have more than one critical date, Orders will have an Ordered Date, Dispatched Date, Delivered Date etc… In Incident Management the core dates to begin with are Created Date and Resolved Date, but there may also be Status transisiton dates i.e. On-Hold/Off-Hold/Assignment Changes etc… I struggled trying to work out how to fit this into a “Narrow your data” mentality. In the end I tried a completly different solution in 2.0. I pivoted the dates in my attempts at PowerPivot godness I probably went a little too far – I couldn’t get the original idea to work.

Pivoting the dates wasn’t as bad for me as it may be for you, I was able to do it on the SQL server, I created a view from the original source of the tables, that view pivoted the data and then PowerQuery imports the view, drops the mic and walks off like a pro

SQL Source – I wrapped mine in a create view statement

SELECT [Incident GUID], DateSwitch.DateValue, DateSwitch.DateType
FROM TblAll_Incidents
UNPIVOT (DateValue for DateType in ([Created Date], [Resolved Date], [Closed Date])) as DateSwitch

I’ll break it down because I know I promised not to get overly technical. I’ll also share a secret – GUID, if you talk to DBA’s or System Architects long enough you’ll come across this term and they’ll invariably try and look down their nose at you if you ask what it means so I’ll tell you. GUID stands for Global Unique IDentifier. Simply put a GUID should be unique within the system – if not the whole wide world. They are 16 digit strings of numbers and are semi-randomly generated (I’m not 100% on the science of their generation) basically though when someone says they’re going to use GUID’s it means they’re going to set the Database up properly, numeric 1,2,3,4… indexes not only repeat for each table they also commit the biggest sin in database terms… the change width, 1, 10, 100, 1000.

For those old enough to remember it that was the worry about the Millennium bug “How will a computer cope if it has to put 100 (three digits) into the space for 99 (two digits)?” Millions if not Billions was spent so we wouldn’t find out the hard way.

The UNPIVOT section is going to get me into trouble, basically it’s going to turn a column into a row in SQL PIVOT would turn a row into a column, in essense though I’m sticking to what I said, I pivoted the dates using the UNPIVOT command in SQL. So we’re turning a column into a row, remember though at any given point in the query we’re only looking at one row. That means our single row will be slit into the number of rows you mention in the the UNPIVOT. Our Tickets have three dates [Created Date], [Resolved Date] and [Closed Date] so our unpivot turns our one row into up to three (not all tickets have been resolved or closed remember.


Suddenly there was a way of really pulling the column counts down for the imports. The second step occurs in PowerQuery. When importing that DateValue column change the datatype to just be Date – you lose the time but the reality is for most reports you won’t use it. Retaining the time in the SQL though means that IF or in my case more likely WHEN senior management say “We want to know what time tickets were created” you can handle it

I got too keen when I first hit upon this and tried to combine all the dates from all the processes I needed to report – they’re still like that in the SQL, however PowerPivot (or more likely my skills) let me down. If you work out how to combine the dates from a single FACT table into a single “Date Pivot” please let me know!