A Fine Slice Of SQL Server
This is more of a heads up for me / reminder regarding some of the caveats to using ONLINE / RESUMABLE index operations with SQL Server.
Don’t get me wrong, there’s a ton of advantages to using ONLINE and RESUMABLE which I will highlight below but here is the link to the Microsoft Learn page if you want more in depth information
Read on for the pros and cons. I do like the idea, though I personally haven’t used the feature.
Comments closedPrathy Kamasani gives us options:
A colleague of mine came to me with an interesting use case, “ Switch between a summarized value or selected value with multiple legends”. For example, I have five countries and their GDP values. When the end user goes to the report, the user would like to see the average GDP of all countries, but when the user selects single or multiple countries on the slicers, the line chart should show only selected values.
Click through for the guide.
Comments closedPaul Turley sends us an e-mail:
Using common language is critical but often trivialized, when describing requirements, deliverables and project expectations. When people are working together, depending on each other to complete important tasks, they must have a clear understanding of the common language and terminology. It is usually only after a word, phrase or abbreviation has been used with an assumed meaning that we realize the error and have gotten ourselves into trouble. Often, on a daily basis, I review project proposals and requirement documents containing inaccurate language related to Power BI project work. I also getting a lot of virtual eye-rolling when correcting seemingly inconsequential language. But I can also cite many cases when subtle misinterpretations became costly mistakes.
This is at a higher level than naming measures or dimensions.
Comments closedErik Darling notes a problem with time-based filters on operations:
There are six waitfor commands that each pause for 1 second. In between them are queries that finish in milliseconds.
If I watch the event output, eventually, I’ll see this:
Okay, so the stored procedure took more than 5 seconds, but… no individual query took more than 5 seconds.
This happens a lot and I’m not sure there’s a good answer for it.
Comments closedAmy Esselman hits on a tricky problem with dashboards:
As powerful and as useful as dashboards are, they’re optimized for the exploration of data, not the communication of specific insights. Once we’ve used our dashboards to uncover something worth sharing, we’ll usually be better served by making a separate presentation, designed to bring the findings to light and get others to act upon the information.
The path from dashboard to story might not always be intuitive. This article will use a dashboard from a recent storytelling with data engagement to illustrate how to transform dashboard insights into an action-inspiring story.
This is especially important if you’re a developer who understand the value of storytelling but doesn’t quite know how to support it while showing arbitrary data.
Comments closedItzik Ben-Gan shows a good use of a pair of new T-SQL functions:
Comments closedTime-based grouping and aggregation are common in analyzing data using T-SQL—for example, grouping sales orders by year or by week and computing order counts per group. When you apply time-based grouping, you often group the data by expressions that manipulate date and time columns with functions such as YEAR, MONTH, and DATEPART. Such manipulation typically inhibits the optimizer’s ability to rely on index order. Before SQL Server 2022, there was a workaround that enabled relying on index order, but besides being quite ugly, it had its cost, and the tradeoff wasn’t always acceptable.
Teo Lachev builds on a prior post:
This blog builds upon my previous “Resolving Tabular Conversion Errors” and applies to Analysis Services in all flavors (Power BI, MD, and Tabular). In the scenario I described in the previous blog, the server at least told us the name of the offending measure in the error description. But sometimes you might not be that lucky. For example, recently I got this error when running a DAX query requesting many measures: “Microsoft OLE DB Provider for Analysis Services.” Hresult: 0x80004005 Description: “MdxScript(Model) (2000, 133) Failed to resolve name ‘SYNTAXERROR’. It is not a valid table, variable, or function name.” All we know is that there is a syntax error in some measure but good luck finding it if you have hundreds of measures in the query and your model. However, the (2000,133) section references the line number and column number in the MDX script (Yeap, MDX even if you use Tabular), so if we can get the script, we might be able to correlate the error.
Read on to see how you can capture that script and get the error. The technique is definitely not intuitive.
Comments closedChenye Charlie Zhu announces a new feature:
By default, Azure Data Factory reports lump sum charges for billing, meaning that at the factory level, we add up charges across all pipelines within a factory, and tell you how much you have spent on these pipelines. In many cases, these aggregate numbers should suffice. But in others, these numbers lack the clarity and transparency that we thrive to provide customers. For instance, if you are running data pipelines for multiple teams, you may want to determine the cost for each pipeline, for proper book-keeping and/or charge backs.
Now, Azure Data Factory will help you with this endeavor, with built-in per pipeline detailed billing view. Moreover, we built the feature on top of the Azure Billing and Cost Analysis platform, allowing you to stay with the cost and budget management tool that you are familiar with to identify spending trends and spot where overspending might have occurred.
Great if you have half a dozen pipelines. Probably less great if you have 500.
Comments closedGarry Bargsley shows a use for extended properties on databases:
Databases listed with 1, 2, 3, 4? What do those databases represent? Where did those databases come from?
Would you believe me if I told you there was a way to identify where the databases were sourced from?
A setting that can be used in SQL Server would allow the person who built these databases or restored them from another system to add a note.
Extended properties are very useful but also very easy to forget and difficult to manage. I’ve seen a few vendor products make great use of them but in-house development tends to ignore them.
Comments closedGilbert Quevauvilliers reads the chain of custody documents:
As often happens blog posts originate from a customer requesting something I have not figured out before.
In this example the requirement was to be able to determine which data sources were being used by which tables, which were then associated to one or many Power BI datsets.
While I was working through this I figured out I could take it one step further and also if required have the actual Power Query as part of the report.
Read on to see what Gilbert came up with.
Comments closed