At last we can look forward to having anew edition of SqlSaturday in Denmark on April 25th. As previously the event will be hosted at Microsoft HQ in Lyngby, so the venue will be familiar to many of you who are returning attendees.
This year we are looking at session in all of the following categories:
Analytics and Visualization
Application & Database Development
BI Platform Architecture, Development and Administration
Cloud Application Development and Deployment
Enterprise Database Administration and Deployment
Currently we have received over 100 abstracts to select from, which is always a daunting task. Luckily Bent Nissen Froning (t|b|l), Claus Lehmann Munch (t|b|l), Just Thorning Blindbæk (t|b|l) , David Bojsen (t|l) are all very accomplished professionals, who will be making the right choices I am sure.
On the day before the conference, the team is offering three (3) pre-cons with world renowned professionals on:
As a response to a comment on a previous blog post on how to extract SSAS Multidimensional [MD] databases with PowerShell, I decided to write a new blog post, to address the tabular version [Tab].
The main differences working with MD and Tab, programatically, is that MD is represented by XML for Analysis and Tab is using JSON. In management studio this makes no difference however, as you paste XMLA and JSON using the same query type; XMLA (I wonder when/if that will change?)
Obviously, the two technologies MD and Tab are vastly different in almost every other aspect as well, but for the scope of this exercise, we will keep it at that.
Just as in the previous example, we will be using the ability to load assemblies in PowerShell and leverage the functionality the product team has provided. With Analysis Services comes a library of objects to programatically access and manage an Analysis Services instance.
In this documentation, you can dig into the details of options available. All of this extensible from both C# and PowerShell.
Now, back to the problem at hand. We wanted to extract the models from one or more servers, to deploy to another (single) server them or even just persist them locally. To do this, we need to load the Tab version of the assembly, which is that first difference to the original script. Next we need to leverage different functionality within the assembly, to export the json.
The script in all it’s simplicity 🙂
#Load the Tabular version of the assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.Tabular") >$NULL
#Add a comma seperated list of servers here
$SourceServers = @( "<SOURCE SERVERS HERE>" ); #Source
#Add a single server here
$TargetServer = "<TARGET SERVER HERE>"; #Target
cls;
#Uncomment to deploy to target server
#$TargetServer.Connect();
#Loop servers
ForEach( $srv in $SourceServers ) {
#Connect to current server
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($srv)
#Loop al databases on current server
ForEach( $database in $server.Databases ) {
#Generate Create Script - Other options are available, see https://docs.microsoft.com/en-us/dotnet/api/microsoft.analysisservices.tabular.jsonscripter?view=analysisservices-dotnet
$json = [Microsoft.AnalysisServices.Tabular.JsonScripter]::ScriptCreate($database, $false)
#Create Path
$Path = "<INSERT DUMP LOCATION AND FILE NAME>" + ".json";
#Export the model to file
$json | out-file -filepath $Path
#Uncomment to deploy to target server
#$TargetServer.Execute($json);
}
}
Tuesday 3rd of this Month I invited people in the SQL Server community to share which tools are essential to their daily work. I was really overwhelmed by the number of stories that the topic triggered. 22 in total took the time to write down and share which tools they use for their work chores.
Going through 22 posts and aggregating them has been taking more time than I had hoped for, since my trusted laptop broke down – blinking codes are well and alive I tell you!
Going through the lot, I found some similarities to the posts, and have categorized them accordingly. But first off a BIG thank you to all how participated!
Without further ado, here goes.
Relational Heavy Lifting
Kamil Nowinski(b|l|t) takes us through the classic stuff, I mean, the real classic stuff – some would call it vintage – by showing how Total Commander still has a place in the tool belt, this century 😉
Matthew McGiffen (b|l) shows how to set up a Central Management Server, in order to execute queries against multiple instances in ad-hoc scenarios, seamlessly. Very nice tip. Matthew also did a second post, lining up multiple tools he’s written about in the past, nicely aggregated in this post.
Jess Pomfret (b|l|t) does a really nice post on how Powershell and the dbatools has changed her way of working. Jess even provides some useful snippets to get you going. I share the same enthusiasm for Powershell as Jess does, and was very pleased to see homage paid to the dbatools – incredible tool. Best of luck on your speaking adventures!
Marek Masko (b|l|t) has a huge post on classic DBA tools as well as a pleasant surprise on testing using tSQLt. Also some good pointers to free community scripts and tools as well. Great read!
Dan Clemmens (b|l|t) goes all in on DBA tools for statistics, execution plans and tracing, even including the legendary diagnostic scripts from Glenn Berry.
Steve Jones (b|l|t) has a huge list of free and paid tools, from SQL Server sentric tools to a good deal of process related tools – i.e. DevOps and such.
Also Steve manages to sneak in a reminder on the password thingy magicky, that, according to domain expert Troy Hunt we all should rely on, be it pwsafe or any other tool like that.
Warren Estes (b|l) is praising the usual suspects in the DBA field, but adds a couple of interesting options for productivity and benchmarking/testing and also rounds up a couple of SentryOne products.
Devon Leann Ramirez (b|l|t) is offering a thorough introduction to their free plan explorer offering. Devon also makes a good point in marking the company’s presence on the community. If you want the quick tour, head over to Vimeo.
Rob Farley (b|l|t) talks about two things I really hold dear; Coffee… and I forgot the other thing. No really, Rob has an excellent blog post on Live Query Stats (LQS), and what some of the use cases are for that feature/tool. There are more ways of using LQS than I had thought about – thanks for sharing!
Riley Major (b|l|t) share his story on how he works with Management Studio and how the cool could be improved to further support a common way of working. Besides the tips on SSMS Riley also lists his favorite (and not so favorite) tools.
The BI Power in Power BI
James McGillivray(b|l|t) is first and foremost writing about my trusted travel mate; The Kindle (App) as his favored tool of the trade. Besides that treasure trove books can be, James also has some pointers to daxformatter and a theme generator which is pretty hefty!
Community Zone
Jo Douglas(b|l|t) argues that the most important tool for any professional is networking and community, and it’s hard not to agree completely. Jo also writes about some great points of where to begin this journey.
Jason Brimhall(b|l|t) brings up the aspect of blogging itself as a great tool of the trade, and I have to agree here and couldn’t have stated it more clearly that Jason:
Blogging helps you become a better technical person.
Googlefoo is also described in Jason’s blog for this party, and he manages to sneak in a reference to his extensive work and blogging on Extended Events, which in itself is an awesome contribution.
Reid DeWolfe (b|l|t) offers a quick write up on classic DBA must haves; SQL Prompt, Plan Explorer and GitHub/SourceTree. Reid also describes some of the main benefits of the tools.
Other
Garland MacNeil (b|t) brings another perspective into the party, by writing from a borrows laptop – not sure it was intentional, but I guess the exercise is very rich in terms of learning. I know others have been there too:
When you drive all the way to work, during rush hour, and after parking up and reach over to grab your laptop and realise you left it at home… pic.twitter.com/eBxW5iofAP
Chrissy LeMaire (b|l|t) has, surprisingly enough, not written about dbatools, and if you believe in that you may call me Bill 🙂
In Chrissys blog post you’ll find a great list of auxiliary tools for all the things you do around programming; Screen shot/Image handling, code repositories, clip board management and video editing tools.
Josh (b) gives us the DevOps perspective of a Database DBA/Developer in a not so uncommon scenario – well, I think we’ve all been there at some point. Some prominent 3rd party tooling is getting some ❤
Hugo Kornelis (b|l|t) makes a good argument on not becoming addicted/dependent on the presence of certain tools in order to perform your job. I guess this applies in particular, when you’re a consultant and can’t always BYOD. Apart from that Hugo really likes SQL Prompt and Plan Explorer 😉
If you’re not familiar, T-SQL Tuesday is a blogging party hosted by a different person each month. It’s a creation of Adam Machanic (b|l|t), and it’s been going on for ages now! Basically the host selects a topic, defines the rules (those are almost always the same), and then everyone else blogs about said topic. Once the deadline is reached, the host summarizes each of the submitted posts on their site/blog.
This month I get to pick the topic, and I am going to go with:
The Essential SQL Server Tools in my stack
Besides SQL Server Management Studio and Visual Studio Data Tools we all have our own set of tools that we use for everyday chores and tasks. But how do we get to know which tools are out there, if not for other professionals telling us about them? Does it have to a fully fledged with certification and all? Certainly not! If there’s some github project out there, that is helping you be double as productive, let us know about it. You can even boast about something you’ve built yourself – if you think others will benefit from using it.
Basically I think, that by establishing awareness about what kinds of tools that are out there, new professionals will not have as steep a curve getting the pace up, as they would have had. But I suspect that even some veteran guys could have an “a-ha” moment from reading the summary.
Additionally, you can (read: should) share how you came to depend on said tool – and of course you are encouraged to give credit, where credit is due in terms of making you aware of the tool.
Another approach for this topic, is to approach it as kind of A Day in the Life of kind of blog post, as has been done before by Erin Stellato (b|l|t). Writing with the specific angle to describing how your everyday is made easier by the use of your tool stack.
The How
There’s only a few rules for T-SQL Tuesday:
Your post must be published on Tuesday April 10th 2018 between 00:00 GMT and 23:59 GMT.
Your post must contain the T-SQL Tuesday logo (see above) at the top and the image must link back to this blog post.
Trackbacks should work, but if they don’t, please put a link to your post in the comments section so I (and everyone else) can see your contribution!
I am super excited to have been selected to speak at the Intelligent Cloud Conference in Copenhagen, May 29th-30th this year.
The full schedule for the event can be viewed here.
My talk will be on Power BI, or to be more precise, a talk on tips and tricks to get you started ingesting data with the Power BI Desktop application. So if you are a veteran SSIS developer, tag along to get your mind blown by the simplicity of Power Query. Or maybe you are a prospect Data Scientist that needs something like Excel on steroids. This will be a session for you.
I will start of by demonstrating how easy it is to import data straight into Power BI from csv or xls(x) files that have a simple structure. Then moving on to scraping data from a web page. Gradually making the tasks more and more complex, as we move on to header/footer issues or what about variable number of columns for multiple files for the same data set? Once we have established the elegance of Power Query in Power BI, I will demonstrate how easy it is to Enterpricylize (yep, made up that word for this situation) your data discoveries by exposing the newly created model in an Azure Analysis Services, with a few clicks in a wizard.
However, if you are not into Power BI or Business Intelligence, the conference has a lot more to offer. It’s a combined Data Platform and Azure conference, meaning there will be an equal number of tracks divided among the two topics, filled with super interesting sessions such as “Failure prediction for manufacturing industry with SQL Server & R”, “Maturing IoT solutions with Microsoft Azure”, “How to make hundreds of servers work for you” and the list could go on and on.
I am really looking forward, not only to speak at this conference, but also listen in on some of the other great sessions that are being presented.
The Why