By: Mike Burger
Posted:
January 26, 2012 at 12:57 AM
Step-by-Step Instructions
Before getting started I would like to point out the other blogs in this series. If you do not need help with the installation of the SQL Server 2012 bits then perhaps one of the other blogs will be of help.
Installation Order:
SQL Server 2012 (RC0) Business Intelligence Configuration
SQL Server 2012 (RC0) Installation and Configuration
SharePoint 2010 Installation for SQL Server 2012 (RC0)
Additional SSAS Instance Installations in SQL Server 2012 (RC0)
... [more]
By: Mike Burger
Posted:
January 26, 2012 at 12:57 AM
Overview
With every new version of SQL that is released there are always a few changes and challenges related to the installation and configuration of all the tools within the BI stack. Admittedly, even with the ever changing toolset within the BI stack, this has gotten easier over time. Furthermore, the deep integration with SharePoint now makes it impossible to setup a VM and use the BI stack without it.
If you have never installed SQL server or any tools on the BI stack don't worry! The
... [more]
By: Mike Burger
Posted:
January 26, 2012 at 12:56 AM
Overview
Please check out the other blogs in the series related to setting up each of the main SQL Server 2012 features. Links to each of the related blogs are shown below.
Installation Order:
SQL Server 2012 (RC0) Business Intelligence Configuration
SQL Server 2012 (RC0) Installation and Configuration
SharePoint 2010 Installation for SQL Server 2012 (RC0)
Additional SSAS Instance Installations in SQL Server 2012 (RC0)
SQL Server 2012 (RC0) PowerPivot for SharePoint 2010 Config
... [more]
By: Mike Burger
Posted:
January 26, 2012 at 12:55 AM
Overview
If you do not already have SQL Server 2012 PowerPivot and SharePoint 2010 installed please check out one of the earlier blogs in this series. All of the blogs in the series are listed below. If you did not follow the SharePoint 2010 Installation for SQL Server 2012 blog then your PowerPivot configuration might vary slightly from what I shown below.
Installation Order:
SQL Server 2012 (RC0) Business Intelligence Configuration
SQL Server 2012 (RC0) Installation and Configurati
... [more]
By: Mike Burger
Posted:
January 26, 2012 at 12:54 AM
Step-by-Step Instructions
I would like to point out that this installation is actually pretty simple for a brand new BI environment. If you are new to the series, please click on one of the other blogs in the series to get up to speed. If not, continue on!
Installation Order:
SQL Server 2012 (RC0) Business Intelligence Configuration
SQL Server 2012 (RC0) Installation and Configuration
SharePoint 2010 Installation for SQL Server 2012 (RC0)
Additional SSAS Instance Installations in
... [more]
By: Mike Burger
Posted:
August 24, 2011 at 10:40 AM
In a world where data is collected for almost everything, companies often find they have trouble figuring out what to do with it. Theoretically companies could report on all their data, however, the larger the dataset gets the more money it will take to setup the infrastructure to support it. For most companies, especially those that are new to Business Intelligence and Data Warehousing, this is not realistic.
For this reason it is just as important for companies to put in the time and e
... [more]
By: Mike Burger
Posted:
July 6, 2011 at 12:13 AM
It is almost impossible to have a conversation about Business Intelligence these days without talking about mobility. Mobile internet and data consumption is growing rapidly and it doesn't look like that is going to change any time soon. There are many mobility options to choose from but not all of them work on all mobile devices. Some require heavy custom application development so many companies are investing heavily in buying their work forces smart phones or tablets so that all users can c
... [more]
By: Mike Burger
Posted:
June 2, 2011 at 12:56 AM
Problem:
During one of my projects I found that the SSAS query log table seemed to be periodically getting truncated. I would notice this because I would run some queries against the cube, watch them get logged into the logging table, and then a few hours later they would be gone! I could not figure this out and it kept erasing all of our historical usage data! I eventually tracked down the issue, explained in this Microsoft Knowledge Base Article. Every time we were using Synchronization to
... [more]
By: Mike Burger
Posted:
June 2, 2011 at 12:37 AM
Have you ever used the SSAS out of the box functionality to log queries? This can be used to see how many queries are run, how long each query runs, and which users are running the queries. If not, check out this blog for a description of some dashboards that can be created from this log table. If so, have you ever noticed that the time SSAS attaches to each query is in UTC?
Here is the create statement for the default query logging table created by SSAS when logging is turned on:
CREATE T
... [more]
By: Mike Burger
Posted:
June 2, 2011 at 11:46 AM
One of the most popular questions after an SSAS cube is moved into production and users begin accessing the data is, "Who's using the cube?" It makes sense right? After spending a lot of time and money getting an analysis cube built, who wouldn't want to know what the usage was like? Luckily, there is an easy and slick way to surface all of this information.
First, turn on query logging for SSAS. This can be done in SSMS. Connect to the cube, right click on the instance of SSAS, an
... [more]
By: Mike Burger
Posted:
March 29, 2011 at 2:06 PM
A client found a cool add-in for Excel the other day that allows users to view the underlying MDX associated with Pivot tables called PTPower. This is great because beginning MDX developers can use it to quickly view the underlying MDX for a Pivot table and use it to build elements within the SSAS cube, filters on PerformancePoint dashboards, and SSRS reports. In fact, in most cases the MDX can just be ripped directly from Excel with only slight cleanup. This tool is also useful for end users
... [more]
By: Mike Burger
Posted:
January 7, 2011 at 3:48 PM
Typical to most BI projects, the biggest challenge as a consultant is to quickly gain an understanding of the client's data. Oftentimes I get placed in a room with all IT personnel, and I am expected to get up to speed with all the systems and data as I gather requirements to build out some reports or a data warehouse. Working with IT is very helpful for understanding the use and impact of the chosen technologies on the project; however, it often does not help me understand the business. I don
... [more]
By: Mike Burger
Posted:
January 7, 2011 at 3:15 PM
It is very easy for SSIS packages to get messy if you don't adhere to some strict naming conventions. As a consultant, my job is to try to make the SSIS packages as easy to understand as possible. One way to do this is to make sure to use naming conventions for all your tasks and components so that someone can easily tell what they are looking at.
I have been using naming conventions in my SSIS packages from a post I found awhile back. The tables I am displaying below come from a Microsoft M
... [more]
By: Mike Burger
Posted:
January 7, 2011 at 2:58 PM
The cube synchronization in SQL 2008 is vastly improved compared to SQL 2005 (much faster). On the target (or production) server you have the option to run the Synchronization Wizard within SQL Server Management Studio (SSMS) to pull data from a source (or staging) server, but what if you want to run the ETL and process the cube on a staging server and then push the changes to the production server once all processing is complete? This can be done by simply adding a step at the end of your SQL
... [more]
By: Mike Burger
Posted:
January 7, 2011 at 2:26 PM
Problem
Within most BI projects you need to in some way handle slowly changing dimensions in your ETL solution. Be it Type 1, Type 2, they are unavoidable because truncating the dimension and fact tables and reloading from scratch is not an option unless the data warehouse is very small. On most of my projects I have struggled with how to handle Slowly Changing Dimensions in SSIS. This is not because I don't know how to use them; it is because the Slowly Changing Dimension task in SSIS does
... [more]
By: Mike Burger
Posted:
January 7, 2011 at 2:01 PM
Overview
One issue that I have run into in every BI project is data quality and data cleansing. Oftentimes data cleansing can be excluded from the scope of a BI project since the client is often afraid to even tackle the problem. On top of that, data cleansing can often be a project all by itself. There are a ton of Master Data Management (MDM) software solutions on the market, and recently with the release of SQL Server 2008 R2, Microsoft has joined the space with the release of Master Data
... [more]
By: Mike Burger
Posted:
October 20, 2009 at 2:20 PMCheck out my SharePoint 2010 Business Intelligence Posts:
1. SharePoint 2010 Insights - Reporting Services and Chart Web Parts
2. SharePoint 2010 Insights - Excel Services and Visio Services
3. SharePoint 2010 Insights - PerformancePoint Review Part 1 of 2
4. SharePoint 2010 Insights - PerformancePoint Review Part 2 of 2
Enjoy!!
... [more]
By: Mike Burger
Posted:
October 15, 2009 at 4:51 PM
If you are like me you have spent a lot of time fighting with BIDS to get every connector within SSIS to line up perfectly straight...ahhhhh!!!
Thanks to Steve Jackson for this great tip on how to easily format and align all of the items within an SSIS package. I have fought with this before and had become excellent at holding down the CTRL key in order to slowly move items once I drag them close enough to almost make all the lines straight.
Can't wait to try this out! I wonder if th
... [more]
By: Mike Burger
Posted:
September 29, 2009 at 7:36 PM
Introduction:
I recently worked on a quick SSRS (SQL Server Reporting Services) project with a client that had a need to be able to query large datasets (potentially over 300,000 rows by 30 text columns wide). The report needed to be very dynamic, meaning that the report would need to allow the end user to run the report to return all of the data, or if they chose, filter on any of the columns to limit the end result. Not only this, but the query to pull the data needed to join to almost as
... [more]
By: Mike Burger
Posted:
September 29, 2009 at 12:59 AM
Overview:
Oftentimes when developing a cube there will be a need to limit which dimensions and measures a user can view within the cube. For the purpose of this blog, the steps and processes needed to develop a solid security model are out of scope; however, I will offer four different techniques which can be used in order to test the cube roles once they are setup.
Ideas for testing roles come from my personal experience as well as from the book, "Expert Cube Development with Microso
... [more]
By: Mike Burger
Posted:
September 1, 2009 at 7:57 PM
Done with Session 01 of the 24 PASS Webinar. This was by far the largest live meeting I have been a part of with over 450 users. I will not have time to write a complete review of all the webinars, however, I will add updates in this blog posts comments as I go along!
For the first session the sound did seem to cut out a bit, and some of the slides were a bit behind, but overall a good start to the 24 hour event. Taking notes the best I could as the webinar went along, these were the
... [more]
By: Mike Burger
Posted:
September 1, 2009 at 6:53 PM
I am about ready to start my first of eleven meetings for the 24 hours of PASS webinars. I only have two webinars tonight with a break in between so I should have time to add updates as I go along.
It will be a little more difficult to add updates as I go tomorrow since I have meetings every hour from 4am until 11am, and then again from 2pm to 4pm. Even though the day is packed I should still have time to add comments to my blogs in order to quickly add updates (rather than write a full
... [more]
By: Mike Burger
Posted:
September 1, 2009 at 1:54 PM
Starting tonight PASS (Professional Association for SQL Server) will begin broadcasting webcasts for 24 hours straight. These webcasts include many topics, however, I am most interested in those surrounding SQL Server Maintenance and Business Intelligence. As of now I plan on attending at least 11 of these sessions (more depending on my ability to keep my eyes open!) which start tonight (9/1) and run every hour until the conclusion tomorrow night (9/2). Here is a list of all the webcasts
... [more]
By: Mike Burger
Posted:
June 23, 2009 at 12:19 AM
Introduction:
In the scenario I am about to describe trusted accounts and domain accounts were used in order to allow Reporting Services to run in SharePoint Integrated mode. This was the initial installation of SSRS within the organization and needed to be done using trusted and domain accounts since SQL Server Reporting Services (SSRS) was configured in a multi-server environment without Kerberos enabled. It was our intention to provide functionality so that if a user was given access t
... [more]
By: Mike Burger
Posted:
June 23, 2009 at 11:36 AM
Introduction:
There are a couple logical steps to take to troubleshoot any error or issue that arises while users attempt to access the SSRS reports from a report library in SharePoint integrated mode. If a user complains that a report does not properly render or that an error is given after clicking on a report there are a couple logical steps to take in order to quickly identify the issue and resolve it. Some steps that I typically take are listed and described in the following section.
... [more]
By: Mike Burger
Posted:
June 23, 2009 at 10:19 AM
Introduction:
It is sometimes inevitable that data within an SSIS package will need to be loaded from flat files. If you have ever had to develop with SSIS you know that it can be quite finicky with its data types. I have found that it is sometimes even worse when the data is coming from flat files because the package would fail before I could even apply a data transformation within the data flow task. Below there is an example of a common error I get when working with flat files in SSIS:
... [more]
By: Mike Burger
Posted:
March 30, 2009 at 2:54 PM
Introduction:
I was recently tasked with an interesting requirement from a client to use only SQL to break an unknown number of records into 7 different equal buckets or groups based on the total number of records returned by the query. Let's work through a simple example first before applying the SQL logic. This logic relies on the fact that the number of data groups is known ahead of time and does not change. Assume that we are given the following result set:
Record
Calenda
... [more]
By: Mike Burger
Posted:
March 30, 2009 at 2:18 PM
Introduction:
I was recently challenged with creating a reporting database off of a lot of source tables that had no timestamp associated to any of the records. In order to allow for historical analysis of the data, the solution was to create an SSIS package that took a snapshot of the data each quarter. However, since the data in the underlying tables could change more frequently than once a quarter it was important to run the package every day so that reports would be up to date.
This wa
... [more]
By: Mike Burger
Posted:
February 18, 2009 at 4:14 PM
Introduction:
The Business Intelligence team at PointBridge was recently sent the following email:
Guys,
How would we do these:
1. Display on one dashboard only the KPIs that are red (or Y or G) from an entire SharePoint site collection (SC)
2. Display on one dashboard all KPIs in a SC that if the current trending continues will be red in a certain predefined timeframe (i.e. 2 weeks, 1 month). It would be nice to use a parameter to allow the user to determine the timeframe
... [more]
By: Mike Burger
Posted:
February 16, 2009 at 2:48 PM
I have recently been sharpening up on my SSRS skills and I have decided to take time to share what I feel are some valuable tips and tricks.
This is a large list but can serve as a great resource to refresh your memory before starting any SSRS project.
General Design Tips:
1. Always use views or stored procedures for data sets. This can allow reports to continue to run properly even if a field is added to and underlying data source. Additional fields can also be added int
... [more]
By: Mike Burger
Posted:
December 23, 2008 at 2:36 PM
Overview:
I had recently been tasked to perform my first installation of PerformancePoint Monitoring Server 2007 onto a Server Farm. Most of my experience to date had been using the SQL Server 2005 Business Intelligence suite (SSIS, SSAS, SSRS, and PerformancePoint), however, I had not yet had an opportunity to do much of the actual configuration and / or installation. This was especially true for installations pertaining to Server Farms.
The hope of this blog is to help someone else tha
... [more]
By: Mike Burger
Posted:
December 23, 2008 at 1:38 PM
Problem:
I was recently tasked with creating an SSIS package to pull data from a 32-bit version of Windows Server 2003 with an installation of Oracle 9i Database into a 64-bit version of Windows Server 2003 with an installation of SQL Server 2005. On the surface this seemed like a simple task since SSIS has built in Oracle data connections. However, this proved to be a lot harder than I could have ever imagined.
After initially having trouble connecting to the Oracle 9i Database I took s
... [more]
By: Mike Burger
Posted:
December 16, 2008 at 4:30 PM
In Part 1 of this blog I gave a theoretical overview of how to deal with slowly changing dimensions as well as some of the general advantages and disadvantages of each technique.
Moving away from the theoretical overview and more towards the actual practice of the theories, my first attempt to handle slowly changing dimensions was within SQL Server Integration Services (SSIS) using Business Intelligence Development Studio (BIDS). Microsoft developed a Data Flow Transformation to handle b
... [more]
By: Mike Burger
Posted:
December 16, 2008 at 4:23 PM
Overview:
Although quite prevalent within the world of Business Intelligence, certain situations can still make handling slowly changing dimensions quite challenging. Just recently I had been tasked to deal with slowly changing dimensions in order to allow data within a dimension table to properly update while making sure to retain the history of the changes within the table.
For me the main challenge with handling the slowly changing dimension in this case was performance. No matter w
... [more]
|
|
|
|
 |
|
|
 Solutions ArchitectMike Burger is a solutions architect for PointBridge. Mike has developed a great passion for business intelligence and has in depth information technology and business intelligence experience. Mike ha... [more]
|
|
|
|
|
|