SSAS at Long Last

Lately, I've been working in SQL Server Analysis Services (SSAS).

An entire world has existed in parallel to writing SQL statements.

Theres Measures, Calculated Measures, Hierarchies, KPI Goals, Values, MDX language.

Two things I've learned so far.  It's extremely powerful.  It's complicated.

MDX may look like SQL, yet that's as close as it gets.

Yet the thing is, Microsoft has an alternative solution now with Tabular Models and DAX.

Should I be learning all this new stuff just to see it vanish.  Hardly, this stuff is out in the wild and will need support for a long long time, as well as new clients requesting it.

However one would be smart to learn the Tabular Model.  I happened to learn that first, then SSAS.

For the complete life cycle, you grab data from the relational system, into a Data Warehouse, then either SSAS or Tabular Model, then into Performance Point, Power View, Power Pivot or SSRS.

The good news, since the product is so mature, there's tons of blogs and documentation available on the web.

I first heard of Cubes / MDX in 2001 working for a company called Z-Tel.  I had to write MDX queries to pull data from the cube into a Crystal Report, which I did using Wizards.

Fast forward, 12 years later, it's all starting to make sense.

And so it goes!


Performance Point KPI Development

I was tasked with creating 2 KPI in Performance Point.

So the KPI were created in SQL-Server Analysis Services.

And it turns out some files needed to be loaded like the AMO and OLE DB + some Web.Config setting modifications.

However, they don't take effect until the SharePoint server gets rebooted, so that ate into the development time.

Thursday, I was finally able to create new ScoreCard/KPI in PerformancePoint.  However, the way the KPI is setup, it has to compare the AVG measure with the AVG measure of a Parent.

So in attempts to figure this out, completely on my own, I've had to map out the flow of data from the Transactional system, into the Staging, into the Data Warehouse, then into the SSAS cube, where it gets loaded into Measures, which then have Calculated Measures and finally end up in the KPI Goal field.  From there it gets sent to PP where the data is displayed.

Suffice to say, it's not working.  So while troubleshooting, the cube stopped processing, threw some error about partitioning, which I search Google for a while, then added some permissions to SQL Server as the Service accounts, and then re-booted the database server.

When it came back, the Partitioning error disappeared.  So I'm thankfully back to where I need to be, in order to troubleshoot further.  I'm going to have to punt on this one perhaps and ask the boss for some assistance, as we are presenting the solution on Monday.

I still enjoy the thrill of chasing down bugs, in this case not necessarily a bug but a design concept which must be applied, however, I think we can figure it out over the weekend.



Hiring Quality IT is No Easy Task

Finding qualified IT programmers is no easy task.

How do we find talented people now?

Headhunters.  They post ads on the job sites, social networks and receive resumes, where they weed out many, select the 'horse' with the best chance of getting hired and submit to hiring manager, go through interview process, negotiate deal, in the history books, move on to next slot to fill.

Yet here's another way to find talent, baseball.  They have 'Talent' scouts.  People who are experts in the field, who assess candidates on a variety of skills, who can identify quality players from average who recommend to upper management, who actually pull the trigger.  Many times, the hired gun goes into the minor leagues for a stint to learn the ropes and proves their worth.  The hiring team grooms the player into major league class.

Perhaps we need talent scouts in the world of IT.  Yet that would open up the door for Agents.  Programmers would hire agents to negotiate the best terms who in turn would take a percentage.

The current system seems a bit odd, where more and more jobs go unfilled because they can't find the right talent, yet unemployment is still at high levels.

Perhaps we've simply overlooked the biggest hole in the hiring process.  The HR person.  What do they actually know about technology.  They simply scan the prospective employee, are they well groomed and mannered, are they punctual, do they have the right keywords on their resume.  They juggle dozens of open positions to fill, how much time do they dedicate for such a big decision as hiring a new employee.  Perhaps it's the hiring HR person that's in over their heads, the weak link in the process.  They have quotas, they have demands from the field to get fresh meat in a chair who can be productive in a short time, and they may have no idea what the position actually does.  Yet they wield high influence as they are the gatekeepers of the org, a very powerful position, and may not want to admit they are less than informed on the latest tech trends.

Just some food for thought on the hiring of IT Programmers in today's world.  Seems the system has room for improvement.

What Level of Data Detail for Execs

So what do Executives want to see?  That's right, the data.  At what level detail?  Well, it varies.

Some CEOs like raw data dumps to discover the trends, because they are technically savvy and hands on.

Others like Dashboards, where they can look, get a snapshot to see the trends.  With this approach, it's helpful to have a complete picture view rolled up at the aggregates, with drill down capabilities all the way down to the detail transaction levels, to be most effective.  To identify isolated points of interest and figure out 'Why'.

Similar to newspapers, some like the detail level of the Wall Street Journal while others prefer the higher level like USA Today, with colorful graphics and watered down content.

I've seen both types of management styles in the real world, whatever works best per individual case I suppose.


Production Line of Raw Data to Insights

Business Intelligence requires a few things.
  1. Data Source(s).
  2. Business Rule(s).
  3. ETL Massaging Data.
  4. Render/Deliver Report.
The same core functionalities that comprise a BI Analyst are the same core functionalities required by a Big Data specialist.

Just on a grander scale.
Possibly more complex data sets.
Including Semi Structured or Structure determined at runtime.
Perhaps Statistical algorithms or Data Mining.

If data were a production line, it starts with raw data, flows through an ETL process, where business rules get added and the final product is one form of Report, Dashboard, Visualization, Data set for Self Service discovery, etc.

Henry Ford would be proud of the automation / production line of Data into Insight.


Presented #PowerView as Self Service #BI Option

I was at a client site today giving a demo of our recent construction of a data warehouse.

They liked the progress, yet asked about a self service option, as they had looked in a product called Domo and liked it's capabilities.

So I opened Excel 2013, connected to the new SSAS cube via PowerPivot, showed them the model.

Then clicked the PowerView button and a new tab opened, similar to a Pivot table which they were familiar with, and by simply clicking on a field, it instantly appeared on the window pane.

Clicking on more fields, the response time was quick.  Then by selecting different Charts, we easily converted the dataset to a variety of graphs.  Then added a few more graphs and within minutes we had a working self service dashboard.

The PhD in the room asked if this was going to replace their SPSS, which in truth, it just may.  Because all the heavy lifting was done on the ETL, mapping, vetting the data, a power user can now connect and discover the trends which once required a full blown PhD.

They were quite impressed and after the meeting I was asked if this product could also work on their phone system data and replace the Crystal Reports, sure, why not.

All in all, Microsoft BI offers traditional reporting, Dashboards and Scorecards through PerformancePoint as well as Self Service, Visualizations and Discovery through PowerView/PowerPivot.

A full arsenal at the customer's disposal.


Customers Just Want Accurate Reports

From my experience customers don't really care about having bad data.

Their thinking, let the Business Intelligence guy worry about that.

All I know is I want accurate reports.

So that means the BI people are tasked with writing exceptions upon exceptions to handle inconsistencies in the data.

And I suppose that's why BI people earn a good living servicing the needs of customers.

That's life!


Attending #SQLPass Summit 2013

It's official, I'll be attending the SQL Pass Summit  2013 in October.

First timer!  Very excited!!!

Building a Data Warehouse Project

My new consulting job, first few weeks I worked remote from home.

This week, I've been on a new client's site, working on Proof of Concept Data Warehouse.

Spent first day modeling the data on the white board, documenting to Excel in a Data Dictionary.

Yesterday I created the SQL-Server database as well as the staging data warehouse tables.  As well as connecting to the database using ODBC.

Today I need to create the SSIS ETL jobs for the 6 Dim tables and 1 Fact table, due by the end of the day.  We are grabbing data from two data sources, however think I'll get the first one working from start to finish then get the other data source working.

That way we can begin building the SSAS cube tomorrow and start on the PerformancePoint on Monday.

I've been given creative freedom on this project, although I do send a few emails to the boss occasionally for confirmation or questions.

And I still have work to do on my other client.  However, I like the fact that I don't have a full time job at one company, swimming in mediocrity and boredom and politics while not learning any new skills with a 2% raise if I'm lucky.

Things are working out fine!


Data Warehousing on the Agenda

With the client today we gathered design specs by asking a series of questions to form table structures of Dimension and Fact tables.  The design changed frequently based on new questions, hashing out the business rules to finalize some table structure.

After lunch, I sat with the developer to map the Staging and Data Warehouse tables to their Transactional system.  When I got home, I created the 6 Dim tables 1 Fact table in Staging, then again in the Data Warehouse.  Then created the SSIS ETL queries to move the data through they system.

Tomorrow I'll ask some more questions to finalize the Data Dictionary we've been keeping and then begin to pull data from OLTP.  Once that's working, I'll begin to create the Cubes in SQL-Server Analysis Services (SSAS) followed by new reports in SharePoint PerformancePoint.  Considering we just met the client for the first time late Friday afternoon and Monday was a holiday, we're off to a good start.

This "Proof of Concept" is due at the end of the month so they can demo everything.  And maybe we'll get the 3 year contract to build a full blown Data Warehouse, wouldn't that be nice!

Data Warehousing is not easy by any means, except once you figure out the pattern of modeling the data, documenting the sources and mapping the fields, then building the tables and doing the ETL, it kind of builds itself being able to reuse code patterns.  The SSAS will be new for me as my experience is mostly with Tabular Model, so the MDX may be a steep learning curve.

Learning new technology is great, applying it in real world scenarios, priceless!


Web Apps Need to Integrate #BI

Traditionally, IT has segregated the front end web app and the Reporting / Business Intelligence.

A report person was brought into the project at the end to produce some last minute reports.

Which typically left the power users and management blind to how their business was running.

Which create a chasm between the Business Units and IT.

The biz was not getting their needs met.

So there's been recent discussions on Self Service BI to get the Business up to speed on metrics, performance and improved decision making.

Except the process has not made it full circle.

What still needs to happen is to blend the BI into the web app, a cohesive unit, transparent to the end users.

Take Amazon, because you bought a book on how to make the Hottest Chile, you may also want a book on how to deal with heartburn.  They've build algorithms to associate groups of items which a customer may want based on rules derived from mining the data.

That is the future trend - a synthesis of front end web apps and Business Intelligence based on Machine Learning.

As well as "embedded analytics" built into the front end apps.

Then the cycle will have come full circle.