Restoring a SharePoint site collection can be a pretty easy task. If your interested in how to do that check this blog ( Backup and Restore SharePoint 2010 Site Collection With PowerShell ) where I explain the process. Unfortunately this is not always a foolproof process as I have recently found out. Most errors are pretty self explanatory such as the site collection already exists, in which case change use the -Force command to overwrite it. I recently came across the following error: Restore-S...
Read More
Like Neo from the Matrix, Link from the Legend of Zelda and Hot Rod, who eventually became Rodimus Prime (for you old schoolers) my journey has begun. It is both exciting and challenging to cut my way through the BI forest and conquer the many obstacles that lie before me. I once read that, “Unless a man has trained himself for his chance, the chance will only make him look ridiculous. A great occasion is worth to a man exactly what his preparation enables him to make of it. Preparation is...
I hope everyone had a great start to the new year. Last year, I learned a lot and hope to continue learning all throughout 2012. I normally don?t bother with New Year?s Resolutions, but this time around I will make an exception. This year, I plan to hit SSAS hard and do as much in that area as I can. I never have known too much about AS, so this is long over due. Look forward to more webinars as well. I enjoy delivering them and definitely want to continue that trend. So here?s to 2012 being the...
In this installment of Fun with MDX we are going to put together a solution that will allow us to view the contribution ratio of internet sales for each level in the Adventure Works product hierarchy. This solution will look at two contexts for this ratio. The first ratio we will build is one that ignores the product hierarchy and shows the contribution ratio at the catalog or global level (i.e. Product vs All Products). The second ratio will highlight the product?s contribution within its paren...
So I have been spending a considerable amount of time the past couple of weeks getting up to speed on MDX. As I come across interesting solutions I want to take a minute to present them and explain how and why they work in the hopes that they will help you or someone down the road. For those that want to play along all of these samples can run against the Adventure Works 2008R2 cube. In this first installment I want to find the top 5 products in each country ranked by reseller sales. At first gl...
Hello all, Firstly ,Happy New Year 2012 :) to all our BIDN’s Family and others too.!!! In order to start this year blogging, I would like to post another blog which might be helpful to the one who works in SSAS. Last time, I was just trying for some trick or some queries in order to quick find the distinct count of all the Hierarchy and attribute members from all the dimensions . For Example, If we have ‘ PaidDate ‘ dim having Hierarchy members as PaidYear ,PaidMonth, PaidDay t...
In my last post we walked through creating a template from an existing package using BIxPress . Templates allow developers to build packages quickly by having a number of components preselected and configured, and making it easy to update the components as needed. In this post we?ll walk through using the template we designed in the last post. Start off by right clicking on the SSIS packages folder in BIDS and selecting BIxPress Package Builder Wizard. From the new window select the ...
The current project I am involved with has required me to develop packages as quickly as possible while also adhering design standards. The packages had a number of things in common with each other, including data sources, variables and components used to load the packages. This is the perfect scenario for creating a template to speed up development of SSIS packages. To create a template you can right click on the SSIS packages folder and select the BIxPress Package Builder Wizard &#...
The last entry in this introductory series to BI Documenter will cover how you can automate the documentation process. The automation works by taking the existing solutions that have been created and in conjunction with Windows Scheduled Tasks sets up the documentation to be run on a schedule that you define. So lets get to it. First select the solution you want to schedule. Next select the Schedule snapshot link from the tools menu on the right hand side of the screen. This will launch a ...
Moving databases from one machine to another or restoring one on a current machine can be a routine process. While moving a database to a new machine I attempted to restored the .bak file and got an error: Filestream feature Disabled. In searching the internet I found this blog http://ppetrov.wordpress.com/2008/08/15/sql-server-2008-enable-filestream/ and followed the directions. When I went to restore the database I got the same error. You may want to try the steps from that blog as it may work...
I’m in the middle of a database migration and thought I’d quickly share a script I threw together to show estimated time of completion for a database restore in progress on SQL Server. The script will also show you estimated time for database backups to complete as well. Please don't take this script as gospel, the best way to truly know how long restores will take is to actually perform a restore! Remember folks: Backups are worthless, restores are pricess SELECT session_id, start_t...
Yesterday I was playing with Excel pivot table slicers. In case you are not familiar with them, they provide an alternative to filtering data in pivot tables to the standard filter drop downs that we all know and love. I have included a screen shot below with the slicer on the left. The SHIFT key permits you to select multiple adjacent slicer items and the CTRL key permits selecting multiple slicers (adjacent or non-adjacent). Pretty standard Windows functionality! Yesterday, I was pulling my ha...
Logging in SSIS is a native way to perform some basic auditing in your SSIS packages. It will give you some insight as to what went wrong in the event that a package fails. In this blog post, I will show you how to enable logging in BIDS during package development, and how to use the log provider for SQL Server. First of all, in BIDS, right click anywhere in the control flow. In the context menu, you will click the first option, Logging. This will bring up your logging configuratio...
There are 7 key principals in this book that demonstrate in a practical way how to be an effective manager. Here is the list: 1. Surround yourself with high-quality employees 2. Train employees well 3. Communicate the end result you want, them empower employees to achieve it 4. Lead by example 5. Listen to employees 6. Praise good work 7. Manage each employee differently As you read through this list these a face value seem to be common business principles and management techniques ...
Second Day learning MDX and I have discovered that writing simple properly sorted queries in Analysis Services is a true art For example, in using Adventure Works 2008R2 database, I am trying to bring Sales amount results from all Products Subcategories and Calendar Years in a descending order. My query wasn’t bringing a true descending of the Sales Amount until I have added a break (‘bdesc ‘ instead of just ‘desc’) in the sorting. The results brought the content of...
Sorry for the long layoff since my last post in this series but the holiday season and client commitments demanded a lot of me. I am back now and ready to continue to share with you all. In some of the more complex SSIS packages it can be a challenge to identify what objects in your environment will either impact your SSIS package of be impacted by your SSIS package. This is doubly true if you didn?t design the package your self. BI Documenter has a feature called Impact Analys...
Recently I needed to create a Named Calculation in SQL Server Analysis Services (SSAS) called Full Name. To accomplish this I combined First Name and Last Name columns, and added the space between first and last using Single Quotes as in FirstName+’ ‘+LastName This got me thinking “When do you use single quotes and when do you use double quotes to represent literal strings in SSIS, SSRS, and SSAS?” My first introduction to the Microsoft BI Stack was to T-SQL which uses si...
SSIS provides functionality to handle unexpected data in the data flow. Data flow errors are often due to invalid data values (e.g. string instead of number) and data truncation. Many components in the data flow support error outputs that allow you to control how truncation and errors are handled. The Error Outputs contain an ErrorCode and ErrorColumn in addition to the data columns in the row that caused the error. Error outputs can be connected to another component in the data flow (e.g. a fla...
As I've shown in my previous posts, there is a bunch of new functions and features with SQL Server 2012. In this thread, I would like to show you how to use the new logical function which select and return data from a specific index of a list of supplied values. The index list always start by 1. This new function is the Choose function. This function has some similarities with program languages such as switch function in VB.net or C#, and the function Choose itself in VS2008 and SSRS. Below the ...
In the SSIS data flow, many data flow components provide an ErrorOutput that allows you to route rows that generate errors or truncation to another component in the data flow. The ErrorOutput path contains the following metadata: ErrorCode, ErrorColumn and Flat File Source Error Output Column. Flat File Source Error Output Column – The data row that generated the error. ErrorCode – Code associated with the error that occurred. ErrorColumn – Numeric ID of the column that caused ...
I just wanted to thank you for all your wonderful posts, for today I was able to continue working on my assignments for SSRS and Service Analysis because of your wonderful "how-to" blogs. The "savior" blog for me today was: Help! I Can’t Connect to Analysis Services in SSMS from Brian McDonald Please continue with the wonderful work!! Sardys
Haiku #4 Done with my first week! PragmaticWorks is Awesome! Can't wait for next week. So my first week was an abbreviated week. I dove into some SSIS vids. I got to an inventory of office stuff. And I drank, like 2 gallons of Coca-Cola. The only thing I wish would have happened was that I couldn't get into any of the practice databases, thus I couldn't do any homework. I feel a little bit behind now. :( Gradually, I'm feeling a little less overwhelmed (if I had a dollar for everytime I said "ov...
Hooray! Ding Dong the witch is dead! Many SSIS developers have had to content with the expression length limitation of 4000 characters. This limitation has been removed in SQL Server 2012. For ETL developers SQL 2008R2 (and earlier) this limitation will still need work arounds. This limitation typically rears its ugly head when dynamic SQL is required. It should be noted that the limitation applied to expressions and not the actual variables. I will be posting an article next week that provides ...
With this topic, I want to share Visio 2010 and sharepoint 2010 new features. But mainly, I would like to hear your feedback, ideas, experience and if possible, some examples where you find this would be interesting, but must of all if you use this in your professional activity, why, and benefits it brought to you and end users. Data Graphic was first introduced in Visio 2007. In Visio 2010, among new features in ribbon, it is now possible to create sub-process and workflows. Sharepoint 2010 int...
If you are like me then you like to reinstall Windows on your laptop every few months to keep things fresh and running smoothly. That or your even more like me and you download some beta version of software and install it on your laptop rather than a VM and screw something up to an unrepairable state. Either way Windows is being reinstalled. I have a laptop without an optical drive in it. The vast majority of the time I don’t mind as I pretty much use ISO files exclusively. This is a probl...