December 2, 2017 at 10:37 am
Ken Wymore - Saturday, December 2, 2017 10:15 AMMaybe that would be a good site survey question? How many people have SSAS installed but don't use it, rarely use it, use it often or don't have it installed? Other than the Stairway to MDX series, I rarely see anything about SSAS or MDX come up here (granted I haven't been checking the forums actively). Maybe I am in the minority concerning its importance and need to start looking into other solutions before it is completely phased out? Hope not!
Surveys get so few responses, usually a few hundred, so not sure this helps. I don't know if MS has this, but like you, I rarely see posts, blogs, questions, notes, anything on SSAS. I think some people use it really well and often, but I think they're a small minority compared to the relational engine.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 4, 2017 at 8:42 am
Steve Jones - SSC Editor - Friday, December 1, 2017 11:36 AMEric M Russell - Friday, December 1, 2017 7:53 AMMDS (Master Data Services) is one feature I wish Microsoft would deprecate. From a database engineering, management, and performance perspective; it simply sucks.I'd like to see some effort put into the Database Diagramming feature of SSMS. This thing hasn't changed a bit in 20 years. For example, the GUI could be more robust; providing better support for reverse engineering, cross database relationships, and also logical modeling in addition to just physical design.
I think many people wish MDS would receive improvements.
As for diagramming, I agree. I used to wish more third parties would do something here, but it's a hard problem with little commercial viability as a product.
My issue with MDS isn't lack of features but on a deeper level how the framework is engineered. The physical data model is poorly optimized and incomprehensible to understand, it's workflow is cursor based, and it doesn't scale. It needs some major refactoring.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 4, 2017 at 9:04 am
Jason A. Long - Friday, December 1, 2017 2:46 PMdrew.allen - Friday, December 1, 2017 2:37 PMJason A. Long - Friday, December 1, 2017 2:27 PMdrew.allen - Friday, December 1, 2017 2:06 PMJason A. Long - Friday, December 1, 2017 1:14 PMdrew.allen - Friday, December 1, 2017 1:07 PMJason A. Long - Friday, December 1, 2017 12:41 PMTop of my wish list...
#1 Inline Scalar UDFs.
#2 GREATEST & LEAST functions.
#3 that ability to use DISTINCT in windowed aggregates.
#4 add a "print" option to sp_executesql that will allow you see the exact code that is being executed (I'd settle for a separate sp_printsql as well).GREATEST/LEAST already exist. They're called MAX/MIN respectively.
Drew
No they don't... You can use MIN & MAX, along with an unpivot to cobble a solution together but it isn't efficient due to the fact that the unpivot effectively creates a Carteasian product. A CASE expression is more efficient but gets painful to write after 4 or 5 elements.
It sounds like you want an aggregate function that works over columns instead of rows. Aggregates are functions over sets, and columns do not form sets, because that would violate normalization.
Also, you're doing the unpivot wrong if you are getting a Cartesian product.
Drew
What I want is the same damned this that's in Oracle & MySQL.
They aren't aggregate functions. and no, they wouldn't violate any of the normal forms.
I'm talking about functions that takes a series input parameter values (like COALESCE or CHECKSUM) and outputs the greatest or least of the supplied values.As far as me "doing it wrong" please, show me how to to it correctly... I have yet to find a way of unpivoting multiple columns without increasing the number of rows.
There is a difference between increasing the number of rows and a cartesian product. An unpivot is linear, but a cartesian product is geometric.
Drew
If I unpivot 5 columns, it has the same impact, in terms of the numbers of rows being output in the result set, as doing a cross join to a 5 row table. How is that not a Cartesian product?
Because the number of columns in an unpivot tends to be fairly static, whereas the number of rows in a table participating in a cross join tends to be fairly fluid. The unpivot is only affected by the growth of one table, but the cross join is affected by growth in two tables. That's what I mean by the unpivot being linear vs. the cross join being geometric. 5n is not the same order as n^2 just because they return the same results when n=5.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 4, 2017 at 1:51 pm
Jason A. Long - Friday, December 1, 2017 2:46 PMIf I unpivot 5 columns, it has the same impact, in terms of the numbers of rows being output in the result set, as doing a cross join to a 5 row table. How is that not a Cartesian product?
The main difference isn't in the number of rows output, it's in the number of reads. Here is a test setup.
DECLARE @t TABLE (
a TINYINT
)
DECLARE @r TABLE(
a TINYINT,
b TINYINT,
c TINYINT,
d TINYINT,
e TINYINT
)
INSERT @r
VALUES(1, 2, 3, 4, 5)
INSERT @t
VALUES(1), ( 2), ( 3), ( 4), ( 5)
SET STATISTICS IO, TIME ON;
SELECT u.*
FROM @r
CROSS APPLY (
VALUES(a), (b), (c), (d), (e)
) u(a)
SELECT t.*
FROM @r
CROSS JOIN @t AS t
SET STATISTICS IO, TIME OFF
And here are the results:/* CROSS APPLY (UNPIVOT) */
Table '#B5588A48'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
/* CROSS JOIN */
Table '#B5588A48'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#B464660F'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Notice that the UNPIVOT has one scan and one logical read of the base table, but the CROSS JOIN has two scans (one on each table) and SIX logical reads (five on the base table and one on the other), but they both produce exactly the same results.
Is that enough to convince you that an unpivot is not the same as a cross join?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 4, 2017 at 2:51 pm
December 4, 2017 at 3:22 pm
Jason A. Long - Monday, December 4, 2017 2:51 PMNo... SQL Server is able to choose how it executes the query internally. That has nothing to do with the logical operation.
All you did was prove my point... Logically, they are the same because the produce the same the same physical result.
My point, all along, is that there is a cost incurred when unpivioting... And that is the cost I'd like to avoid by having actual GREATEST & LEAST functions.
Yes, SQL Server is able to choose how it executes the query internally, and it chose to execute the two differently. You can plainly see this in the number of table scans and logical reads based on this very small sample.
1 logical read does not equal 6 logical reads. 1 table scan does not equal 2 table scans. A constant scan does not equal a table scan (from the actual execution plan--not included). The two queries are physically different.
You're also wrong that it has nothing to do with the logical operation. The internal execution has to return the same results as the logical operation, so the internal query is tightly constrained by the logical operation. It has everything to do with the logical operation.
And just because two queries produce the same result for one single instance, does not make them logically equivalent. If I add a sixth row to the second table (which does not change the queries), the two queries will no longer produce the same exact results. The CROSS APPLY (UNPIVOT) will still only produce five rows while the CROSS JOIN will produce six. The two are not logically equivalent.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 6, 2017 at 1:45 pm
In SSMS, when viewing the 'Execution Plan' tab of the result grid and hovering over a plan operator or row pipe, it would be nice if row counts were formatted with commas. So, for example, instead of 211392072 it would show as 211,392,072, and also the row counts could be displayed above the pipes without having to hover over. SQL Sentry's Plan Explorer already does this, but it would such a small and useful thing for SSMS to do that too.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply