|
|
|
By: Raja Ayyapusetty
Posted:
October 18, 2009 at 8:32 PMIt’s no secret that you are not supposed to touch SharePoint databases but there certainly are some scenarios which would tempt you to at least read from them; mostly for performance reasons. For example, if you want to do some kind of reporting with data pulled from lists spread over 15K sites, it’s not a terrible sin to read from content database directly. So, if in any shape or form your code is interacting with database directly you have some work to do to make it work in 2010. It shouldn’t
... [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: 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 1: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 1: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]
By: Michael Becker
Posted:
August 14, 2008 at 10:30 AM
I'm posting this here because I always have trouble finding it when I need it.
SQL Server 2005 ships by default with a utility for exporting objects to T-SQL scripts. Unfortunately, it doesn't allow you to export the data in your tables.
There is, however, a freely available add-on that does this for you – it works just like the wizard that's included out of the box, but the last step asks you if you want schema and data, data only or schema only.
You can get the "Microsoft SQL Serv
... [more]
By: Ethan Matyas
Posted:
May 19, 2008 at 9:48 PM
Like many organizations, we have many different repositories for data within our organization. Some of our data sits in SQL Server for applications like Microsoft Dynamics SL and Microsoft CRM. Most of our other data sits in SharePoint lists which we use regularly for both internal and external collaboration.
From time to time, we need to mash some of this data for various analysis purposes. Many times, we use tools like the BDC to import data into SharePoint but we recently had a case where
... [more]
By: Tony Pagnusat
Posted:
April 16, 2008 at 5:00 PMRecently Microsoft released a white paper covering the Best Practices for Maintaining your SharePoint SQL Databases. This is frequently a topic that doesn't get touched upon until SharePoint performance starts to degrade or when SQL Databases get to the size that they become unmanageable. Keep in mind that all content for your sites are contained in these databases so keeping them in tip-top shape should be high on the priority list. The white paper starts off discussing the process of checkin
... [more]
By: Matt Morse
Posted:
January 10, 2008 at 10:16 AMI routinely use SQL Server Management Studio to write SQL queries, ogle details of the database schema, etc. Last week, I needed to change the permissions on a SQL login, and I got the following message: That confused me, as I was logging in to SQL using Windows authentication, and I am a local admin on my machine; as such I should be a member of BUILTIN\Administrators - which is by default configured to be a member of the sysadmin server role in SQL Server. But I kept getting "Access den
... [more]
By: Talha Shah
Posted:
December 26, 2006 at 8:51 PM
“Extraction, Transformation, and Loading” also known as ETL is among one of the key steps in data warehouse life cycle. Generally source data structures in operational data store are not similar to target data structures. To solve the issue of incompatible data structures ETL acquires data from operational data stores and transform it into information that can be used by decision support systems. As ETL is such an important part of data warehouse lifecycle, it is important that ETL is comple
... [more]
By: Dave Scheele
Posted:
September 30, 2006 at 8:01 AM
A few months ago I was designing a MOSS 2007 infrastructure for a client who had originally intended to implement a SharePoint Portal Server 2003 solution, but based on their heavy reliance on forms and workflow discovered during Envisioning interviews, decided to take the plunge and use Microsoft Office SharePoint Server 2007 instead. The product was in the Beta 1 stage at the time and their system was scheduled for implementation sometime around the Beta 2 TR timeframe. One of the big deci
... [more]
By: Aaron Steele
Posted:
April 2, 2006 at 11:44 AM
One of my favorite SQL stored procedures has been hiding in the master database since SQL 7. It's a useful utility for running commands against every database on a given SQL Server named sp_MSForeachDB. It undocumented and may have been created for running updates during SQL upgrades. But we can put it to work for creating reports and other repetitive tasks.
Here's the syntax:EXEC sp_MSforeachdb @command1, @replacechar, @command2, @command3, @precommand, @postcommand
Parameter
Desc
... [more]
|
 |
|
|
|
|
 |
|
|
|
|
|
|
|