Archive for the ‘SQL Analysis Services’ Category

Predixion Insight – Data Mining

January 22, 2011 1 comment

As I’m based in Sydney, I missed the webinar due to the time difference between US and AU. But luckily, I received a replay link and watched the demo.  Although I’ve play with Predixion software before, it is still exciting to watch over the demo.

Basically, in addition to the data mining functionality similar to the DM add-in for Excel, Predixion Insight is able to talk to PowerPivot. It is able to apply the trained mining model to the data set in PowerPivot and tag the record with the mining result. Then, using DAX to further format the dataset in PowerPivot and you can easily get a nicely analyzed predictive analytic report. Even more, with PMML support, you can even import SAS, SPSS model into Predixion Insight and then apply it to the dataset in Excel/PowerPivot as well as validation tools built into Predixion Insight to validate the model. Also, with Excel Services in SharePoint 2010, the final report can be uploaded and shared by everyone.

Before I had a bit concern about the data volume supported by Excel as well as Internet speed. Now I am much more confident after watching the demo. The PowerPivot would overcome the Excel data volume limitation. Regarding the data transferring across the Internet, the case given by Predixion software is that one of their client sent a 10 million records PowerPivot to the cloud server and it only takes around 4 minutes to get the mining results from the remote server. I believe in most cases, it would satisfy most of the small to medium organisations.

Now the only concern I have at the moment is the operational BI support. Imaging I have a model in the cloud used for identifying the customer category (e.g. good/bad). If a new customer is set up in the OLTP system, it would be fantastic if the system can query the model and tag the customer. In this case, Excel is not used at all. Instead, a web-service or APIs are required so that the OLTP systems can interact with the cloud mining services.

FW: Days Since Last Event: TSQL vs. MDX vs. DAX

January 19, 2011 Leave a comment

There is a nice blog discussing “Days Since Last Event”, you can find it here.

It compares the same logic implemented by TSQL, MDX and DAX. In my perspective, the TSQL is the most easy to understand. The MDX (2nd solution) is elegant. The DAX one is the hardest to understand. (Tips: don’t read the DAX code in the text, read from the screenshot and the code is formatted better).

Talking about Excel 2010 on SSAS 2008 R2 Named Set Creation

Categories: SQL Analysis Services

Time to learn PowerPivot in Excel 2010 (Gemini)

October 28, 2009 Leave a comment
A real showcase from end to end develop a cube
Great Football Project
Categories: SQL Analysis Services

FW: Weighted Average: from requirements to implementation

September 22, 2009 Leave a comment
Categories: SQL Analysis Services

Thoughts Regarding “Kimball University: Five Alternatives for Better Employee Dimension Modeling”

September 7, 2009 Leave a comment

Today I read the article "Kimball University: Five Alternatives for Better Employee Dimension Modeling"

The basic employee dimension structure is quite common. However my personal opinion is that in most cases, employees change a lot while roles (company structure) are relatively stable. Hence, I prefer at the DW layer, separate the employees from the roles and make roles a self-referencing table. e.g. DimEmployee, DimRoles (or even DimEmployee, BridgeEmployeeRoles,Dimroles if you need many to many relationships). By doing this, when an SCD type 2 update from the top to bottom (let’s say CEO is changed) happens, you don’t have to replicate the whole tree. As even the person of CEO is changed, the position is not changed. People below CEO now is still under CEO. The only thing changed is that now the CEO is not XXX. He is YYY at the moment.

By the way, the Alternative 5: The PathString Attribute is quite interesting.

Categories: SQL Analysis Services

Talking about SUBCUBE vs. Slice for YTD()

September 1, 2009 3 comments



SUBCUBE vs. Slice for YTD()

I had the same issue some time ago.

Subselect does not set the current coordinate in the cube. Hence, in many cases, it does not filter the data as you expected. YTD, Existing, Tail etc all having similar issues.


In order to make sure the MDX returns a correct result, either set the current coordinate by put it on an axis or put it in a where clause. As Excel 12 extensively use subselects, we need to be very cautious. And personally, I always drag time dimension to the report filter or one of axis.

Categories: SQL Analysis Services