Tag: SQL

SQL Pass Summit 2014 Great Success!

Posted on November 10, 2014 by Doug Hoogervorst - Blog, Conferences, SQL

MDX, aggregations, scope assignments… SQL Pass Summit 2014 was data nerd heaven in terms of having access to uber experts who you can ask and learn from. Some learn from white papers only or maybe a book. Personally, I prefer face to face with real examples and it has been completely refreshing here at SQL Pass Summit 2014. Speakers this year have been completely focused on the tips and tricks.

I want to shout out to the following speakers:

Chris Webb, Peter Myers, Brian Larson, Kesper De Jong, Devin Knight, Miguel Llopis, Tessa Palmer, and David Peter Hansen. Really enjoyed your sessions. Hope you’ll be back!

It will be interesting to see what happens next year. Not a lot of new work on SSAS multidimensional models (re: none) and some of the sheen is off the tabular model as its limitations are more and more known and not all that acceptable in some key places. Where is SQL Server 2016(?) going? There has been no word on it. Count on Microsoft to continue to empower the power user.

Here’s hoping:

that Power Query comes together to improve SSIS
that the difference between on-prem and cloud goes away
for data residence or delivery; there are too many “it works on prem but not in Power BI or you can use this in Power BI but not on prem”
that support for NON technical people continues. In talking to Power Query people, I kept hearing about M and connections with JSON. Those are not mainstream items when making a mainstream product.
MDX / DAX can become friends.

SQL Pass Demos Power BI and Power Query – looking good!

Posted on November 5, 2014 by Doug Hoogervorst - Blog, Conferences, SQL

First day coming to an end here in Seattle at SQL Pass Summit. One more session today. Lots of demos of Power Query and Power BI, and these tools continue to mature. It is hard to tell what is “about to be released” and what is actually out, but the demos are fun to watch. Power Query has really come a long way. It now can connect to quite a few things – web pages, Salesforce.com, Business Objects, documents, Power Pivot models, databases – and you can do basic transformations like splitting columns. It looks cool for good Excel users who then want to make a Power View in Excel. Downside, well, it is in that one Excel sheet, not published yet.

Power BI has also come quite a ways. Released in February, it shows great with Power Maps but these are again simple demos, not necessarily useful demos. Overall the dashboards can be set up at a relatively lower cost for licensing. Entry level cost is $40/user/month if someone doesn’t have Office 365, plus the cost of consulting to set it up (or do it yourself). However, some big things are missing – like connecting to on-premise cubes, providing ad hoc access to people who don’t know how to set up dynamic time frames, and working with anyone who doesn’t have Office 365.

More reviews to come as we power through SQL Pass.

We are here – SQL Pass 2014!

Posted on November 5, 2014 by Doug Hoogervorst - Blog, Conferences, SQL

Hello customers… one thing that defines us at Business Impact is our commitment to stay on top of what is happening in the world of business intelligence. We can do this dozens of ways, but our largest investment comes when we attend the SQL Pass 2014 Summit. Guess where Michele and I are today?

SQL updates and best practices are on display plus new technologies, sessions and more. It means we have to take a week out of our schedule to get here at SQL Pass 2014… but YOU get the benefit.

And while we’re here, please take advantage of us. If you have any questions on SQL Server, BI or products that have come up or that we haven’t been able to answer, please write me or Michele May.

Plus take a look at what sessions are offered while we are here!

Snapshot Tables Data Warehouse

Posted on May 8, 2013 by Doug Hoogervorst - Blog, SQL

Often our customers ask us to capture a specific point in time – say inventory or how much they are owed in Accounts Receivable – so that they can refer back to it later. Of course, this can be recreated by rolling back all the transactions but it is far easier to capture a snapshot of the data as of a point in time. This is stored then in data warehouse snapshot tables. Then you can trend back and compare today vs. last week or last month. This information is very basic in business intelligence because we want to compare today vs. something, whether it’s the past or a goal or what have you. [quote align=”right” color=”#999999″]…the most utility is trending for the last quarter and then keeping the snapshot as of the last day of a month…[/quote]

The drawback come in storing these points in time, these snapshots. Each snapshot is not altogether overwhelming in terms of storage, but keeping inventory by every product by every warehouse by every bin can be 10000+ records for a day and if you keep that each day for a year and then two years and so on… well, what is the utility of having your inventory position on Flag Day 2010? And it does add up in terms of space, especially when you add indexing.

We find that the most utility is trending for the last quarter and then keeping the snapshot as of the last day of a month or the week end (this depends on whether a business measures itself weekly or monthly). Thus, you keep every day for up to three or four months and then only keep the snapshot as of the period you measure. This balances storage v. utility.

Install Northwind database in Microsoft SQL Server 2012 in 3 easy steps

Posted on January 25, 2013 by Jacob Ross - Blog, General

This guide will show how to install northwind database in only 3 easy steps.

Having trouble attaching northwind database to your SQL Server 2012? Well you are not alone! Northwind is the name of the sample database for SQL Server 2000, that later got replaced with the AdventureWorks sample database in SQL Server 2005. However, it is still used in e.g. Microsoft SQL Server 2008 Database Development Training Kit so may still be relevant for users trying to learn SQL essentials.

The problem is that the original database file is not compatible with the newest version of SQL Server and causes difficulty for new users. We don’t believe the first lesson of SQL training should be jumping through hoops to get the sample data working, so here is the “fixed” database file and a guide to install it.

Good luck!

You can download the database here (zipped) and I would recommend you extract it to your SQL Server DATA folder as e.g.: “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA”.

Step 1:

Open SQL Server Management Studio and connect to your instance. Right-click the “Databases” folder and click “Attach”.

Step 2:

Click “Add…” in the “Databases to attach:” box and find NORTHWIND.MDF in your folder. Make sure to click on the file and check that the “Database Data File Location:” is accurate. Click OK.

Step 3:

Notice that it includes a “NORTHWIND_log.ldf” which actually doesn’t exist. So we want to click on that and remove it. Don’t worry! A new log file will automatically get created in the right location once you have attached the datbase. Also, verify that “NORTHWIND.MDF” has the right “Current File Path”. Click OK and check the “Databases” folder in SQL Server Management Studio for the “northwind” database.

That’s it! You should now have full access to the Northwind Sample Database in a SQL Server 2012 environment.