MERGE Statement MATCHED clause headache relief

2011-12-08T13:52:00.006-05:00

Have you ever been using a MERGE statement and when you get the MATCHED clause you think to yourself, “This is easy, I’ll just script out an UPDATE statement and do a quick replace”? Only when you get around to it doing the quick replace it isn’t so quick. A friend of mine(SB) once said “I work hard at working smart”. It is a great motto to work by especially when working in a technical

Database Mirroring monitor for ETL

2010-10-21T23:31:00.001-04:00

BY Jim Bennett Do you have databases that are mirrored that your ETL is populating? Does the database mirror ever start to lag? If you answered yes to either of these questions then this is for you. SQL Server has a handy table that is created when database mirroring is configured. This table (msdb.dbo.dbm_monitor_data) allows you to see the status of your mirrored databases. So how can

SSIS Package Configurations

2010-10-04T11:58:00.002-04:00

By Jim BennettRecently I was asked to verify whether a directory full of SSIS packages all had their package configurations enabled. So instead of opening each one manually I decided to write a Powershell script to do it for me. In each SSIS package file there is an XML node that is designated for the package configurations. The XML looks like this for disabled package configurations:

Predictive Analytics in the Cloud

2010-09-21T13:17:00.001-04:00

By Irit Eizips The ability to do predictive analytics and data mining without being a statistician is here! Predixion Insight is a very cool public and private (on-premise) cloud service that is accessed via Predixion’s client software, which is installed into Excel (as an add-in) and lets you build sophisticated models without really needing to know all the technical stuff that would typically

Getting Started with SQL Server Data Mining for Retail/Finance

2010-09-20T10:59:00.001-04:00

By Rick Durham There are two informative, technical webcasts found at this link that cover how SSAS Data Mining is used to solve general retail/marketing problems… “Overview of SQL Server Data Mining” “Applying SQL Server 2005 Data Mining to Enterprise Business Problem” Here is the URL: http://www.sqlserverdatamining.com/ssdm/Home/Webcasts/tabid/62/Default.aspx Types of finance/

Standardize Your MDX Parameter Queries

2010-09-09T17:16:00.001-04:00

By Dan Meyers One thing that I have found useful when writing a lot of Reporting Services reports that have parameters and use MDX is to create some standard calculated members in my cube for the Label and Value properties of the parameters. Just like any other calculated measures that get built into the cube script, you get the advantage of reusing them instead of doing them over and

Interpreting Causation in Time Series Forecast models

2010-09-02T11:54:00.001-04:00

By Rick Durham · Building data mining models is one thing · Determining the primary factors that contribute to the final predicted data point is quite another One of the most useful algorithms in the SSAS suite of data mining tools is the Time Series algorithm. Its forecast method is really a combination of two other algorithms (ARIMA, ARTxp) and it takes the historic data values in a

MDX Tips from BI Conference

2010-08-11T15:31:00.001-04:00

By Dan Meyers Below is a link to a video and the accompanying slide deck for an MDX session that I sat in on at the Microsoft BI Conference in New Orleans. http://www.msteched.com/2010/NorthAmerica/BIE11-INT He presents a different way to do your time period calculations using a Utility dimension in SSAS. I think it’s interesting how he has created a hierarchy within the dimension.

Microsoft Based Planning and Budgeting solutions revisited

2010-07-26T11:31:00.001-04:00

By Irit Eizips In January 2009 Microsoft made the announcement that bed farewell to its PerformancePoint Planning initiatives and rolled its Business Intelligence (BI) solution into SharePoint. The common question asked is – Did Microsoft completely abandon the Corporate Performance Management concept? For those of you who are still unsure what this concept represents and how it differs from

ProClarity Resources

2010-07-06T10:20:00.002-04:00

Hard to find Training, Whitepapers, Webcasts, & Case StudiesBy Dan MeyersBelieve it or not, ProClarity is not going away anytime soon. In fact, I have done quite a few demos lately at some rather large companies. In addition to that, I often get asked by clients about online training material which tells me that there are still new people using the product every day. Many people have problems

T-SQL Fundamentals

2010-05-12T05:15:00.002-04:00

Comparing Similar Approaches to Writing QueriesBy Dan MeyersI often get asked by clients about the “best” way to write a query. Whether they should use IN or EXISTS, table variables or temp tables, a LEFT OUTER JOIN or NOT EXISTS, etc… Most often it depends on what your data is like (does it contain NULL values for example), how are your tables indexed, and a number of other things. In order to

Overcoming Calendar Limitations in PerformancePoint Planning

2010-04-12T13:24:00.005-04:00

Some financial analysts may require to be able to present their budget and forecasts in both fiscal and calendar years. This becomes somewhat of a design challenge when it comes to PPS Planning since the application is set to handle only one Time dimension which is built at the onset of the application’s creation. Moreover, once generated, the time dimension that was created using the “calendar”

URL Encoding in Reporting Services

2010-04-06T13:58:00.005-04:00

Handling Special Characters Using HttpUtility.URLEncode()By Dan MeyersReporting Services allows you to create hyperlinks in your reports using the Go To URL action. This is a very useful feature that provides you with the ability to make almost anything on the report a hyperlink. When creating links to other reports, this action type provides many advantages over using the Go To Report action

Query the Members of an AD Group using a Linked Server

2010-03-11T18:25:00.001-05:00

Using LDAP (SQL Dialect) Queries from SQL Server By Dan Meyers I was recently asked by a client about the best way integrate information from AD (Active Directory Services) into SQL and more specifically into Reporting Services reports. They wanted to be able to grab the userid of the person running the report and do a lookup in AD to see if the user was the member of a specific AD group

Building Great Cubes: Tip 1

2009-12-29T13:02:00.004-05:00

Less is MoreBy Peter SpragueI have seen a lot of SSAS 2005 cubes that look very similar despite being created independently by different customers and partners across the US and Canada. They always look something like this:- 5 or 6 dimensions with few or no natural hierarchies- 70 or more (often many more...) attribute hierarchies, including the ever popular attribute [Customer Fax]- 4 or more

Analysis Services 2008 Metadata Report Pack

2009-11-25T13:41:00.012-05:00

Reporting on Analysis Services Metadata using DMV Schema RowsetsBy Dan MeyersAnalysis Services 2008 Metadata Report Pack DownloadI often get asked by clients about the best way to get metadata about the various SQL Server Analysis Services (SSAS) objects on a particular server. As usual, there are a few ways to go about getting this information. Unfortunately for DBAs and report writers, most of

Empowering the Data Warehouse with External data

2009-10-12T14:57:00.006-04:00

By Rick DurhamTo build a powerful data warehouse you must include as much relevant data from internal and external sources as possible to optimize the decision processes that managers and “C level” executives are called to make each day.As an example, retailers have current/historic sales data along with pricing information, but this will only provide partial insight into the determinants that

Formatting Linked Server MDX Query Results Using CTEs

2009-10-05T14:51:00.011-04:00

By Dan MeyersIn my previous post I described how to export MDX query results in SSIS without having to use a linked server. Even though a linked server is not required and probably not advised the majority of the time, there might be times when it is actually the best solution to your problem.One of the biggest headaches with this approach it that the data types of all of the fields returned are

SSAS Association Algorithm and ItemSet Sparcity

2009-09-21T16:56:00.004-04:00

Checking Data Size and Parameters while working with a Market Basket Analysis modelBy Rick DurhamRecently, I was developing a Market Basket Analysis model at one of my clients, using SQL Server Analysis Services 2005 (SSAS). The reason I we built this model was to understand what items are purchased together so for physical product placement as well as how to position items in

Exporting MDX Query Data with SSIS

2009-08-31T14:10:00.015-04:00

Query from the data flow without having to use a linked server or a script task By Dan Meyers I was recently working on a Scorecard application that displays KPI information from multiple data sources which included a few Analysis Services cubes. This required me to extract some KPI values from the cubes and place them into a SQL Server table (they did not have PerformancePoint up and going yet

Notes from the Field