One of the most common reasons to use SQL Server Integration Services (SSIS) is to import data from or export data to text files. Maybe the data in these files is delimited by pipes (|) or commas. Maybe the files are in XML format. Regardless of the type of file, SSIS is a great tool to work with this data. When importing a file, the data is loaded into memory of the server where any of the available transforms are at your disposal for manipulating the data. One thing you could do is calculate a...
Read More
One of the new T-SQL features is a new way to page results. This allows you to skip a number of rows and view a given number of rows. For example, when you do a search on Amazon for SQL Server books, you will view the results a page at a time. The new paging functionality is an enhancement of the OVER clause using the new keywords OFFSET ROWS and FETCH NEXT or FETCH FIRST ROWS ONLY. Here is an example using the AdventureWorks2012 database: SELECT ProductID, Name FROM Production.Product ORDER BY ...
Hello all, Some Hot news of the SQL PASS SUMMIT 2012. PASS Summit is the world's largest, most-focused, and most-intensive conference for Microsoft SQL Server and BI professionals. Organized by and for SQL Server and BI users, PASS Summit delivers the most technical sessions, the largest number of attendees, the best networking, and the highest-rated sessions and speakers of any SQL Server event in the world. The 3 most awaited days from Nov 7-9 are here for all the SQL and BI lovers around the ...
In 2005 and 2008, SSIS required a lot of manual configurations regarding deployment, configurations, environments, etc. It took a lot of time just to set up the environment for proper performance. Microsoft came up with a term for this, and called it the “package deployment model.” After 2012 was released, all existing deployments were referred to as this, and 2012 introduced a new concept: the “Project deployment model”. The easiest way that I thought of to remember it w...
Recently, another colleague came to me again with an issue where BIDs would not open a report in design mode. Instead, the following block of text greeted the developer: <html><head><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=UTF-8"><title></title><style>.ErrorStyle { font-family: tahoma; font-size: 11 pt; text-align: left}.DetailsStyle { font-family: tahoma; font-size: 10pt; text-align: left;text-indent: 0; word-spacing: 0; line-height: 100%; ...
Once again, thanks to everyone that attended my session on PowerPivot. Here are my responses to some of the questions I received during my session but did not have time to answer until now. 1. Does the current version of PP support drill through? The current version of PowerPivot does support drill through. All you have to do is right-click a measure in your spreadsheet and select Show More Details or alternatively you can double-click the measure. 2. Is PowerPivot add-in required for SharePoint...
Thanks to everyone who attended my PowerPivot 101 webinar last Tuesday morning! It was a blast and I had a great time presenting for the 300 strong crowd that attended! In case you missed the webinar, you can easily view the recording here free of charge ! Also, don't forget to check out the dozens of other free webinar recordings . With this kind of free training available, there really is no excuse for you to not be learning something new! If you're looking for a great book to get you started ...
Thanks to all who attended my webinar last Thursday on Building Dynamic Cube Reports. You can find my code Recording: http://pragmaticworks.com/LearningCenter/FreeTrainingWebinars/WebinarDetails.aspx?ResourceId=470 Code: http://sdrv.ms/RqxbcF There are several reports in here that step you through the changes I made throughout the webinar. Also, look for my upcoming SSRS Master Classes that will take you Reporting Services skills to the next level!
H2K Infosys is based in Atalnta, GA providing Instructor led face2face Live online Software Training classes for everyone from any part of the world. Contact h2kinfosys@gmail.com / training@h2kinfosys.com www.H2KINFOSYS.com *Test lab is deployed in Cloud to practice from anywhere. *100% Job oriented training. *Check our students reviews posted in public access web sites. *Real Software Project scenarios are discussed. *Complete practical oriented “HANDS on TRAINING”. *Pay One Time Fe...
When you have a Database that has been orphaned by the original dbo, you might receive the following error in BI Documenter. Unable to complete version write of SQL Server database. Please contact support at support@pragmaticworks.com if this issue continues. Underlying reason: Property Owner is not available for Database '[ABC_DEF]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. To resolve this error you would need to change the owner ...
It is necessary for you to have the following access to document a report in BI Documenter *System User Roles-You are just an authorized user. *Browser Role to the Web Service Directory- This access is necessary to browse the reports. *However, you must need the Publisher Role to any report folder you intend on documenting inside of the Reporting Services Server. Why do you need Publisher role? In order to document the report in BI Documenter, the Publisher role is required to get the nuts and b...
This post will detail some of the challenges I encountered while setting up a three tier SharePoint 2010 farm that included PowerPivot. Hopefully these notes will help somebody else. Our three-tier farm will include: One server for the SQL Server 2008 R2 relational database that SharePoint will use. (SQL) One application server that will run the central administration web site, the web application web site which will house our site collections, and a PowerPivot installation which includes an Ana...
Here's What's In My BI Tool Belt 1. BI xPress - Auditing, Notification frameworks and much, much more! Number one for a reason... 2. Task Factory - Extra SSIS components. SCD Merge Transform, Upsert, SharePoint Source/Destination and more 3. BI Documenter - Takes the pain out of documentation. The easy button. 4. BIDS Helper (does some things BI xPress does not) 5. Parent Child Naturalizer (codeplex) 6. ResophNotes - code snippet organizer 7. Cube Player 8. Blitz! - SQL Server TakeOvers 9. PoorS...
You must have the same Management Studios and BIDS version of the SSIS packages you are documenting, on the same machine as BI Documenter. BI Dcoumenter will not document any IS packages if you do not have Management Studios or BIDS loaded on the machine you are using BI Dcoumenter. You will also need db_ssisoperator (in 2008/R2) or db_dtsoperator (in 2005) access to the msdb System Database for every SQL Server Instance that includes deployed SSIS Packages you intend on documenting.
I wouldn't normally blog about searching a chm file but it has been the topic of two support cases in the past couple of weeks. If you have a .chm file for which the search feature does not work or displays a message box stating "No Topics Found", then require an additional file for the feature to work properly. The following are steps to get the feature working: 1.) Download the file needed http://www.pragmaticworks.com/downloads/itcc.zip 2.) Unzip this file to the BI Documenter directory (you ...
The last couple of years in the US have been marked by many discussions of numbers, to be more specific, percentages. It started with the 99% protests and, most recently, has included the unfortunate 47% comment by one of our presidential candidates. But, when I think about it, I remember being aware in grade school of having a certain percentile value assigned to each of my subject areas. I was really proud of hitting above 90% in math and reading skills for my grade. I also remember seeing per...
In SQL Server 2012 Analysis Services, Microsoft introduced the xVelocity engine and the tabular model for databases into Analysis Services. This gives much more compression and performance, but not all of the features of multi-dimensional databases are available. Although a major current drawback, I would expect that Microsoft will be working to add these into tabular and DAX in the future. Tabular and DAX is much simpler to develop than multidimensional databases. In a multidimensional database...
Multiple value parameters are a common tools used in SSRS. You can use this tool to select which columns show on a report. You will need to create a multiple value parameter and place an expression on each column on the report. More specifically the expression needs to be on the column visibility property of the columns. Here is an example report with five columns. Here are the parameter available values with the column choices. I hard coded these choices into the available value fields; you cou...
Here are the slides and code for the October 23 Training on the Ts session.
Two of the most exciting developments in the T-SQL world are the enhancements to the OVER clause and the new analytic functions. These particular enhancements will enable us to write code to solve complex problems in a very efficient way. As they say in sales: This is a game changer. There are eight analytic functions: CUME_DIST, FIRST_VALUE, LAG, LAST_VALUE, LEAD, PERCENTILE_CONT, PERCENTILE_DISC, and PERCENT_RANK. In this post, I’m going to explain how to use the LAG function. The LAG fu...
I was recently helping a client set up their PowerPivot workbook for the first time and when they brought the months over on the pivot table, the results of the month weren’t sorted. So for example, April and August were the first months the list. Typically, users want to see months in sequential order (January, February, etc.). As DAX doesn’t have a key order by field, it’s a little bit different than in SSAS, where you can just order by the different key field. The solution i...
Join me next week for a really fun presentation on building Dynamic SSRS reports using Analysis Services cubes as a data source. This webinar will demonstrate how you can use one of the underutilized features of Analysis Services called Dynamic Management Views. Session details: Designing Dynamic SSRS Reports using an Analysis Services Data Source Instructor: Devin Knight Date/Time: 10/25/2012 11:00 AM Creating Reporting Services reports that use Analysis Services as a data source can frustratin...
SQL Server New Version Denali - New Feature Sequence One of the new featured in SQL Server 2011 – Denali is Sequence. Whoever have worked in Oracle or DB2 can easily connect to this feature and it is in the SQL Server wishlist for long time. While migrating Oracle database to SQL Server this was one area quite bit of effort needed because in previoius versions of SQL Server Sequence was not supported. Though there were workaround to achieve the same feature, there was no One to One mapping...
Pragmatic Works just published a video on their YouTube channel put together by yours truly on implementing security in your SQL Server Analysis Services cube . The video covers implementing basic dimensional security, cell security, as well as an extended look at implementing dynamic data driven security. The video is about an hour long so grab a bag of popcorn, sit back, and hopefully learn how to make your cube as secure as if it were guarded by a squad of Segway riding special forces command...
If you missed any of the great 24 hour of PASS sessions from September you can now watch the recordings. http://www.sqlpass.org/UserLogin.aspx?returnurl=%2fLearningCenter%2fSessionRecordings%2f24HoursFall2012.aspx Details on my session can be found below: Session 01 - Choosing the Right Reporting Platform Presenters: Brian Knight , Devin Knight Download presentation slides (PDF)