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 ...
Read More
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 ...
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...
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 ...
In the last September 28th I've passed my 1st microsoft certification. In fact, I've passed 2 microsoft certifications!! Yes, I was able to do both certification exams in the same day. It was my very first experience in certification exams. I've got 1000/1000 pts in 70-452 and more than 900 pts in 70-448 (I think I missed 1 question). I believe 70-452 is easier, but this is my opinion. My study/training was based in Testking and I had 3 weeks to be prepared. Not that I wanted, but because my com...
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 ...
Yes! For the second time, we host SQL Saturday. This event will be held Mar 17 2012 at Universidade Autónoma de Lisboa , Rua de Santa Marta, nº 56, Lisbon , 1169-023, Portugal . Here is the link . I wonder if I will get the change to meet any of you?
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...
BIxPress just released a new version. There are many great features apart of this release. My favorite feature in the new release is in the monitoring console. Now when you stop your packages the color turns blue and the row says stopped. Before the package would run because there was not a command being passed to BIxPress. Now that is not the case. This feature is a really big bonus to a great software product. It is also a great testimony of the value of a Pragmatic ...
Hello all, After such a long time , I would like to post some another blog which might be helpful to the one who works in SQL. I need to find the one who has changed the tables within my SQL Database that I have created. For certain reason ,we may need to find /to know who has done any changes within SQL. Such as add view, delete table, modify table, create database, add column, stopped SQL agent job, create new job, modify rules, add roles, new login, and etc. In order to trace this information...
Display folders are an often used tool in Analysis Services to help end users navigate through a cube or dimension they could find what they need to build a report. Imagine you develop a cube that has dozens of measures. For an end user to find what they need they would be forced to dig through every measure. If an end user gets lost while trying browsing the cube they are a lot less likely to continue using it. This is where display folders come in handy. They make it easy for end users to find...
When creating long reports, I would like them to have a neater and more structure style, therefore, I usually use the “advanced” mode under “Column Groups” to keep the column headers together within the entire report. This feature makes the report easier to read. I would also advise that when adding sub-reports to your main report to always verify that these have set up the"static" values before bringing the sub-reports to your main report. Even if you have set up those v...
There are many organizations that piece meal their solutions together for many reasons. Some of the reasons might have valid reasons, however, none this include the power of the Microsoft BI stack. Microsoft has a complete BI solution under one roof. I allows effective and efficient BI solutions at a reasonable cost. There is no greater way to increase ROI then to use the most cost effective system. The cost of Oracle, Informatica, IMB Cognos, and other BI solutions only provide yo...