Top Posts & Pages
- Filtering Tables in #SQLServer Management Studio
- Server and column collation conflicts in properties of a login
- How to do a String REPLACE in Teradata
- SSIS Script Task and Proxy Auto Configuration (PAC) Script
- Go'e gamle Anders i Barcelona...
- SQL Server Quick Tip 11 - 15
- Rolling period with shell dimension SSAS
- #TSQL2SDAY #77 - Favorite SQL Server Feature - RoundUp
- Slides from Pro Techniques Talk at MsBIP
Tags.Net Analysis Services BI Business Intelligence C# Coffee D&D entrylevel Espresso FAIL Fejl Humor Issue MCP MDX MsBIP MSConnect NFL Personal Powershell Presenting Roleplay Security SQL SQL2008 SQL2012 SQLBits SQLBits X sqlfamily SQLPASS SQLServer SQL Server SQL Server 2008 SQLTip SSAS SSIS SSIS 2008 SSMS SSRS T-SQL Teradata TSQL2SDAY vandkøling VS2008 VS2010
Subscribe to Blog via Email
This is my seventh post in a series of entry-level posts, as suggested by Tim Ford (b|l|t) in this challenge.
I have not set any scope for the topics of my, at least, twelve (12) posts in this blog category of mine, but I can assure, it’ll be focused on SQL Server stuff. This time, it’s a little trick on how to execute DAX via a SQL Server Management Studio [SSMS] MDX Query.
In our current setup, we have both Reporting Services [SSRS] and PowerView Dashboards connecting to our underlying SQL Server Analysis Services [SSAS] cubes. And as we are constantly monitoring the servers, logging which queries are executed, we can tell which ones candidate for optimization. Now, with PowerView Dashboards we do not have the ability to change the actual DAX that gets send to the server. But we can execute the DAX, and trace whether aggregates are missing or if partitioning would be applicable.
Since we have the individual query, it would be nice to be able to execute these manually through SSMS with a SQL Trace running. Admitted, I am a Trace fan 🙂
This is actually possible, with only a little tweak.
In order to execute DAX on a SSAS Multidimensional cube, the Cube property of the connection string needs to be assigned. Here is how to do that.
Open a new MDX Query
You will be prompted for server, but before you assign any of that, hit the ‘Options’ button:
One of the properties available in the connection string is Cube. See full reference of connection string properties here. Select the third tab; Additional Connection Parameters, and assign the cube you want to query.
Write your DAX query, in the MDX query window
and execute it to see the results.
At the time of writing, not all of the queries we are logging are directly executable via this tweak, but we manage to get the long running ones going – which gives us enough insights to potentially fix or at least improve the query execution times.
Hope you enjoyed this little tip.
Speaking Kick Off 2016 – Slow start!
On Monday 23rd, I will be speaking at the Microsoft Business Intelligence Professionals, Denmark [MsBIP] user Group meeting. It’s been about two years since I was the host of one such meeting, while I was at Maersk Line. This time it’s at the new Microsoft HQ in Lyngby – And it will be my first visit there.
This will also be my first public talk this year.
The MsBIP User Group, is a community group dedicated to share information about Microsoft BI technologies, such as:
- SQL Server (DB, SSAS, SSIS, SSRS, MDS, DQS)
- SharePoint (PPS, Excel, SSRS…)
- Excel og Power BI (PowerPivot, Power View, Power Query, Power Map)
- 3. Party Products
The User Group is also part of the Microsoft Power BI User Group Program.
There will be two talks at this session:
Time Intelligence is probably the most interesting feature of any analytical solution. Computing Year To Date, Month To Date, Same Period Previous Year is quite easy in DAX but, as soon as the customer requests for time intelligence require working days evaluation, complex and custom calendar shapes, seasonal pattern recognition, the DAX formulas start to be harder to write.
In this session we are going to show how to compute classical time intelligence with the built-in DAX functions. Then, we will show some more complex time intelligence formulas that require to think out of the box, using advanced data modeling and querying techniques to produce interesting and useful formulas.
Getting the techniques in your tool belt right, makes a world of a difference. Did you ever wonder, how to deploy a cube, with minimum impact to query performance? Or how to optimize processing performance? Are you really ready to deploy when its required? Or do you get nervous every time?
Attend this session to build and improve your SSAS Developer skills, by exploring:
- Custom Assemblies – To improve Query Performance
- Partitioning – To improve Processing Performance
- Unit Testing – To improve Cube Release Quality
- Synchronization – To improve End User Experience
- PowerShell to tie all the ends together
My session will be presented for the first time at this event, and I hope you will join me for some SSAS Fun. Seating is limited to 50, or so I heard.
This is my sixth post in a series of entry-level posts, as suggested by Tim Ford (b|l|t) in this challenge.
I have not set any scope for the topics of my, at least, twelve (12) posts in this blog category of mine, but I can assure, it’ll be focused on SQL Server stuff. This time, it’s a neat little trick that has saved me a lot of that repetitive delete, down, home, repeat kind of work.
Only too often I find myself searching the web for pieces of code, that I need for some odd solution. And almost every time, I come across a web page, where the code sample is displayed with line numbers. It’s always annoying to have to sit and delete those manually. No longer I say! Yes, you heard me right. Actually Visual Studio has a neat trick to help you get this done in a jiff.
An example of a code snippet, could be the following:
Even though the numbers are not highlighted, when we copy from the source, they are still pasted into the destination. To easily get rid of the line numbers, just paste your code sample into a text document in Visual Studio; Pres [ctrl] + [n] and select a text file:
Once the file is open, paste in your code snippet:
Now for the juicy part.
Place your carrot/marker in the first line and first column, on index (0,0) so to speak – See green arrow:
Now, at the same time, Press [Shift]+[Alt] and mark just behind the last dot (.) behind the “7th” line, as in below screen dump.
This week, I have been looking forward to the time where I got to read through all the contributions to my #TSQL2SDAY invitation: Favorite SQL Server Feature. Very happy to see this many. I have added a short description of each blog post, as well as my own personal key take-away(s).
So, in no particular order, I give you the round-up:
Rob writes about SQL Server being backwards compatible and why that is cool. Rob also encourages to upgrade, if you are stuck on older versions – it’s what Microsoft recommends as well.
I also like that fact that with Azure SQL Databases, you don’t need to make the decision – it’s taken care of for you!
Key Take-Away: “…we now recommend ongoing, proactive installation of CU’s as they become available.”
Ben Miller (b)
Ben makes a great point in introducing Server Management Objects (SMO) – I like the fact that the post is kept as simple as possible, but still explains the why’s and how’s. If you are not aware of SMO, please do get going and Ben’s place is a good start!
Key Take-Away: I know SMO already, so no gain here…
John Morisi (b)
John must have balls the size of Texas. His favorite SQL Server feature is Replication! No, kidding aside. John offers a quick intro into what options there are with Replication and makes an effort to debunk some of the bad reputation Replication has. Do you wanna know more?
Key Take-Away: “…Replicate data to any ODBC or OLE DB accessible database including Oracle, AKA heterogeneous replication.”
Has written about Partition switching. This feature is as cool as it sounds – and what even more cool, it’s quick! I have used this technique in my ETL, at times. Normally data can be moved quickly enough, but when you need it done ultra fast, this is definitely an option worth investigating.
James also provides a little insight into new features in the upcoming release of SQL Server 2016.
Key Take-Away: “The good news is that the SWITCH command works on regular tables and in any edition.”
Seems this is Shanes first blog post ever, so kudos for making the entrance on the #TSQL2DAY stage! Shanes first adventure in blogging hits it out with an excellent run-down of Common Table Expressions, and a recursive one that is!
Key Take-Away: “…you are SELECTing from the CTE while still defining the CTE!!”
Andy’s blog post is a challenge to the topic. A challenge which I think is utterly justified. Andy advocates to take a step back. Look at the bigger picture. Take a moment of thought, before you start hammering away with your new found hammer.
Key Take-Away: “…configuring a multi-subnet WSFC, with an AG that spans two FCIs, then take backups from the readable secondary to use for cross-domain log shipping to a third data center.” (who doesn’t eat that for Breakfast?)
Andy offers up Dynamic Management View/Dynamic Management Functions (DMV/DMF) as his favorite SQL Server feature. The blog post is a quick introduction to DMV’s/DMF’s and makes a great starting point for anyone who haven’t yet been using those. Andy also shows a couple of pointers, to what DMV’s/DMF’s has to offer. The information is practically endless…
Key Take-Away: “…DMVs/DMFs are the gateway into SQL Server!”
Jason has written an awesome post about the almost unmentionable feature. In the end however, he caves in and spills the whole deal. Unintentionally, I think, there are several take-away’s from Jason’s blog post – both the feature (which I will not dare utter), but also Synonyms – If nothing else, Jason demonstrates and example to follow, in the sense that the customer gets a perfectly working solution at minimum cost (even though an upgrade would have been way more cool to work with etc…)
Key Take-Away: “Using a synonym, I can extend this database beyond the 10GB limitation…”
Kennie offers another shot at CTE’s; This one also recursive.
Key Take-Away: “…we continually join the data set to the result set as it exists for each “loop””
Via a Dell Laptop, Edwin steers us through a good number of uses of SQL Server Logging. To anyone who thinks logging is just logging, have a look at the uses in Edwin blog post.
Key Take-Away:”How To Forecast Database Disk Capacity If You Don’t Have A Monitoring Tool”
John talks about Transactional Replication for Azure SQL Databases and offers three main reasons why this is an ûber cool feature. I have to agree, that this is a really cool feature and John does a good job explaining why. Looking forward to the follow-up post containing the How To.
Key Take-Away: “The reporting subscriber database can be scaled up or down as needed based on your needs…”
With the End of Support for SQL Server 2005, Derik finds it appropriate to pay his respects by picking Database Mirroring as his favorite feature. The feature will be replaced by Basic Availability Groups in SQL Server 2016.
Key Take-Away: “…mirroring fails over even faster than Availability Groups.”
In this post Kenneth walks us through the different applications of Delayed Durability in SQL Server. Advice on Simple and Full Recovery Mode as well as HA scenarios are covered. Delayed Durability is a new feature to SQL Server 2014 and applies to Azure SQL Database as well as the upcoming SQL Server 2016 release.
Key Take-Aways: “The fact that this should be utilized for massive Data Warehouse loads, where durability is not of concern.”
I was really impressed by the hard work put into this round of #TSQL2SDAY, so thank you, to all the contributors. Great job all!