Dynamic Management Views are a very powerful tool that are part of Analysis Services in 2008 and 2008 R2. They give you the ability to monitor Analysis Services usage and metadata. MSDN articles on SSAS DMVs – http://msdn.microsoft.com/en-us/library/ms126079.aspx Good post on using SSAS DMVs – http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/ A report developer could leverage the capability to read Analysis Services metadata to build a report that can dynamicall...
Read More
Did you know that BIxPress has 2 features that ease package deployment? I covered the first option a couple of weeks ago and that was the BIxPress Quick Deploy feature. Visit my other blog if you want to learn more about that feature. Today’s blog is going to focus on the other BIxPress option which uses the SSIS Deployment Wizard. Now this feature has a few more steps than the Quick Deploy option however you have a few more features you can take advantage of as well. Let’s take a lo...
Don’t you hate it when you’re troubleshooting a package and you have to hunt around for a configuration or an expression? Well BIxPress has solved this issue and this is by far one of my favorite features of BIxPress. Let me tell you how much of a timesaver this is and how easy it is to just glance at your package and be able to identify which items are dynamic. Once you have BIxPress installed it automatically highlights all expressions/configurations with either a red or blue icon ...
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. Sometimes we might need to delete the particular row from a table in SQL Database. While working someone might mistakenly insert the duplicate same data multiple times within a table that might contains a huge amount of data and after populating that table .He/she wants to delete that duplicate data inserted from a table. So, for this we might drop and recreate the table...
Do you need to develop your SSIS packages faster? If the answer to that question is yes then here’s another reason to use BIxPress. BIxPress has a lot of features that can help speed up development that we have covered over the last couple of weeks. Today we will take a look at one of the most useful in my opinion and that’s the Package Builder and its available templates. The templates are time savers because you will not have to re-create the wheel each time you have packages that ...
Recently I was conducting an unscheduled demonstration of PowerPivot and had one of those uh-oh moments. After I extolling the virtues of this cool tool, I launched Excel and clicked the PowerPivot Window button. I could almost hear the sad trombones playing "Wah Wah Waaaahhh...." as a command window popped up with a message similar to "Unable to open file ... Files\Microsoft Analysis Services\AS OLEDB\10\FlightRecorderCurrent.trc". Ah, the joys of unscheduled/unrehearsed demonstrations! "Not a ...
Do you know what Notification Framework is? The primary objective for notification framework is to alert you about certain events in your SSIS package. This notification framework will alert you through email and/or SMS if your package fails along with details to help you troubleshoot the issue. As with all the other features included with BIxPress this is a very simple process and something most developers shouldn’t live without. Let’s explore this feature and I will show you how to...
In a previous post I wrote about how you can use embedded custom code to extend the capabilities of Reporting Services. This week I will show you another method of using custom code but this time with external assemblies. Ideally when using custom code you would choose to do so using external assemblies. External assemblies help developers manage code from outside Reporting Services and share the exact same code across multiple report. Here’s a few of the pros and cons of using external as...
Whether you consider these to be the same or list that they are technically different there is still a valid discussion of why you should use one verses the other. It?s a trick question! The precision and scale will produce the same results no matter what the data entered. I entered data with the same precision and scale but changed the data on each row. Take a look at the insert statement. I swamped the insert statement and played with all sorts of numbers. Even if I ...
If I had a nickel for every time I've heard from a business user that the OLAP solution must support current real time data from the source system, I'd have at least half a dollar. The fact is that in reality there is usually some acceptable degree of latency. Even though Microsoft SQL Server Analysis Services has a ROLAP storage mode option, I've typically tried to steer people towards the MOLAP option due to it's query performance superiority. This past week I've had the opportunity to explore...
Today, one of my team members faced an issue that I was not able to give the answer right away. The problem was, in SSMS while he was connected to DEV JOBs, he couldnt edit steps, as image below: First we started for privileges/permissions issue. But that was not the problem. After checking his SS version we got the problem: select @@version Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64) Apr 22 2011 19:23:43 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6...
Today we’re going to take a look at the real time package monitoring feature of BIxPress. The real time monitoring feature of BIxPress will allow you to monitor current or past packages that have been executed on your company’s server. As you can see from the below images it looks just like it does in bids when your developing your SSIS packages. From this monitoring console you can watch a single or multiple packages as they run on the server. This feature can be very useful when tr...
Don’t you hate it when you create that variable in your SSIS package under the wrong scope? Well fret no more because as you have already guessed it BIxPress can handle this. This is just 1 more feature where BixPress can save you time when developing your packages. Below I will show you how you can easily change the scope of your variable without having to delete it. The first thing you need to do is select the first of the 4 different BIxPress variable options from the tool bar. This wil...
« 1 | 2 |3 » This is the 3rd and final post about BULK INSERT. In this one I want to show you how to bulk insert a file into a table with an identity column. Here is the script to create table: CREATE TABLE [dbo].[Users] ( [UserID] int IDENTITY(1,1), [FirstName] [varchar](10) NULL, [LastName] [varchar](10) NULL, [Country] [varchar](20) NULL ) ON [PRIMARY] I want to insert a csv file without mapping UserID, since it is an identity column. File format is shown below: When trying to run...
« 1 |2| 3 » In my previous post I've shown how to bulk insert a csv file through T-SQL. In that example I've used the same structure to both csv file and destination table. In this post I'll show you how to bulk insert a file wich struture differs from destination table. For this example, I'll take the same destination table: CREATE TABLE [dbo].[Users] ( [UserID] int NULL, [FirstName] [varchar](10) NULL, [LastName] [varchar](10) NULL, [Country] [varchar](20) NULL ) ON [PRIMARY] From ...
« 1| 2 | 3 » Recently, there was a post where the user found himself in a problem when loading data utilizing BULK INSERT This post is the first from a series, wich I want to explain how to use BULK INSERT in multiple possible ways. BULK INSERT - as mentioned in BOL , BULK INSERT "Imports a data file into a database table or view in a user-specified format in SQL Server 2008 R2. Use this statement to efficiently transfer data between SQL Server and heterogeneous data sources." Notice...
Late last year one of my colleagues Mark Crosby has introduced the Prezi tool in our company to do presentations. Prezi is a presentation software that can be used in creating more visual presentations. It is different to the traditional powerpoint software as it has a Zooming user interface and uses a vector based illustration and text which creates big visual impact. The main differences between Prezi and Powerpoint that I have found are as follows: The first difference I found is that Prezi i...
One of the new and exciting features of SSIS in SQL Server 2012 is the SSIS Project and project deployment model that is now available. The new project deployment model includes a new option for deployment as well as a bunch of new features such as shared connections, properties, environments and versioning. This post will introduce this new model and highlighting some of the various features and functions that are available. Create the Integration Services Catalog Before we can get started the ...
This is Part 3 of 3 in a series of blog entries that reviews the 4000 character expression limitation in SQL Server Integration Services packages. As I mentioned in the previous entries, the limitation exists in SQL Server 2008R2 and earlier versions. As I will demonstrate here, this limitation has been removed in SQL Server 2012. As someone who loves Integration Services, I cannot begin to explain how relieved I was to hear that this was coming. Although 4000 characters is typically more than e...
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...
This is my wish list of things I’d like to cover in todays session at 11 AM EST https://www1.gotomeeting.com/register/935071481 but seeing that I know I’ll only get to a third of these thought I would post these for everyone. If you’re seeing this late the sessions are recorded and can be viewed on www.pragmaticworks.com/Resources/webinars . Next week look for a while paper I’ll be releasing on the same topic but of course with more details and screenshots. This post is p...
Hello All, This might not be the proper for the blog post but it helps me to understand more regarding the transfer of tables within different file groups in MS-SQL. So, I think it will be more helpful to the others who indeed needs such information related to file groups . I had posted as below within MSDN http://social.msdn.microsoft.com/Forums/en/transactsql/thread/d35ab8ff-a6a1-458c-b3e4-b2c9f6a1ff83 I have few issues regarding the transfer of the tables from one file group to another file g...
I'd like to thank the Foundation for helping me become more knowledgeable about the IT industry and providing continuous training with all of the various resources you've provided to us such as BIDN and the free webinars on the PW website. I'm grateful for these valuable resources coming from industry experts and I study them daily to learn and improve my SQL BI Stack skills. Thanks PWF!! -T
Good evening BIDN, Tonight I will show you how BIxPress can scan your SSIS packages for any unused variables. This feature isn’t only limited to finding and removing unused variables but it will also inform you on how and where each variable is used. As you can see from the image below I created a simple package with a couple precedence constraints and a flat file connection. Each of these is driven from variables as shown on the variables tab. In order to see which variables are being use...
Today we will be taking a look at how to implement a robust auditing framework to your SSIS packages. Now this task might sound a bit daunting but rest assured that with BIxPress this can be achieved in just a matter of minutes. Below I will give you step by step instructions on how to do this. The first step is to right click on your package in the solution explorer and select ‘Add/Remove Auditing Framework (BIxPress).’ If you don’t have BIxPress installed you will not see thi...