Press "Enter" to skip to content

Author: Kevin Feasel

SSRS Mobile Report Drillthrough

Published 2017-03-23 by Kevin Feasel

Patrick LeBlanc shows how to drill from a mobile SQL Server Reporting Services report to a paginated report (built on Analysis Services):

17. The report appears but does not execute because the parameters are not set. Why not?

Well, after inspecting the URL (http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fHigher+Education+Solution%2fReports%2fAnnual+Enrollment+Details&DateSchoolYear=2007&Term=Spring), it passed the values as expected. What is the problem? Remember, the parameters are populated from and SSAS model, so that means we need to send the values formatted as such. This format is:

[TableName].[Attribute].&[Value]

No problem, just build that string as part of the URL. Guess what, that doesn’t work either. What you need to do encode certain characters in the URL. For example, to pass year it needs to look like this [Date].[School Year].&[{{SelectionList.SelectedItem}}].

Click through for a step-by-step guide.

Comments closed

Understanding Thread-Local Storage

Published 2017-03-23 by Kevin Feasel

Ewald Cress explains thread-local storage and its relationship with SQL Server workers:

So far, this is all rather abstract, and we’re just seeing numbers which may or may not be pointers, pointing to heaven knows what. Let me finish off by illuminating one trail to something we can relate to.

Out of the five local storage slots which contain something, the first one here points to 00000000`3b656040. As it turns out, this is an instance of the CCompExecCtxtBasic class, and you’ll just have to take my word for it today. Anyway, we’re hunting it for its meat, rather than for its name. Have some:

Click through for details, including a graphic.

Comments closed

Visualizing Market Basket Analyses With Power BI

Published 2017-03-23 by Kevin Feasel

Leila Etaati explains how to use Power BI and a Force-Directed Graph custom visual to display results of a market basket analysis:

By clicking on the “R transformation” a new windows will show up. This windows is a R editor that you can past your code here. however there are couple of things that you should consider.

1. there is a error message handling but always recommended to run and be sure your code work in R studio first (in our example we already tested it in Part 1).

2. the all data is holding in variable “dataset”.

3. you do not need to write “install.packages” to get packages here, but you should first install required packages into your R editor and here just call “library(package name)”

Leila takes this step-by-step, leading to a Power BI visual with drill-down.

Comments closed

xp_cmdshell Not A Security Risk

Published 2017-03-23 by Kevin Feasel

Kevin Hill makes a great point:

A stored procedure that, out of the box, is disabled and has no explicit rights granted (or denied) is locked down to everyone but those in the sysadmin server role.

If someone exploits your SQL Server via xp_cmdshell, its because you LET them, either by granting permissions or by putting someone in sysadmin that clearly should not have been there.

For this in more detail, check out Sean McCown’s post from 2015.

Comments closed

Zero, One, Close Enough

Published 2017-03-23 by Kevin Feasel

Kendra Little points out a columnstore optimization which leaves a strange execution plan as a result:

I have a very simple query. It’s running against a table with a nonclustered columnstore index.

SELECT COUNT(*) FROM pt.FirstNameByBirthDate_1966_2015;

GO

The query returns one row, as expected. Here’s my count:

For the record, that is the correct number of rows in the table. Here’s where things get weird. In the actual execution plan, the columnstore index returns zero rows.

Yes, this is really the actual execution plan. I’m not tricking you, I promise.

Click through for the answer.

Comments closed

Kinesis vs SQS

Published 2017-03-22 by Kevin Feasel

Kevin Sookocheff compares and contrasts Amazon’s Kinesis and SQS offerings:

Complicated Producer and Consumer Libraries

For maximum performance, Kinesis requires deploying producer and consumer libraries alongside your application. As a producer, you deploy a C++ binary with a Java interface for reading and writing data records to a Kinesis stream. As a consumer, you deploy a Java application that can communicate with other programming languages through an interface built on top of standard in and standard out. In either of these cases, adding new producers or consumers to a Kinesis stream presents some investment in development and maintenance.

Click through for the full comparison and figuring out where each fits.

Comments closed

Multi-Joins In SQL

Published 2017-03-22 by Kevin Feasel

Vladimir Oselsky hits on something I dislike:

My first gut reaction was that this code is broken and would not run. To my amazement code ran just fine. Now came the hard part, which was to figure out what the code was doing because I have never seen this syntax before. Since I did not understand what I was looking at I could not BING “weird join syntax” to get an answer. As a developer, I learned long time ago to break down code into smallest possible chunks to get the answer.

After I have figured out the relationship between tables, I was able to understand what query was doing. To be able to read query better it can be rewritten in the following way.

Do read Vlad’s post.  I’ve seen terrible misuse of this plus right outer joins, and I don’t know if I’ve ever seen a case where using this syntax made the code easier to understand.

Comments closed

Hadoop In The Trough Of Disillusionment

Published 2017-03-22 by Kevin Feasel

Alex Woodie has an article about companies moving away from Hadoop:

Instead of trying to fit all the barnyard animals into the name (Cutting suggested Hadoop + Hive + Hbase + Spark + all the others omnivores, as well as “Cutting Con,” which maybe actually would have worked), the conference organizers went back to the roots of the Strata conference in 2011.

(Note to self: it’s ALL about the data.)

That doesn’t mean Hadoop is irrelevant. We will need a place to land unstructured and semi-structured data. But when the biggest Hadoop distributor removes the name of Hadoop from its flagship conference, it’s clearly an indicator that things haven’t gone quite as expected.

I’ve seen several articles along these lines lately and couldn’t resist the Gartner callout.  I consider this a helpful antidote to the “Technology X will solve all your problems!” marketing nonsense, which followed the “Technology X will solve all my problems!” developer nonsense as developers find new and shiny toys.  People are realizing where Hadoop is a great solution and where it’s a bad solution, and the same goes for other technologies; my hope is that after another 9-12 months of “Is Hadoop doomed?” types of articles, it’ll settle out into a long-term growth pattern where people understand its appropriate uses.

Comments closed

Entity Framework Slow, News At 11

Published 2017-03-22 by Kevin Feasel

Jovan Popovic shows that Entity Framework is slow and Dapper is fast:

To setup test, you can go to StackExchange/Dapper GitHub an download source code. Tests are created as C# solution (Dapper.sln). When you open this solution you can find Dapper.Tests project. You might need to change two things:

  1. Connection strings are hardcoded in Tests.cs file with values like “Server=(local)\SQL2014;Database=tempdb;User ID=sa;Password=Password12!”. You might need to change this and put your connection info.
  2. Project is compiled using dotnet sdk 1.0.0-preview2-003121, so you might get compilation errors if you don’t have a matching framework. I have removed line: “sdk”: { “version”: “1.0.0-preview2-003121” } from global.json to fix this.

Now you will be able to build project and run tests.

Nothing’s going to be faster than hand-crafted, well-tuned statements from people who know what they’re doing.  Micro-ORMs like Dapper and FSharp.Data.SqlClient will trade a little bit of a speed hit for developer niceties.  Heavier frameworks like Entity Framework and NHibernate add a lot more, but tend to be significantly slower.

Comments closed