|
|
|
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: Ali Rizwan
Posted:
January 23, 2012 at 10:34 AM
If you are new to SQL Server 2012 like me, you might run into some issues when you are trying to create some Tabular Model projects. A Tabular Model is simply a new type of server mode in SQL Server 2012. It utilizes a new storage mode known as the Vertipaq storage engine that enables higher compression rates by storing and compressing data across columns. The columnar compression enables faster response times when the data is less variable in a given column as oppose to the variability
... [more]
By: Ali Rizwan
Posted:
October 17, 2011 at 4:37 PM
I wanted to share my experience with the new features of Mobile Entrée and how to leverage your existing Microsoft Business Intelligence investments as a starting point to building out the mobile platform. Mike Burger posted earlier regarding Mobile Entrée (found here). He stated the fact that it can integrate with SharePoint and render reports in Excel in the mobile browser. These are very compelling features in trying the solution out. I am impressed with the ability to finally share my E
... [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: Teo Nedev
Posted:
August 17, 2011 at 11:29 AM
If you happen to install PowerPivot on a SharePoint server that has prior SQL Server installation (SQL engine, Analysis Services, Reporting Services etc.) you will run into problems.
You may see error that the installation was unable to register Microsoft.AnalysisServices.SharePoint.Integration.dll assembly. Or if you are really lucky, you may get by far more helpful error message "Object reference not set to an instance of an object".
You generally need 3 steps in order to
... [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: Brian Ringley
Posted:
May 27, 2011 at 10:05 AMLast week, I attended 2 days of Fast Track Data Warehouse and 3 days of Parallel Data Warehouse training at the Chicago Microsoft Technology Center presented by Jim Carroll, Ross LoForte and John Plummer. It was an intense, thought provoking week, to say the least, with so much information I could not possibly touch on all of it in this one post. Instead, I will provide a high-level summary of the similarities and differences between the two approaches and the new market penetration Microsoft ho
... [more]
By: Brian Ringley
Posted:
April 29, 2011 at 10:23 AMMicrosoft recently released a free Excel 2010 add-in which uses PowerPivot to analyze your schedule from your Outlook calendar. The add in, named Calendar Analytics Tool, has two main benefits. The first is that it is extremely useful (and cool). By using the familiar Excel pivot table functionality, including Slicers, you have the ability to graphically display all of the meetings for a given period of time by: meeting topic, who was involved, categories and the details behind the high level st
... [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: Ali Rizwan
Posted:
March 29, 2011 at 9:57 AMIntroduction PowerPivot builds on existing Excel features and extends their functionality. Those familiar with Excel are aware of Pivot tables. Excel typically involves creating a pivot chart with one single data source. PowerPivot allows users to extract data from various sources. These data sources can also be linked to one another with in a PowerPivot workbook. This feature allows you to create sources which maybe from a broad list of sources and link them all in your work book. Slicers are
... [more]
By: Brian Ringley
Posted:
March 24, 2011 at 10:46 AMThis method has been blogged about in various places on the web. Below is my take on a recent implementation of this method with some screen shots, helpful hints and some things to lookout for. One approach to dynamically allowing / denying access to the data contained within an Analysis Services Cube via PerformancePoint is through Custom Data. Custom Data allows Analysis Services to include the currently authenticated user name as a parameter on the custom data field in an Analysis Services c
... [more]
By: Brian Ringley
Posted:
March 21, 2011 at 4:45 PMMobile BI is gaining a lot of attention with the huge increase in the demand for mobile devices. This topic routinely comes up in conversations with our customers as they (along with everyone else) want to be able to receive the same analytics and user experience they interact with on their desktop/laptop on their smart phone/tablet. They also expect the interaction to be achieved without loss of functionality. No small task, but then again, the customer is always right. Besides the much smaller
... [more]
By: Brian Ringley
Posted:
March 11, 2011 at 1:22 PMRecently, I was tasked with creating an internal Dashboard which allowed for Practice Leadership to track metrics related to sales pipeline change. The first thing that came to mind was a demo I had previously seen from someone at Microsoft. The functionality consisted of selecting a KPI in a Scorecard and having a Trend Chart on the Dashboard dynamically change to give a historical view of the selected measurement over time. The interactivity between Dashboard objects is new to PerformancePoint
... [more]
By: Andrew Schwenker
Posted:
January 25, 2011 at 10:44 AM
Storing documents on a file server and connecting to them with metadata stored in a database is rather common. Let’s talk about the situations in which you’d like to surface the metadata attached to the file in Business Connectivity Services (BCS) and elsewhere using a single Windows Communication Foundation (WCF) service. To return a file to SharePoint through BCS using any connector, you need to use a StreamAccessor method instance. The StreamAccessor gives SharePoint access to the underlyin
... [more]
By: Brian Ringley
Posted:
January 18, 2011 at 2:08 PMSo you’ve spent an extraordinary amount of time and resources implementing a top notch BI solution at your organization and no one is using it, or at least they say they are not. So what now? Well, chances are people are using it without even knowing. Many organizations face this dilemma going live with their first BI solution. Users of the system either do not use the system because they are not familiar with the tools, do not know it exists, or, in fact, are using the system without knowing i
... [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: Andrew Schwenker
Posted:
December 28, 2010 at 9:41 AMHere’s the scenario: you’ve got two Windows Communication Foundation (WCF) services that return related data. For instance, let one of them be a service that returns People and let the other be a service that returns Documents. Every Document was authored by one or more Person. Since these services are separate Line of Business (LOB) systems, you must input them separately into Business Connectivity Services (BCS). You want to associate these two items together. If you have a one-to-many re
... [more]
By: Brian Ringley
Posted:
October 25, 2010 at 2:16 PMJust as the creation of the internet and the World Wide Web has forced companies to bring on new technologies and consequently create new roles that they did not have 20 years ago, increasing data volumes and analytical power, as it relates to Business Intelligence, has had the same effect on organizations in order for them to be competitive. The emergence and adoption of the Data Steward role is more apparent and has increased significantly over the past several years. This role is becoming mo
... [more]
By: Brian Ringley
Posted:
September 20, 2010 at 10:30 AMOne of the most useful features of Excel 2010 and Pivot Tables is the new Slicer component. The Slicer allows end users to more easily visualize what dimensions and attributes are available to “slice and dice” the data and what has been selected as filter criteria. In past versions of Excel, the Report Filter was available to achieve the same end result but did not provide a useful way of displaying what filters were selected. The below example using the Report Filter option shows that “multipl
... [more]
By: Brian Ringley
Posted:
September 17, 2010 at 12:39 AMMicrosoft SQL Server Integration Services has some great features and is extremely useful for cleansing and importing data into SQL Server, however; not every situation warrants its use. One instance where I found SSIS to be less useful was when importing a lot (just under 100) of XML source files into a SQL data warehouse. For this approach, it was far less time consuming to use the T-SQL Bulk Insert command for XML Data over the Data Flow task with a XML file source in SSIS. The first thing i
... [more]
By: Brian Ringley
Posted:
September 10, 2010 at 11:27 AMWhile assisting an organization in deciding if they should obtain a Business Intelligence solution; which vendor is most adequate to suit their needs and actually implementing the system may seem like the hard part, in many cases, it turns out to be the easy part. In my experience, the hardest part lies in the adoption and “buy-in” of the solution to those within the organization. Many times it has been the employee’s sole purpose to manually compile information from a variety of sources and mak
... [more]
By: Brian Ringley
Posted:
August 23, 2010 at 2:19 PMWhile working for a global executive search firm during the height of the US credit and housing crisis, the SVP – US Finance became anxious as the firm was having an unusually difficult time collecting past due invoices from clients. This data was a key component of their financial Business Intelligence system and readily available to Accounts Receivable (AR) Managers throughout the world on their personal Dashboard page. However, they were either not using the Dashboard for this information or
... [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 8:22 PMExcel Services (Excel):
A new addition to Excel within Office 14 is Sparklines. Sparklines are an easy way to show a trend within a data set. Interestingly enough, even though these do look like a type of graph, they are simply conditional formatting! The new Sparklines can be found within the "Insert" tab of the ribbon as shown below:
There are three different types of Sparklines. The Line, Column, and Win / Loss. Once you have inserted some Sparklines into an Excel workboo
... [more]
By: Mike Burger
Posted:
September 29, 2009 at 8:06 PM
Welcome to the second part of a two part blog series covering the functionality of PerformancePoint within SP2010. In the first part I covered the functionality of PerformancePoint specifically in regard to how it is integrated with SharePoint. In this part I will more specifically cover the enhancements to the PerformancePoint content such as Dashboards, Scorecards, Filters, etc. The topics for Part 2 are listed below.
Part 2:
General Enhancements - there have been improvements in ho
... [more]
By: Mike Burger
Posted:
September 29, 2009 at 7:52 PM
I am actually quite impressed with the initial evaluation of the new functionality with PerformancePoint and SharePoint 2010 (SP2010). In a series of two blogs I will try to point some of the big changes and improvements that are going to be included with the release of SP2010, specifically in relation to PerformancePoint Services. Some of the topics I will cover within the two blog series include:
Part 1:
Ease of Installation - the setup to get PerformancePoint up and running is a lo
... [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: Travis Nielsen
Posted:
May 15, 2009 at 10:47 AMThere’s a great scene in the movie Alien where Tom Skerritt’s character, Dallas, has a (somewhat eerie) conversation with the ship’s computer, named Mother. By conversation, I mean Dallas asks the computer questions and Mother responds with meaningful answers. Whenever I see it, I think of two things: I’m sure its not a mistake that, given the name of the computer, the room looks like a womb. One day, we will all interact with computers like this. You may understand why I thought of this afte
... [more]
By: Jeff Monnette
Posted:
April 18, 2009 at 10:07 AM
Did you know that SQL Server Analysis Services includes very powerful data mining capabilities that can help organizations find hidden patterns in their data and unlock the data's real value? These capabilities were introduced with Analysis Services in SQL Server 2000 and really came of age in 2005. With SQL Server 2008, Microsoft has introduced a new set of client tools that integrate with Excel and Visio to make using the data mining features easier than ever.
I recently gave a prese
... [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]
|
 |
|
|
|
|
 |
|
|
|
|
|
|
|