Press "Enter" to skip to content

Month: October 2019

Speeding Up Excel Pivot Table Performance

Published 2019-10-28 by Kevin Feasel

Chris Webb shows how you can improve performance of Excel pivot tables hitting Analysis Services Multidimensional models:

Back in 2016 I wrote the following blog post about changes to the way Excel 365 generated MDX queries for PivotTables connected to Analysis Services, Power Pivot/the Excel Data Model and Power BI datasets:

https://blog.crossjoin.co.uk/2016/07/08/excel-2016-pivottable-mdx-changes-lead-to-big-query-performance-gains/

I know it sounds boring and not something you need to worry about but trust me, this is important – these changes solved the vast majority of Excel PivotTable performance problems that I encountered when I was a consultant so you should read the above post before continuing.

Unfortunately, earlier this year these changes had to be partially rolled back because in some rare cases the queries generated returned incorrect results; this means that you may find that values for subtotals and grand totals are again being returned even when they aren’t being displayed. The good news is that you should still be able to get the improved performance with a few minor tweaks.

Read on to see what those tweaks are.

Comments closed

Against Premature Re-Architecture

Published 2019-10-28 by Kevin Feasel

Cyndi Johnson has a good rant:

One of my biggest pet peeves in software development is the compulsion that so many developers have to rip up the foundation and completely build something over again, pretty much from scratch.

I’ve been that developer plenty of times. It’s easy to walk in, see that there are some problems, and want to raze everything. Sometimes that’s a reasonable answer, but every apparent mismatch or hack was put in to solve a particular business rule, many of which are lost to the mists of time. Burning down and starting over loses a lot of that information, so rebuilding is something you do with caution.

Comments closed

On Quantum Supremacy

Published 2019-10-25 by Kevin Feasel

John Cook has some thoughts on Google’s quantum supremacy announcement:

Google announced today that it has demonstrated “quantum supremacy,” i.e. that they have solved a problem on a quantum computer that could not be solved on a classical computer. Google says

Our machine performed the target computation in 200 seconds, and from measurements in our experiment we determined that it would take the world’s fastest supercomputer 10,000 years to produce a similar output.

IBM disputes this claim. They don’t dispute that Google has computed something with a quantum computer that would take a lot of conventional computing power, only that it “would take the world’s fastest supercomputer 10,000 years” to solve. IBM says it would take 2.5 days.

If you want to jump the gun but also stay on the Microsoft stack, the Q# programming language is open-source and you can run a simulator on your machine. Manning also has a Q# book in the works.

Comments closed

Task-Based Effectiveness of Visualizations

Published 2019-10-25 by Kevin Feasel

Adrian Colyer summarizes an interesting IEEE paper:

So far this week we’ve seen how to create all sorts of fantastic interactive visualisations, and taken a look at what data analysts actually do when they do ‘exploratory data analysis.’

To round off the week today’s choice is a recent paper on an age-old topic: what visualisation should I use?

No prizes for guessing “it depends!”

…the effectiveness of a visualization depends on several factors including task at the hand, and data attributes and datasets visualized.

Is this the paper to finally settle the age-old debate surrounding pie-charts??

The results were very interesting, though as an official Pie Chart Hater, I would point out that in none of their results was a pie chart ever better than a bar/column chart. There are cases where it works out okay, but if it’s never better and often worse than something, I’d rather use the alternative.

Comments closed

Database Restoration and the Plan Cache

Published 2019-10-25 by Kevin Feasel

Andy Mallon has some tests for us:

If you restore a database, what does that do to the plan cache? Well, let’s start by looking at the documentation for RESTORE. (Emphasis mine)

Restoring a database clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. For each cleared cachestore in the plan cache, the SQL Server error log contains the following informational message: ” SQL Server has encountered %d occurrence(s) of cachestore flush for the ‘%s’ cachestore (part of plan cache) due to some database maintenance or reconfigure operations”. This message is logged every five minutes as long as the cache is flushed within that time interval.

Yikes. That first sentence sounds like it is going to clear the cache for the entire instance.

Read on as Andy tests this and (spoiler alert) changes the documentation.

Comments closed

Ordering in Cosmos DB Queries

Published 2019-10-25 by Kevin Feasel

Hasan Savran shows how you can order data in Cosmos DB queries:

If you need to use multiple properties in your ORDER BY then you need to define COMPOSITE INDEXES.For example when I try to run the following query and try to order the objects by CreatedOn and Score, I end up with an error because I do not have a COMPOSITE INDEX to use with this ORDER BY.

Many parts of Cosmos DB’s SQL syntax are similar to T-SQL, but some of the underlying assumptions—such as, what you need to order data—are quite different.

Comments closed

Auto-Deleting SQL Agent Jobs

Published 2019-10-25 by Kevin Feasel

Dave Bland takes us through SQL Agent job auto-deletion:

Have you ever looked at something in SQL Server and wonder why it is there?  That is what I think when I see this option in the SQL Server Agent job properties.  I can not come up with any good reason of why you would want a job to delete itself upon completion.  I even did a Google search and really didn’t find a good reason.  However, if you know of a great reason of why you would want to enable this, I would love to hear about it.

I’ve used it in the past for scheduling ephemeral work, particularly when I didn’t have the ability to control operations otherwise. For example, I need to perform a time-consuming one-time update on data, but I don’t want to tie it to a script on my machine because I wanted to go home that night. Creating a job which auto-deletes upon success lets me schedule it for when I want it to run, kick off the script, and not leave a mess behind in the SQL Agent jobs list. It’s a case where I don’t really care about history and checking the box gives me a quick indicator of success: if the job’s gone in the morning, my work here is done; if not, I need to begin troubleshooting.

3 Comments