Firstly, a very happy new year to all. All the best for your 2012, wherever you are!
I have been working as a consultant for around 9 years, and I have experienced a fair amount in that time. From my first client assignment (where I was almost too nervous to get out of my car in their parking lot!), right up to now where I have the pleasure of leading a consulting team… It’s interesting that the memories that stand out are those of the people I have been able to work with over the years. Some that were fantastic, and some that were less than fantastic (!)… I have been able to learn heaps from them all.
So, I wanted to begin this new year with a short series of lessons I’ve learnt from others, and I hope they will be a little amusing and maybe even helpful too…
So, let me begin with my consulting musing #1, whatever you do, “Don’t Panic”!
This really summarises my shuffles and changes through various roles. Every time that I would get comfortable in a certain scenario, like actually being able to get out of my car at client sites; another challenge would appear which would always seem slightly beyond my current skills.
Like the day I was asked to visit a client to assist with a particular MDX issue they were experiencing. Truthfully, at the time I could only spell MDX, never mind consult on it… But I had at least mastered the art of getting out of the car, so I bravely stepped into their offices and was led directly into a small room with about 10 people, and one chair and computer (which was for me)… Talk about the hot seat!!
An hour later, I had solved their issue, they were grateful, and I was relieved. Two key lessons from this one:
1. Listen. Yes, the basics, just listen. I sat for half an hour and just let them explain their issue, ask leading questions to clarify, and keep listening.
2. Remember that no man is an island. Once I understood the issue, I jumped on the phone to a senior colleague, distilled the issue to him, and was able to then convey his answer to the client. (which fortunately worked!!) This one gets tough, as it forces us to occasionally swallow our pride, and make use of the network around us. However, everyone wins in these types of situations. I learnt, my senior colleague was able to apply his own troubleshooting skills, and above all, the client got a good result.
So, relax, don’t panic, and have a little fun while you’re about it
For the past few years, ITWeb in South Africa have run a number of annual Business Intelligence (BI) conferences. In addition to this, they have run seperate conferences for Data Warehousing (DW).
Now, personally I have never really understood this approach of seperating the concepts. Basically this is implying that possibly BI != DW.
Based on my experience in delivering BI projects; I have always found that a good BI project absolutely requires a good DW component. Despite Analysis Services’ ability to conform data through the virtual world of the UDM (and I include PowerPivot and other 3rd party BI front end tools with similar claims in this discussion), pragmatism and the usual dodgy data quality issues have always dictated that best practise ETL and DW practises are needed before we expose data to end users.
In fact, I have always found the relationship to be so tight, that my internal framing of the world has dictated that BI == DW.
As with any good internal assumption or belief, it is currently being challenged. I’m involved in assisting an international company in developing a group wide reporting data warehouse. Now, the programme is so large, that our current scope of work is “simply” to consolidate the data from the inevitable wide variety of sources into a central data model. This stage of the project has dictated that the approach is very different to what I am used to. i.e. I am no longer directly involved in data delivery, so my usual toolkit of Excel, Strategy Companion, Performance Point and a bit if good old Business Strategy conversations are simply not in the picture.
So, maybe there is space in the world for the idea that DW != BI. (i.e. maybe ITWeb has a point after all)
So, maybe if we flip my formulas into logical implication statements, we could say:
BI –> DW
And although my current project does not necessarily dicate that DW –> BI, I would argue that for the long term success and acceptance of the data we’re consolidating, BI projects will need to be initiated to allow end users to dive into the data asset we’re creating.
Looking forward to being involved in that
A fairly high percentage of any developers life will require troubleshooting. And yes, I found myself back in that position with a recent install of master data services.
When trying to load the mds IIS site through the browser, the default.aspx would simply not render. So, the loading wheel would simply keep spinning in the tab, without returning any content. Occasionally I would be fortunate to get a ‘page could not be loaded’ timeout response, but most times the wheel would literally keep spinning.
This install was distributed, I.e SQL database was on separate server to IIS. Also SQL server was configured in non-standard port for security.
The install of mds ran successfully.
The config tool ran successfully, creating the site and db successfully.
But still, no page loading?
Turns out that the config tool drops the SQL port specified when storing the connection string to the web.config XML file. Update this string in the web.config file by hand, and suddenly the page starts loading as expected.
Posted from WordPress for Windows Phone
When uploading a PowerPivot model into an existing SharePoint 2010 Powerpivot Gallery, I received the infamous “an error occured while capturing snapshots” error, where the hourglass changes into a disturbing red cross.
Quite odd seeing as I had used this exact library in the past and successfully created snapshots.
Now, there were errors that were quite prevalent in the CTP builds of SharePoint and related to security and underlying services. So, I used this thinking according to Dave Wickert’s blog, http://bit.ly/eQGCWD. And my server passed all of the checkpoints, i.e. everything was setup correctly. I did check the ULS logs, and only found the generic Timeout Expired error.
So, in a move of desperation, I uninstalled Internet Explorer 9 Beta.
And the snapshots started working again!
Another reason not to experiment with IE9 just yet… g
I am excited to announce that I have accepted a position with Avanade Australia, as Group Manager for Business Intelligence, as of January 2011. Meaning that I currently have 46 nights left under African skies before my family and I jet off to the great Down-Under.
I will be based in Sydney, and will be looking after customers in Sydney, Melbourne and Brisbane.
I’m really looking forward to the opportunity to see more of our beautiful planet, meet new people, and have a hand in growing the Microsoft BI business in a new part of the world. (and to enjoy the New Year’s fireworks over the harbour bridge!!)
So, I had a fantastic time at TechEd Africa last week. I had the privilege of presenting two sessions, one of them being a session on DAX, the expression language for PowerPivot. In that session, I made the following statement:
“DAX time calculation functions; such as YTD, QTD, MTD; only understand Calendar time, with no support for custom Financial Time hierarchies”
In this blog post, I’d like to correct myself. Because, I wasn’t entirely correct
In fact, DAX does provide some support for custom Financial Time Hierarchies; as it allows for the last day of the financial year to be specified in a number of the time calculation functions.
e.g. let’s look at the typical Year to Date calculation for Sales Amount in Contoso.
The DAX version of this would look something like:
However, TOTALYTD also provides support for two optional parameters, namely Filter and Year_End_Date.
So, if we wanted to calculate the YTD for a financial year which is April – March, the DAX would look something like:
=TOTALYTD(SUM(FactSales[SalesAmount]), DimDate[DateKey], ALL(FactSales), “31 Mar”)
(so, the Filter basically does nothing, and the Year_End_Date is simply the date and month – yes I specify the month name as text to avoid regional setting issues! Also, including the year in the parameter is not required and won’t really make sense as we would want the expression to evaluate for any year)
The result of these statements looks as follows:
You’ll notice that the Financial YTD resets itself in April as required.
So, yes, there is support for Financial time.
However, I wasn’t entirely incorrect in my statement earlier, as this logic doesn’t strictly go down to the day level. So, retail environments which have custom trading months, such as 26th – 25th, are not currently supported by the time calculation functions.
=CALCULATE(MAX(Table1[Date]), (FILTER(Table1, EARLIER(Table1[Code])=Table1[Code] && EARLIER(Table1[Date])>Table1[Date])))
Note: I’m still coming to grips with the EARLIER function, seems to work semantically the same as an INNER join… will hopefully post a better explanation when I understand some more…
2. Using similar thinking, I could then join back to [Table1] and return the Value at the point in time established in the previous step.
(I would love any feedback if it’s possible to merge this with the previous step, may be more efficient)
=CALCULATE(SUM(Table1[Value]), (FILTER(Table1, EARLIER(Table1[Code])=Table1[Code] && EARLIER(Table1[DatePrevious])=Table1[Date])))
3. Finally, Profit is then just the difference between the current value and the previous value for the CLOSE rows.
=if(Table1[Position]="CLOSE", Table1[Value]-Table1[ValuePrevious], BLANK())
Results in the table below.