Slidedeck and Demo – Campus Days 14

2014-11-19 14_09_09-Campus Days - Azure HDInsight part 1.pptx - PowerPointMaking finishing changes to my talk at Campus Days next week in Copenhagen.

The talk will be focused on End-to-End Big data solution running on AZURE, and the various methods make the implementation both easier, faster and smoother. Will start by showing how we can make it all work using the AZURE Portal, and then move on to automation of the process using POWERSHELL – in the end I have a good friend with me on stage, who will present an all together new way of managing your Big Data Project on AZURE.

 

So make sure that you’re in place Nov. 26th at Campus Days.

Pre Pass Summit 14

20141106_000328000_iOS This is the week after Pass Summit 2014, and what have I learned, gained, experienced during Pass – well for starters I’ve meet a bunch of interesting people, sat through a whole lot of sessions, sessions that both inspired me and made me think about the way I implement solutions at my clients.

Automation

One of the things that was most emphasized was the ability to automate and script everyday tasks – in one session the speaker showed us how to automate the most trivial task on a SQL server, using PowerShell. This is something that I will take home, and start using with my clients, or just for starters in our internal IT setup.

SQLServer

For instance, why would you use the GUI in Sql Server Management Studio to make Backups and restore of databases, when everything can be automated in about 20 lines of code. Furthermore, this is only one aspect of the many things that can be automated by using PowerShell.

Azure and HDInsight

In another session, well actually it was a whole day precon, the speakers walked us through the rather tedious task of creating a BIg-Data application on AZURE, using the GUI – it’s manageable but wouldn’t it be nice if everything was automated, as the point being with AZURE – is the ability to spin up an application / server park when needed.

With that in mind they then walked us through an PowerShell script that by using a configuration file, was able to setup and deploy a BIG-Dataa application on AZURE with only on hit of a button – F5 for execution.

The script would then automatically set up:

  • A new Storage Account
  • A new SQL server
  • A new SQL Database for metadata storage
  • A new HDInsight Cluster
  • A new Blob Storage
  • Upload data to the storage

All this would take about 10 minutes, during which, you as a developer could make something more meaningful.

After the deployment you had a setup with data, and you could now begin the interesting part of the project, namely querying the data in the HDInsight Cluster.

And the beauty of it, is that once you’re finished using the BIG-Data application on AZURE, you could remove it all, and deploy it later when the need arises.

People

As with any conferences, one of the most important things to remember is to develop your network, and by this I mean – Just do it, talk to people, be open, ask what they work is about, where they’re from, and then the talk will soon flow smoothly. That way you’ll see that  many have the same challenges as you, and many may even have solved them, and what better way to meet other people is there than to get a solution or maybe help a fellow SQL man/woman with a solution to their problems.

So don’t be shy, just throw yourself into the battlefield, and make friends and acquaintances from across the world.

Twitter

Be on twitter, both during PASS and when home in your day job, stay in contact with the many people and “celebrities” you’ve meet during pass. Many times I’ve managed to sit across people that I know very well on twitter, but not in real life, just to suddenly realize whom they are. And likewise they’ve meet me like that. So the value of using twitter a few times a day is just mind-blowing.

Follow #SQLHelp as this is the way to get help from the best in the field, people will if they have the answer and time, often provide you with either a solution or at least a direction for a solution to your problem.

And after a while – just start help others, remember give back to the community, and your experience will be ten times better ;o) and the community will flourish.

SSIS Connectors v3.0 for Oracle and Teradata

This monday Microsoft announced the releas of a new SSIS Connectors v3.0 for Oracle and Teradata by Attunity, this release will fix a few bugs, and

You can download the connector here http://www.microsoft.com/en-us/download/details.aspx?id=44582

In regards to Teradata, the release is considered a minor release, and the fixes is listed here.

  • Expose additional TPT Stream Attributes(TD_PACK and TD_PACKMAXIMUM) to provide maximum tuning flexibility.
  • Support for loading table with columns using reserved words.
  • Fix mapping for TIME(0) to DT_STR SSIS datatype.
  • Can display table name more than 30 characters correctly.
  • Support for block mode and set as default mode.
  • Expose TD_SPOOLMODE for TPT Export for faster extracts.
  • Support for Extended Object Names(EON), which allow UNICODE object names in the Data Dictionary tables.
  • Adding new datatypes (TD_PERIOD_DATE, TD_PERIOD_TIME and TD_NUMBER)

You can download the connector here http://www.microsoft.com/en-us/download/details.aspx?id=44582

 

Pass Summit 2014 – Tips & Tricks

One of the greatest thing when attending a conference, is that you meet a lot people that instantly understands what you’re talking about. And during some talks and sessions, we tend to share tips and tricks.

One of the better tricks I’ve come across was shared today by Robert Cain ( twitter | blog ) and it’s about to make your life as an t-sql developer a lot easier.

You know sometimes when you have to make a new statement, you right-click the table and create script as select and use that to work further on.

Script Table

Usually you need to join another table onto that table, so you add an Alias to the table, and then add that alias in front of all the attributes on the SELECT statement.

whatifitoldyou

 

Createscript

Go add an alias to the table.

tablealias

Put your cursor at the last attribute in the select statement.

Click and hold ALT+SHIFT and move you cursor up with the ARROW-UP

alt-shift

See there’s a light blue line now?

Now start typing the alias of the table, and by magic the alias you write is written on all lines selected previous.

alias

And this can also be used for copy pasting.

I bet you this will be the most used little nifty trick – and it works in SSMS 2012 and 2014

Pass Summit day 1

powershell-f8deadaf08be625e8d5dc2d146d1b7a5Sitting here waiting for the session to start, and what better way to use the time than to plan my schedule for today and tomorrow. I have to say it’s real hard to choose the right ones, due to the high quality of the speakers and abstracts.

But I think I made som good choices, but that I’ll know better to night. Here’s the sessions that I found most interesting.

I’ll write during later about what I took with me from the sessions.

[AD-306] Make SQL Server Pop with PowerShell
Speaker Robert Cain

PowerShell is everywhere, and that includes SQL Server. In this session, we’ll look at using PowerShell with both the SQL Server provider and the SMO (SQL Management Objects) library. You will learn how to handle common DBA tasks, such as checking the status of various services, validating backups, and checking for disk usage. You’ll also see how to perform developer tasks such as creating tables, inserting records, and querying data. Perhaps most importantly, you will learn how to read and understand the SMO object model, and translate it into PowerShell commands. Come see how to make your SQL Server Pop with PowerShell.

[BIA-206] Configuring SharePoint 2013 as a Business Intelligence Platform
Speaker : Edwin Sarmiento

With SharePoint as the business collaboration platform of choice for the enterprise, it has become an important piece in the overall Microsoft BI stack. However, deploying and configuring it to support Microsoft BI solutions has become a cumbersome task for the IT professional. In this session, you will learn what it takes to successfully architect and design SharePoint 2013 as a BI platform. We will cover multiple configuration scenarios and the different technologies necessary to build the infrastructure. At the end of the session, you can walk away feeling confident to run Power Pivot, Power View and Reporting Services in your SharePoint 2013 farm.

[DBA-407-M] Query Store – A New SQL Query Tuning Feature
Speaker : Conor Cunningham

Have you ever come in to work only to have the boss come tell you that your main site is down and the database is “broken”? Fixing query performance problems can be rough, especially in high-pressure situations. Microsoft has developed a feature to help customers gain significantly easier insight into production systems and to be able to quickly fix cases where a new plan choice from the query optimizer has undesired performance consequences. This talk introduces the Query Store, explains the architecture, and shows how it can be used to solve real-world performance problems. It will now be possible to ask questions like “show me what query plans have changed since yesterday” and to quickly ask the optimizer to “go back” to the query plan that was working fine for you previously.

[BIA-307] Continuous Delivery for Data Warehouses and Marts
Speaker : John Welch

Most data warehouses are in a constant state of flux. There are new requirements coming in from the business, updates and improvements to be made to existing data and structures, and new initiatives that drive new data requirements. How do you manage the complexity of keeping up with the changes and delivering new features to your business users in a timely manner, while maintaining high quality? Continuous delivery is an approach for managing this. It focuses on automation of many steps in the process, so that time is spent on adding new functionality, rather than repetitive steps. Attend this session and learn how Continuous Delivery can be applied to your data projects.

Pass Summit 14 – Preconf (in progress)

Attending the preconf session Big Data: Deploy, Design, and Manage Like a Pro where Buck Woody (web), Adam Jorgensen (web | twitter) and John Welch (web | twitter) is doing their magic with Azure, HDInsight, PowerShell and everything in between.

B1iLXdiCQAA3SR6

Great questions from the attendees, and even greater answers.

Some keypoints from Buck is these, but I think they’ve always been relevant, but now even more in regards to Big Data.

  • Always ask the right questions
  • Never select the tech beforehand
  • Always select the TECHNOLOGIES after the questions have been asked and answered
  • Move 1TB data to Azure, DONT DO THAT
  • Send data i a trickle way, incremential data load

Powershell In A NutShell :

  • Scripting language
  • Based on Command Lets
    • Verb Noun
    • DIR becomes – Get child item
  • Variables always starts with a $
    • $Datasource
  • Everything is an OBJECT

John Welch is starting to talk about how to load data into your Azure storage, for this task we’re loading data from Twitter.com and Linkedin.com

John has a tool to download the XMLfeed from Twitter and Linkedin, the data needs to be preprocessed on record at at time

photo1

  •  Text files need to be in UTF-8 no BOM
  • Records is delimited by newline
  • Formats
    • Several formats can be used
    • Delimited text
    • SEQ. File
    • RCFile / Optimized Column File

 

More to come

In exactly 1 week I’m at PASS Summit 14

firsttimers

In exactly 1 week from now I’m in Seattle for my first ever Pass Summit, and I for one cannot tell you how much I look forward to be there with all the amazing people from the SQL community. As it my first time there i’m planning to attend the different “firsttimer” events, as well as all the other events that’s part of this summit.

We’re 3 guys travelling together this time, 2 from Rehfeld and 1 from Mærsk (whom used to be at Rehfeld) – and instead of crashing each night at different hotel rooms we’ve rented an whole appartment via AirBNB.

It seemed as the smartest, and it’s actually cheaper than hotels, only downside, is that the houserules says “STRICTLY NO PARTIES” – gonna be a tough one to manage ;o)

I’m attending 2 full day pre conference workshops, monday and tuesday and I’m really excited to learn from the best – and even pick up some tricks for use in my own presentations at SQLSaturdays around europe.

I’m in the process of trying to plan my session schedule, but I assure you, it’s a tough task – trying to pick which one of the sessions that I can skip to go for another.

Well until sunday next week, have a nice week, catch me on twitter, maybe we’ll see each other in Seattle.

 

CU 4 for SQL Server 2014 ready

sql-server-2014It’s a good day today, the release of Cumulative Update 4 for SQL Server 2014 will fix a lot of small and larger bugs.

Including the painfull one that task would shift to upper left corner in SSIS when resized.

Cumulative update package 4 for SQL Server 2014 (including update for SSIS) is available at http://support.microsoft.com/kb/2999197/en-us

This CU release contains a list of bug fixes for SQL in general, SSRS, SSAS and SSIS.

For SSIS fixes:

FIX: Tasks shift to upper-left corner when you move or resize them while designing a maintenance plan in SSMS 2014

FIX: Error in SQL task with IsQueryStoredProcedure set to True when you use .NET Data Provider for Teradata in SSIS

FIX: SSIS 2012 or SSIS 2014 XML Source task cannot load data from URI

For SSAS Fixes:

FIX: MDX query returns no result when the cache is not cleared after you run a previous query in OLAP or tabular model

FIX: User obtains incorrect data when two users in the same security role run a client report in SSAS 2012 or SSAS 2014

SQL Performance Fixes:

FIX: A severe error occurs when you run a query that uses CTEs against the partitioned tables in SQL Server

SSRS Fixes:

FIX: Error when you print, print preview, or export the report to PDF in SSRS 2012 or SSRS 2014

For the full list look here

Salesforce and integration with SQL Server – part 2

SFSQL

As described int the previous post, I’ve been working on a project which integrates data from Salesforce (SF) to Navision (NAV) manipulate and create new data, and then we needed to push it back to SF again.

To get all this to work together , there is a need for a local database in your SQL server, and in this case we call it LOCALSF – this is where alle the storedprocedures from DBAmp resides. But it is also the place for the local tables which is used to store data from the SF cloud storage.

The previous post showed you how to generate the tables to hold SF data, but DBAmp is also capable of Insert, Update and Delete in SF cloud storage. To mange this there is a need for either a Insert, Update or Delete table – these can be generated almost automatically by calling the stored procedure SF_Generate

To do this we have to issue a statement that will grab the table structure from SF containing all the attributes of which we can insert, update or delete values, the procedure wil then render a table in the local database.

The statement if we would like to create an insert table for account is this

EXEC SF_Generate @operation = 'insert', 
                 @table_server = 'SFCLOUD', 
                 @load_tablename = 'Account_insert'

This creates a table with an ID, ERROR attributes as well as alle the attributes needed to insert a new Account in SF, you then need to populate the table with values, ID and ERROR must be left empty as DBAmp uses these to keep hold of which record is deployed and which are not.

After the population of the account_Insert table, we need to puch the data into SF, this is done by calling the following statement.

EXEC SF_BulkOps @operation = 'Insert',
                @table_server = 'SFCLOUD', 
                @table_name = 'Account_Insert'

What DBAmp then does is, that it will grab all rows from the insert table and insert them into the account object in SF, afterwards it writes a status in the ERROR attribute on the insert table, so it’s possible to see if any given record is succesfully transfered.

If you instead want to generate a table for Deletes you have to issue the following statement.

EXEC SF_Generate @operation = 'delete', 
                 @table_server = 'SFCLOUD', 
                 @load_tablename = 'Account_delete'

This statement will generate a table called ‘Account_delete’ which will contain an ID, and ERROR attribute and that’s it.

Then you could populate the IDs with SF ID’s that needed deletion from SF, then as before you would need to issue a statement for deleting these records.

EXEC SF_BulkOps @operation = 'Delete', 
                @table_server = 'SFCLOUD', 
                @table_name = 'Account_Delete'

Again DBAmp will connect to SF in the cloud and delete the approriate records and save a status in the ERROR attribute.

In the next posts, i’ll be getting into the stored procedure SF_BulkOps that we shortly looked at in this post.

Salesforce and integration with SQL Server

SFSQLIn a recent project i’ve been working on, we needed to transfer data from Salesforce (SF) to Navision (NAV) manipulate and create new data, and then we needed to push it back to SF again. To manage this we needed to persistate the data from SF in our on onpremise SQL database, there are a few tools that allows that – we used DBAmp for the purpose, its a framework developed in T-SQL using stored procedures. It allows you to replicate tables from the SF Cloud to you on-premise database.

In the following series of posts I’ll go through the various procedures and how it could be implemented in Integration Services

First step is to make a Linked Server on you SQL server to the SF database in the cloud (how to do this is not covered here) – when that is in place you should be able to make queries like this to you data in SF.

SELECT * FROM SFCLOUD...Account

In the above statement SFCLOUD is the name of the linked server, and remember to use 4 name convention

These are the main procedures in the product, and before we can push data to SF we need to have local copy of the table.

  1. SF_Replicate
  2. SF_Generate
  3. SF_ReplicateAll
  4. SF_BulkOps
  5. SF_Refresh

To do this we have to issue a statement that will grab the table structure from SF and then make a create statement on our local server, the statement if we would like to get the account table is this

EXEC SF_Replicate @linked_server = 'SFCLOUD',
                  @object_name = 'Account', 
                  @options = 'batchsize(50)'

What this will do is that the table will be created in the local database, and the data will be transfered as well, and data will be transfered in batches of 50 rows.

When the stored procedure is finished you should be able to select data from your local table, in your database, so if your database is called LOCALSF the you SQL statement would look like this.

USE LOCALSF
GO
SELECT * FROM dbo.Account

That should result in a dataset exactly as the one you would get from the first statement, what was executed on the Linked Server.

In the next posts, i’ll be getting into the stored procedure SF_Generate