Articles for the Month of February 2016

Feb 28 2016

Best Practices for the care and use of the SSISDB

Author: Ginger Grant • Discussion: 1 Comment

Deploying SSIS packages since the release of SQL Server 2012 and beyond, has moved to the SSISDB database. If you are using SQL Server 2012 and beyond and are not using the SSISDB for deployment, my sincere condolences as maintaining and deploying packages any other way is a maintenance headache which thankfully has been resolved. As the SSISDB has not been used for very long, the appropriate care and feeding of this database is not well known. SSISDB is part of Integration Services Catalogs. It is not possible to create an SSISDB without first creating an SSISDB catalog, which does not happen when SQL Server is installed. Follow Microsoft’s instructions for creating a SSISDB Catalog, which creates the SSIS catalog and the SSISDB database. You cannot rename the database, as SSISDB is the name of the database that Integration Services uses internally.

Backup the SSISDB

A client asked me recently why he should back up the SSISDB database. While you can recreate everything inside of the SSISDB, it will take time and you will have to remember exactly how all of your variables were set. Restoring the backup decreases this issue and having a backup allows a server to be redeployed quickly. When you do back up the database, make sure that you remember to backup the database certificate, which is created when the SSISDB is created as well, as you will need this to do a restore. By default. the recovery model of the SSISDB is set to Full. If the packages in SSISDB are changing minute by minute, full would make sense, but given that an SSISDB contains packages which are run on a scheduled basis, most likely the changes made are infrequent. Change the recovery model to simple.

Managing SSISDB Growth Over Time

SSISCatalogSettingsSSISDB contains all of the data used for the reports created when SSIS packages are run. Right click on the SSISDB icon underneath the Integration Services Catalog and take a look at the settings. The default settings are listed here, and to decrease the size of the SSISB over time, you may which to change them. The Retention Period is set to 365 days. Many environments don’t look at reports greater than 90 days, as information prior to that timeframe isn’t very meaningful. If that is the case, change the retention period to the number of days someone is actually going to look at the report, which will decrease the amount of data stored in the database. To get rid of the logs, the setting Clean Logs Periodically needs to be set to True, so don’t change it.

The Server-wide Default Logging Level is by default set to Basic. The information provided at this level is generally what is needed to troubleshoot any issues. Don’t set the logging to None just to save space. If the SSIS code ever crashes, the person doing this may be cursed. If you have simple jobs though, you may find the logging level of Performance may be adequate. Check out Microsoft’s documentation on Logging to better understand the differences between levels.

Lastly if using version control in another application, such as TFS, there may not be a need to set the Maximum Number of Versions per Product to 10. Generally speaking most people don’t look past the last 3 versions. The number should reflect what is practically, which is nearly always less than 10.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

 

 

Feb 24 2016

Who Do You Work For?

Author: Ginger Grant • Discussion: 4 Comments

“Who do you work for?” seems like an obvious question, after all you work for Company X or yourself, but is that really the answer? I recently read an interesting blog post from Mike Fal b | t who recently started a new job and talked about the things he finds important when selecting a position. After reading his post, I thought about a comment I heard about working which has stayed in my head ever since.

You work for your Immediate Manager

bossA few jobs ago, I was working for a company which was purchased by another company. Changes were coming, but they hadn’t happened, yet. I was working for Tyler, who was soon not going to be my manager. He knew I was not going to be working for him soon, and at that time I didn’t know I would be getting a new manager. We had a conversation about the upcoming changes where Tyler told me  you really don’t work for Company X, you work for your immediate boss. He’s right. After all working for a company is one thing, but where the rubber meets the road is when someone directs what you do during the day. The ability to make your life miserable or make you happy to come to work comes from your supervisor, not from the company. One person’s input is a lot smaller picture than Company X, more immediate, and more intense. When Tyler was not my manager, I realized how right he was. I didn’t think much of the new manager and left.

How do you Determine Where to Work?

Because people are such a large part of the working environment, a change in management is a big deal in determining if you want to stay or not. It also explains why two people who work for Company X may have two different perspectives, especially if it is a large company. A friend of mine quit Company Z, which is a large company that continually has very high marks for what a great company it is. Employee surveys continually rank it near the top of several Best Company’s for Employees to Work lists. He quit because he didn’t like his manager. He thought a number of people we knew in common were great, but that couldn’t overcome his bad manager.

Weighing the Criteria

When management is not a consideration, then the criteria change from people to tasks. Quality of work, ability to learn and apply new skills, career advancement, monetary compensation, working environment, scheduling are important considerations. Since rarely is one able to really determine the management question prior to being in a position, these tangible criteria are the only thing one can use to make a decision on where to work. Many times though, this information isn’t enough, and you only find out after you make a decision if it was the right one.

 

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Feb 14 2016

Why the Power Pivot Tab may be Missing in Excel 2016

Author: Ginger Grant • Discussion: 11 Comments

If you have recently upgraded to Excel 2016 and don’t have a Power Pivot tab available to you, there are a couple things you can do. You might want to go to the Developer tab and click on the COM Add-ins icon, which will display a list of the Add-ins available. If Power Pivot is available, selecting this options will allow you to use Power Pivot. But what if Power Pivot isn’t there? Well unlike Excel 2013, there is nothing for you to go download from Microsoft. Chances are if you don’t have the option listed in the COM Add-ins window, you are going to have to part with some additional cash to get the Power Pivot tab.

Business Analytics Features are no longer included in all Versions of Excel

Power Pivot is considered a Business Analytics feature, but What-if Analysis and Forecast Sheet are not. Seems to be an interesting definition of Analytics Features. If you want Power Pivot, you are going to have to pay for it. Here’s

Extracted from https://blogs.office.com/2015/09/18/new-ways-to-get-the-excel-business-analytics-features-you-need/

Extracted from https://blogs.office.com/2015/09/18/new-ways-to-get-the-excel-business-analytics-features-you-need/

a clip from Microsoft’s website intending to clear up what versions include Power Pivot. Looking at this graphic, this is no way lists all of the versions of Excel which Microsoft sells. What about Office 365 Enterprise E1? Surely you would get Power Pivot functionality with that right? No. How much more money is Power Pivot going to cost you? Well, if you have Office 365, you are paying $8 a month for the Office software, including Excel. There is no guarantee that spending more money will provide Power Pivot though. Office 365 ProPlus, which has Power Pivot, will run you $12 a month. If you have Office Small Business Premium, which runs $12.50 a month you won’t get Power Pivot. Check the version of Excel 2016 by going to File->Account then look at what is listed. If the version isn’t Office 365 Pro Plus or one of the other versions listed in the graphic, there will be no way to make Power Pivot appear.

Power BI: The tool for Desktop Data Analytics

Excel 2016 is the first version to be released after Power BI moved to it’s own application. While Excel received the visualizations of Power BI, Excel did not inherit all of the data modeling capabilities of Power BI. The difference in Power Pivot is the diagram now shows the directionality of the relationship through arrows, a far cry from Power BI’s data modeling capability. Even when it is available, Power View is turned off in Excel 2016, and the reason given for this is “The interactive visual experience provided by Power View is now available in Power BI Desktop”. This sounds like a clear drive to separate the two products. I understand the desire to separate the two products. Changing the licensing model where people don’t know if the version of Excel they have will do what they need it to do, I don’t think is a good way to get people to use Power BI.
Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Feb 09 2016

2016 Releases of SQL Server and Excel

Author: Ginger Grant • Discussion: No Comments

It’s been 2016 for over a month now, so hopefully you are still not changing 2016your 5 into a 6 still. Chances are you haven’t changed your software to reflect the new year yet. Office 2016 came out in September of 2015 and SQL Server 2016 hasn’t been released yet. It’s hard to keep up with all of the version changes that have come out, especially when you throw in Power BI which is has something new every month. If you know you are going to be upgrading to one or both of these versions, or want to learn more about SQL Server or Excel so you can decide if  is worth the upgrade effort, this week I might be able to help out. I am going to be talking about the 2016 version of SQL Server on Wednesday, February 10 at the Arizona SQL Server User Group meeting and then talk about the 2016 version of Excel on Thursday at the Excel BI SQL Pass Virtual Chapter. If you are not in Arizona right now, you are missing out as we are having Department of Tourism weather of 80 degrees. You can get back to me in August when I am melting in the 115 degree heat.

Polybase in SQL Server 2016

Since there are many new features to talk about in SQL Server 2016, I picked Polybase. As big data matures many places are looking to keep their structured data right where it is and create an HDFS cluster to store other data. Polybase allows SQL Server 2016 users to look at both all in one place.

Excel 2016

It’s been a while since September 22, 2015, the date Office 2016 was released , but I still know very few people who have upgraded. I’ve been to a few clients that hope to upgrade to Excel 2012 this year. In this session, I will show where things got moved and renamed, what’s new and what is on the deprecated list. If you don’t have 2016 installed yet, or if you do and wonder where Power Query went, please join me to hear all about it. Generally speaking, the Virtual Chapters are posted on Youtube, and when they are I will have a link available. Unfortunately for those who attended my last Excel BI Virtual Chapter Meeting, due to technical difficulties that recording is not available, but hopefully this time everything will work.  When the recording is available I will make sure a link it is available on my blog for those who can’t make it.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur