Microsoft SQL Server – Page 2 – Cognitive Information

11 Mar 2014

0 Comments

How to set OLE DB Source data in SSIS

SSISMicrosoft SQL, properties, SQL, SSIS

Sometimes I have to learn things repeatedly before it sticks. I re-learned this tip from Tim Costello at last night's Oklahoma City SQL User Group presentation. We will get his presentation up soon. First, a big thank you to Tim Costello for coming to Oklahoma City to speak at our user group. Check out his site for more tips. This tip was just one tidbit from his presentation "Pro Tips: Tuning the data flow in SSIS". Watch for him at a SQL Saturday near you. Why you need to set IsSorted property on the OLE DB Source When preparing…

15 Jan 2014

By Allen

0 Comments

SSAS Cube Partition Slice ErrorCode=”3240034361″

SSASMicrosoft SQL, SQL SSAS, SSAS

Slices are a great way to improve query performance in a heavily partitioned SSAS Cube. The photo above shows where to add or change a Slice properties in partition properties dialog box. If you ever get the following errors when processing cubes, it has to do with the Slicer property at the partition level. The first error message tells you it is a slicer, the next few messages will indicate which partition puked. <Error ErrorCode="3240034361" Description="Errors in the OLAP storage engine: The restrictions imposed on partition slice where violated." Source="Microsoft SQL Server 2012 Analysis Services" HelpFile="" /> <Error ErrorCode="3240034318"…

21 Oct 2013

By Allen

0 Comments

PASS Summit 2013 – A First-timer’s Experience

Microsoft SQL ServerMicrosoft SQL Server, PASS, User Group

First let me say I came to Summit with a few plans, but no set expectations. After SQLkaraoke with Jason Horner in KC,MO, I learned to just show up to see what happens. I really came to learn from people who are better at their craft than I and catch up with friends I’ve made through SQL Saturdays. There were only about four sessions on my must-see list. My plans did include visits to the Microsoft Certification and testing area. Overall, I enjoyed the few sessions I attended, really loved the SQLfamily, learned from Itzik Ben Gan, met Dave…

14 Oct 2013

By Allen

0 Comments

Custom Chart Palettes in SSRS

SSRSReporting Services, SSRS

This article is a follow up to the Cognos article on creating a custom color palette. Custom palettes are pretty common when companies start to standardize their corporate image across all company communications. Make a copy of your report template. Name it 'MyCorp Chart Report' In the report area, add a chart Right-Click the chart object and choose Properties For Palette, choose 'Custom' In the properties window, not find the custom palette settings. A dialog box appears that allows you to select each color to include in the custom palette. Below I have set the first color in the palette…

14 Oct 2013

By Allen

0 Comments

Customized SSRS reporting template.

SSRSMicrosoft SQL Server, SSRS

Large reporting projects can be simplified with a good template as a starting point. Design templates can store the company header with logo, company color palette, font preference and layout. These are lessons learned while developing a reporting library for GE Oil & Gas, a company with very specific rules for their corporate image. We created one template for Portrait reports, one for Landscape and one for charts shown above (with corporate colors). GE even has their own company font. As a multi-national corporation, they normally need different logos displayed depending on the country where the report is rendered. That…

22 Aug 2013

By Allen

0 Comments

How to configure Reporting Services 2012 for SharePoint 2013 integrated mode

SharePoint, SSRSReporting Services 2012, SharePoint 2013, SQL Server 2012, SSRS

These are my installation notes because I did not find a clear tutorial for configuring SQL Server Reporting Services 2012 on a SharePoint 2013 server in Integrated Mode. I found lots of SharePoint 2010 articles, but SharePoint has changed and now Reporting Services is actual a Service Application within the SharePoint farm. This post is aimed at setting up a single server development environment. It assumes you have already installed the following: SharePoint 2013 SQL Server 2012 SP1 (Enterprise or Developer) SQL Server 2012 Reporting Services for SharePoint 1. Install the SSRS Service in SharePoint. This is best done through…

17 Aug 2013

By Allen

0 Comments

SSIS 2012 Parameters vs. 2008R2 Configurations

Microsoft SQL Server, SSISIntegration Services, Presentations, SQL 2008 R2, SQL 2012, SSIS

Update 8/17/2013: The SSIS Project downloads have been updated below. Abstract: In this session, Allen Smith, Business Intelligence consultant, compares SSIS Parameters in SQL 2012 to SSIS configurations in SQL 2008R2. The 2008R2 options allowed dynamic configuration across environments. In SSIS 2012 we review the new dynamic options, including Parameters and Catalogs. This upgrade takes SSIS from simply confusing to downright confounding. We will simplify these options so anyone with basic SSIS experience can follow along. Special thanks to our friends at GDH Consulting for hosting the December meeting of Oklahoma City SQL User Group. SQL Integration Server developers need…

10 May 2013

By Allen

0 Comments

Business Intelligence Forecasting in SQL and Cognos

Cognos, SSASBusiness Intelligence, Cognos, Data Mining, Microsoft SQL Server, SQL, SQL 2012

Note: This article is an update of a presentation given when I was at the University of Oklahoma's Health Sciences Center. At that time, I was using Cognos version 7.3. This article is updated for Cognos 10 and SQL Server Analysis Server 2012. Revisiting this subject ten years later, my opinion is that Microsoft has finally surpassed IBM Cognos. Most businesses, including medical clinics, want some method for forecasting business trends. The Cognos BI powerplay presentation tools contain the tools needed for basic forecasting. The trick in getting accurate forecasting is select the correct algorithm. I recommend trying the model…

30 Apr 2013

By Allen

0 Comments

How to fix “Usage Based Optimizer” connection errors in SSAS 2012

SSASerrors, SQL Server 2012, SSAS, Usage Based Optimizer

The Usage-Based Optimizer can be a very powerful tool for calculating aggregations, when it works. When it doesn't work perfectly, then it can be frustrating to figure out. The devil is in the details. If you move data between environment, then you have even more details to find and fix. Just for a quick overview, in Visual Studio for 2012 (BIDS for previous versions) your project properties deployment will point at an SSAS Server Instance which in turn has to have query logging configured to point to a SQL Server database. That database table, then logs info about the queries…

10 Apr 2013

By Allen

0 Comments

Getting SQL data into SharePoint

BI/DW Projects, Microsoft SQL ServerExcel, PowerPivot, SharePoint, SQL 2008 R2, SQL 2012, SSRS

In March, I was invited to speak at the OKC SQL Users Group and I never got the slide deck posted. So here it is to help get you started. Sharing SQL data in SharePoint from Allen Smith Session Level: Beginner SQL and Beginner SharePoint Session Abstract: Microsoft SharePoint is a collaboration software that provides a web based portal for business intelligence, content management and document sharing. In this session we will start with the basics of sharing a SQL table as a 'List' in Sharepoint. Next we move on to fun stuff of integrating SQL Server Reporting Services integration.…

Category: Microsoft SQL Server