July 2007 - Posts
Thought I’d share with you something strange I found while using named queries in the DSV layer in SQL Server Analysis Services.
I have developed quite a complex layer of connected tables in my DSV. As development had evolved (with the customers demand) I found myself turning 3 of my regular tables into named queries. The named queries were basically using the same table twice in an inner join to compare the state of the entity in two different times in the table. After this change, my process time for the cube and the dimensions grew from about 5 minutes to 20 (!)
After a while I found myself adding yet another named query to my DSV. This time, the named query included a few tables inner joined. My process time for the cube and the dimensions grew to over an hour(!) I admit I even stopped the process so I don’t know the total time, but just the cube itself demanded about 45 minutes… needless to say, quite disturbing.
So what’s left to do? Check the SQL query working in the background of the SSAS engine. Seeing the query used is easy – all you need to do is click on the dimension > attribute > sql query or click on the measure group > partition > sql query. After double clicking the "select" statement under the "sql query" and you can copy the query.
Next, you take the query and run it inside a new query in the Management Studio using the relevant database. Run the query with the Estimated Execution Plan which can show you what the engine of the SSAS is actually doing in the background while building the attribute / measure.
I found the engine runs my named queries used a lot of scans and their order wasn't right (it started with the big tables first).
So what’s the solution? Well, in my project at least, the only immediate solution seemed to be to build actual tables out of the named queries. And my processing time shrunk back to 5 minutes again! J
About a week a go (I admit I'm only catching up to it now…) Microsoft released the SQL Server 2005 Best Practices Analyzer. The tool analyzes and monitors the SQL Server Profiler files. It uses a predefined list of recommendations to determine if there are potential issues in the database environment.
Now if you're like me, then after downloading and installing the tool, you can't help but think to yourself "yeah great, but how do I use it?..." Well all you need to do after that is run the SQL Server Profiler and run the program on the trace files. Enjoy!
Well I admit I started writing this blog so I could be in touch with a larger community, but truth be told, I’m finding this blog has become a means for me to write stuff down somewhere so as not to forget them… J
Let’s face it, even at the age of 27 I have a hard time remembering everything. So what can I do? Notebooks with lists always seem to magically disappear or simply not be there just when you need them. Web pages, on the other hand, are always accessible – any place any time – and never seem to fade with time. Not to mention the fact that they’re also easier to search…
So my personal tip to you? Blog! At the very least, it’ll help you not forget stuff ;)
Though it might also make you develop an obsession with seeing how many people checked you out this time…
Today I thought I'd tackle a subject even more complicated that SSAS Best Practices (yes, it's possible!), so today's topic is BI in the government (though you were probably quick enough to get that from the title..)
As you may have read in my first post, I work for the Tel Aviv Jaffa municipality. Panorama hosted an event not too long ago with a panel. It so happens that Avi Ketko, head of the whole computing service in the Tel Aviv municipality (which would translate into my boss's boss's boss J) said something in that panel which didn't go down all that well. What was it? Well, he said something along the line (and this is by no means a direct quote!) of a municipality, as a non profit organization, does not strive to better it's "bottom line" as other businesses out there do. And after that everybody seemed to have given him a sort of stare..
So I thought I would talk about how that was somewhat misunderstood. Because you see, I think first and fore most, that our customers in the TA municipality do gain an advantage from us (else I would probably be out of a job, but that's another discussion ;) ). We have developed cubes, and reports on those cubes, which have helped our customers in the municipality understand where they might have gone wrong and where they may cut back. For instance, one of our projects in the subject of property tax has helped find more easily those who don't pay their money on time and alert us on them (yes, we know who you are!!). Working on the HR cube, on of my customers' needs was to locate those jobs which have been open for quite a while and re – consider them (as if they have been open for more than a year, they might be irrelevant). Both of these topics help make a better "bottom line" for the municipality.
One of our other next main goals is to start publishing materials outside of the municipality – to our everyday inhabitants. The CEO of the municipality wants to further the concept of openness and clarity to all our real customers – you, the residents of TA, and just every other person who was looking through the municipality internet site. In that, I believe, lies the real genius! As our customers are not just the people who work in the municipality itself, but also people who might just be curious as to what's going on in their city!
So basically, I would say that though we don't have a financial report we hand to the stock exchange, we are still dedicated to bettering ourselves and making our customers – you – better informed!