I found two simple ways to dump MDX query output to a table and then dump the table data to excel file or text file as per need. The two methods are: 1. Using OPENROWSET Syntax : OPENROWSET('provider_name', 'datasource'; 'user_id'; 'password', {[ catalog. ] [ schema. ] object | 'query' }) Connect to the relational server using windows authentication(important!). Example: SELECT * INTO zzz_temp_table FROM OPENROWSET('MSOLAP','DATASOURCE=OLAPServername;Initial Catalog=Cube1_DEV;', 'MDX Query here'...
Read More
Many of you may now or in the future have the need to upgrade your SSIS packages to the new SQL Server 2012 SSIS. Luckily, upgrading from SSIS 2005 or 2008 isn’t nearly as painful as you may have experienced upgrading old DTS (SQL Server 2000) packages. If you’re considering the upgrade to 2012 here are some of the new features you have to look forward to: Usability fixes in the development environment (Resolve References, Undo, Zoom, etc…) New/Changed Tasks and Transforms (CD...
I use sharepoint 2010 to display the data analysis tool I have created in excel 2010 to manage the Microsoft licenses as well as to utilise the excel 2010 capabilities of slicers and dicers. This has been going pretty smoothly until yesterday when I stumbled with this error below. "Workbook is larger than the maximum workbook size" So I had to go through the Excel web services properties on the sharepoint server and increase the maximum file size. Here are the steps that I followed. Logged into ...
This past Thursday and Friday I attended my first PASS SQL Rally event in Dallas, Texas. The week was full of some pretty amazing sessions presented by some even more amazing speakers. If you were a DBA looking for performance tuning tips or other ways to perfect your profession, there were tons of great sessions for you. If you were a developer looking some great development tips and tricks to make your life easier, there were some really informative sessions given. And if you were a business i...
I often use Visio diagams for architecture, and not having a shape for a SSAS server was always annoying. I'd built one out previously, but never took the time to polish it (which mostly meant make the text editable....) Now that I needed one for Tabular mode servers as well, I took the 20 minutes to fix the editable text. Thought this might be useful. You can download the Visio Stencil here http://www.bidn.com/Assets/Uploaded-CMS-Files/OLAP_Servers-4b288bf1-3b87-4758-be85-140693b0034d.vss If y'...
With Sharepoint rapidly becoming the data store of choice for user driven and user built data capture systems, getting this data into your warehouse is becoming more and more important. There are three primary methods of doing this from SSIS. 1. Install 3rd party Sharepoint List Adapters. CodePlex even has a free version at http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652 2. Write your own adapter. Again, the CodePlex project is a great start 3. Create a script component to query Sha...
Another SQL Rally has wrapped up in Dallas, TX and it was great event. My favorite part about these larger events is being able to meet new people and see old friends that I haven’t seen for months. Unfortunately, I did not attend any of the precons this year but I did see a lot of great sessions. I made it a point to at this event to reach outside my comfort zone with the types of sessions I attended. Here’s a wrap up of my couple days in Dallas: Thursday I started my morning by att...
Aggregating over Lookups in SSRS SSRS 2008 R2 has a feature to do a lookup to another dataset, but no way to trivially aggregate the value. It’s a fairly simple solution: Use a small piece of code to do the summing. Now, you may have various LookupSets, so you don’t want to code a function for each, so we store the values in a dictionary lookup. Then when you query at the sum level, you just query the dictionary Dim dValueList As New System.Collections.Generic.Dictionary(Of String, D...
The problem: An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The statement has been terminated.".An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: “VIOLATION OF UNIQUE KEY CONSTRAINT ‘ Your Column’ . Cannot insert duplicate key in object ‘Your Table ’. The duplicate key value is ( the duplicated value ).” My solution: When loading ...
Thanks to everyone who attended my session at SQL Saturday #130 in Jacksonville, FL a couple weeks back. I apologize for posting this so late, but better late than never. To download my session materials, just click this link . In it you'll find my PowerPoint slide deck and the MDX script I used in the class. If you have any questions about what we went over or any questions regarding the materials, feel free to leave me a comment or shoot me an email. Thanks again to all those who attended... e...
In SSIS 2012 there is a great new feature called environments. They can be thought of as a collection of parameters or variables. One of the questions I get asked when teaching SSIS 2012 is ?Can I duplicate an environment??. There is a move feature already included. But this moves the environment instead of copying. Now you can write T-SQL Scripts like the one at the bottom of this blog to create an environment and create the variables in the environment, but this is time consuming. You can scri...
Today I was working in SSAS Tabular Mode for SQL Server 2012. What my goal was, was to remove version numbers from Products. This was so that instead of having the following below in a report: Microsoft .Net Framework 1.x Microsoft .Net Framework 2.x Microsoft .Net Framework 3.x Microsoft .Net Framework 4.x I would have one name instead: Microsoft .Net Framework I was using the DAX Left Function , and nested inside the Left Function I was getting the count of Characters to find out where the ".x...
One of my favorite expressions is a time string expression. I use it all the time when renaming files. In a recent MSDN question someone asked how to create a time string that used plain time, not military time (24 hour clock). I thought that would be easy and was going to tell the writer to find it for him/herself. But in trying to put it together, I found it rather challenging: (DT_WSTR,24)((DT_WSTR,8)((10000 * YEAR( GETDATE())) + (100 * MONTH( GETDATE())) + DAY( GETDATE())) + "_" + RIGHT(REPL...
Last week I looked at how you can configure the Expression property of an SSIS variable ( http://www.bidn.com/blogs/RussLoski/ssas/2688/configuring-variable-expression ). I could build a variable with an expression and then configure the formula that I use for that expression in an XML configuration file or even an SQL configuration. Why stop at this? Why not see what else can be configured? In this blog I examine how to put the expression used in a derived column into the configuration. Here is...
So far in my quest to become involved in SQL Server BI, I have been looking at my options in the field and becoming a BI architect seems quite appealing to me. Of course, being a relative newbie to SQL, I know I would have a lot of work to do to attain this goal but I'm not deterred at all. I believe that the best way to learn anything is knowing what you want and then creating a plan and goals around it, which is why I'm now looking into MS certification in BI. Yes, as a IT Pro I'm very aware o...
I've joked in the past that the data miner is to the BI analyst what the weather person is to the news reporter. One reports on the past, and the other predicts the FUTURE! Pretty cool when you look at it that way. So let?s do something meaningful today that can help our entire nation. Let's forecast the unemployment rate using the Microsoft SQL Server 2012 Data Mining Add-ins for Office 2010! I started by downloading and installing the FRED Excel add-in which you can find here . I pulled the un...
In the fast paced world of business intelligence, there are many tools available to the user manage one’s data and make the moves necessary to stay ahead of his or her competitors. It’s safe to say that these tools did not materialize out of the ether, and they certainly were not created by some cyborg master program. They were all created by people with years of programming experience. However, a part of the development process that doesn’t often receive enough praise is the Q...
Edit: This is not new content. I've reposted this with corrected image links. In my previous blog post, I showed a row-by-row solution to creating an excel workbook dynamically using SSIS and populating one worksheet per row of data. Then "studio72" asked if this method could be adapted if the test data "users" had more than one row of data in the resultset as follows: Name | Age | Job John 25 IT John 34 ENG Anna 21 HR Anna 24 IT Mary 20 MFG I want the SSIS to create one excel file with sheets f...
I am in reporting team and once had this requirement. We had one table where Seconds were stored as INT and in report we wanted to convert the seconds to HH:MM:SS format. We already had 1 solution ready with us. It was something like: --------------------------------------------------------------------------------------------------------------- DECLARE @Seconds INT SET @Seconds = 3800 SELECT CONVERT ( VARCHAR (10),@Seconds/3600) + ':' + RIGHT( '00' + CONVERT ( VARCHAR (2),(@Seconds%3600)/60),2) ...
I enjoy the most, talking about SQL and sharing whatever little knowledge I have. Many SQL Developers have this misconception: “Primary key => Clustered Index: Only a Clustered Index can exist on a Primary key column”. On numerous occasions I had tough times explaining that this is not the case every time, you can create a Non-Clustered Index on a primary key column. But if this hot discussion is going on across a coffee table and I’m away from Computer I get helpless. So fi...
Everybody has different views about VIEWS, that’s what make them interesting topic to discuss. Other thing is if you make some assumptions about VIEWS, they can lead you to problems. As my target audience is SQL beginners, today I’ll talk about few things about VIEWS so some of the obvious mistakes can be avoided. Actually there are so many things we can talk about VIEWS, but I leave them for future posts. Today I’ll show what happens when you create a view using “SELECT ...
The time intelligence filters provided with PerformancePoint 2010 gives developers an easy way to provide users with a method for specifying time periods using common English terms such as "Last 6 months", "Same Period Last Year", "Rolling 3 months", and so on. These filters can be linked inside your dashboard to control Excel Services reports, SSRS reports, scorecards, and analytic girds and charts. Behind the filters are formulas based on the Simple Time Period Specification (STPS.) In the tex...
Today I had the opportunity to revisit a problem I encountered previously; but, today was different. Today I found the solution. Adding Time Intelligence to your SSAS cube using the Business Intelligence Wizard is an easy way to add a whole bunch of useful information to your solution. It is one of my favorite topics when teaching boot camps or quick starts because it adds so much functionality to your Analysis Services solution without a lot of work. One day, I decided to create an SSRS report ...
Recently I had the pleasure of pulling in a flat file encoded in UTF-8 format into a SQL Server 2008 table using SSIS. I'd like to share my experience in the hopes that it will help someone else with a similar issue. It might also help me in the event I come across the situation again and forget how to get past it. The first indication of a problem was the following error message: The data conversion for column "Column 37" returned status value 4 and status text "Text was truncated or one or mor...
What is the Difference between ROW_NUMBER , RANK and DENSE_RANK ? Which one to use? This is very common question in the minds of SQL newbie's. Lets take 1 simple example to understand the difference between 3. First lets create some sample data : -- create table CREATE TABLE RankingExample ( Names VARCHAR(1), SalarY INT ) GO -- insert data INSERT INTO RankingExample SELECT 'A',5000 UNION ALL SELECT 'B',5000 UNION ALL SELECT 'C',3000 UNION ALL SELECT 'D',4000 UNION ALL SELECT 'E',6000 UNION ALL S...