Adatis

Adatis BI Blogs

SQL PASS Summit – Day 1

Having spent several days enjoying Seattle and getting to know and enjoy the city the conference is now underway. Yesterday there were some introductory meetings where we got to meet some of the other attendees and get a feel for the environment, today everything was in full swing. The morning started with the opening keynote presented to a huge audience. There were demonstrations of some really exciting new features – in one we observed Kinect being used with Power Map in order to track customer movements to observe customer interest in different parts of the store. We saw some great looking Power BI dashboarding functionality with the ability to drillthrough into detailed reports. As well as this we saw some further enhancements SQL Server Azure and on-premise integration including a new stretch functionality which will allow users to seamlessly ‘stretch’ their data into the cloud, keeping the most frequently queried records on premise and the other ones in the cloud. We also saw a Columnstore index being created on an in memory table! Miguel Lopes gave a talk on the new features in Power Query where we saw the ability to query using ODBC and support for Analysis services connections, on the whole though whilst I think the ODBC will be particularly useful for some situations, much of this talk was giving an overview of Power query as a whole rather than demonstrating new functionality. The integration of SSIS and power query in future was mentioned, however no dates have been set for this and we are told that this may (or may not) be available at some point in 2015. Jen Stirrup gave an interesting overview of some of the features available in R, the session was so popular that many people had to sit round the front on the floor! Niko Neugebauer’s contagious enthusiasm when presenting his session on ETL Patterns with Clustered Columnstore indexes was great to see and I picked up a few tricks here that were quite handy when working in 2014 environments. I also very much enjoyed John Welch’s session in Continuous Delivery for Data Warehouses and Marts, this is something I myself have been involved with a lot recently and it was very interesting to see his methods of achieving this and also to discover that in many cases we were both doing things in the same way :) Overall the day has been very interesting, we have seen some exciting new features announced today and some significant enhancements to the Power BI product range, it seemed to me for some time that the lack of dashboarding functionality in Power View was holding it back and I think many people will be very pleased with this new functionality and the further enhancements to the Azure service.

Getting Started – Continuous Integration – Team City Installation and Setup

I have been working on a project where we have been using Continuous Integration (CI). Having used this for several months I am very enthusiastic about it. To give a brief explanation, using CI means that when you check in your source the project will be built built and (configuration dependant) deployed to a shared development environment. Whilst this takes a little getting used to I have found the benefits are significant – errors during the build are immediately spotted and with the automatic deployment to the shared environment errors or code clashes can be identified quickly. In this blog post I will be detail the setup of Team city in a windows and TFS environment for use in CI. The free edition of Team City will be used which allows access to all features and use of 3 build agents should be sufficient for most BI development environments. In subsequent blog posts I will cover the setup of automated build and deployment for Database and SSIS packages using MSBuild. Preparation 1. Download Team City (Windows edition) from: http://www.jetbrains.com/teamcity/download/ 2. Create a domain account which will be used to run the Team City service. The account does not require any special permissions on the network but should be granted local administration rights on the machine selected as the build server. Later when we use Team city for deploying projects this account will need appropriate permissions on the target servers to deploy the Databases, SSIS Projects etc. 3. Download the latest version of the JDBC driver for SQL Server from Microsoft, available from http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774 4. Select a SQL Server instance to be used to hold the Team City metadata (2005 or higher). Mixed mode authentication should be enabled. 5. Create a database on the server to hold the Team City metadata. I suggest calling it TeamCity and using the Full recovery model. Ensure that the default collation of the database is set to SQL_Latin1_General_CP1_CS_AS this is generally NOT the default configuration. 6. Create a non windows account and give it full permissions over the TeamCity database created in step 5. Installation 1. Run the Team City setup program as an administrator. 2. Install to the default directories and Select all features. 3. Select the option to install as a service. When prompted use the service account setup in Preparation Step 2 for Team City Agent & Scheduler (not the SYSTEM account). 4. Choose a default port for the Team City Agent. This will be used when you access the administration portal to view build progress and make configuration changes. For the purposes of this tutorial I will use 8083 which will make the address of the when viewed from the local machine http://localhost:8083/  5. You should be presented with a screen like the below. If the configuration looks correct click save.   6. Select to run immediately upon completion of the install. 7. Open your web browser and navigate to http://localhost:8083/mnt (change port number if required) You should be presented with the following screen: 8. Click Proceed. Following this (if you do not click proceed the directory will not exist) Install the JDBC driver from preparation step 6 and place the drivers (sqljdbc4.jar & sqljdbc.jar) in the C:\ProgramData\JetBrains\TeamCity\lib\jdbc directory (assumes default installation paths). 9. When prompted to setup access to a database chose 'MS SQL Server' as the instance type. Set the connection properties and name of the database to the DB setup in step 5 of the preparation using the SQL account from step 6 of the preparation. 10. Read and accept the license agreement. 11. Create an Administrator account for Team City configuration and ensure that the credentials are stored securely for later use. The next step is to connect TeamCity to your source control, detailed here: http://blogs.adatis.co.uk/blogs/jeoc/archive/2014/09/19/configuring-team-city-to-connect-to-visual-studio.aspx