ETL, Cubes, Reports and Dashboards

Been working on a new project a few days this week.  It's already mid flight for a few weeks, I'm just filling in for a few days.

So what have I been working on?  ETL a half dozen tables into the Data Warehouse from a pre existing data warehouse.  Then created some views.  Then brought into the SSAS cube.

And that's where I got stuck.  Kept throwing the same error.  Looked up every possible bug fix.  Turns out one of the existing views was limiting some of the data from the cube to speed up development processing time.  Once I knew that, applied a fix, and the cube compiled.

So the data was available for viewing, I took the old developers Power View, brought down a local copy, renamed and pushed back to SharePoint.  And changed the Data Source to the new Data Warehouse Cube.  And added the fields back.  Report #1 done.

Next up, some SSRS reports.  Took the old developers Stored Procedure on to the new server.  Changed the table and column names to use the new ones, it compiled.  Then took his SSRS reports, created new Data Source, pointed to new Stored Proc, it ran.  Added a few parameters, uploaded to SharePoint, only problem, the reports are timing out.  And I still need to figure out Row Level Security and add one more parameter.

Then I ETL 2 giant tables into the new Data Warehouse, applied standard naming conventions, all prepped to import into the Cube this weekend.

So that was two 8 hour days of work.  And I have 3 more reports to complete by Monday over the weekend.

Monday, my other project is having their Go-Live, a real big event, the project is about 5 years in development and it should be exciting.  I'll be there for the kick off and if any problems arise with my reports.  Perhaps they'll have some additional work down the road, who knows.  I heard they are supplying bagels, score~!

After that, a new project is starting up, this one's in Orlando, doing Crystal Reports, Business Objects, Webi and Edge.  About 250 reports in total, should be fun~!

And there's another project doing some SSRS Dashboards against a Data Warehouse I created last year around this time.

So vacation's over.  Just passed my one year anniversary being a full time consultant.  Not too shabby.  No complaints from here.


Code Loss is Serious Business

When you write code every day, it sure helps to keep backups.  And working in the Microsoft stack, the tool of choice is Team Foundation Server or TFS.  It's a web based version, often hosted in the Cloud which allows source code backups.  You can assign permissions to users who authenticate and have access to folders, can add, modify or delete files.  It allows for versioning so you can see what changes occurred when and by whom.

You may be familiar with it's predecessor Visual Source Safe, which was a client server application.  Very similar, except it shipped with Visual Studio 4, 5 and 6.  I was actually the VSS Admin at a few prior jobs.

Except the past two weeks, I've been bitten by TFS which almost caused actual code loss.  In one scenario I attempted a roll-back to a prior version, there was a conflict and something went wrong.  My only solution was to grab the latest code from SharePoint which luckily keeps versions as well.

And this week, I accidentally clicked on "remove from project" on a particular file, which deletes it from TFS.  So when trying to "Undo Checkout" it complained that the file no longer existed locally.  Luckily my boss assisted and we applied the Undo on the server rather than on the client and it restored successfully.

Uhg.  Double Ugh.  I've not had this many blunders in quite a while.  And with the same application.  And code loss is some serious business.  Luckily, I was spared such fate.

So all is well, except I will never ever attempt a rollback on source code if my life depends on it.  The correct way is to store off a copy of the code, check out the files, overwrite them locally and upload back to the server.

Man, I need a vacation.  Wait a minute, I just got back from vacation yesterday.  Oh well, hopefully the source control Gods look favorably on my code going forward.

And so it goes~!


A Call from the Big Leagues

I've been tinkering away here at this blog for a few years.  And there has been absolutely no financial reward to speak of.  I express my opinions, stories and technical understanding for the blog-o-sphere day in and day out.

I'm not expecting much.  I do it for the pleasure of self expression and to keep the mind busy.

And I've been plugging away at IT here in the Tampa Bay area for close to two decades.  Yet I'm basically an unknown.  There's people moving to the area who seem to get a running start and pass me by.

It took me a while to get into the full BI stack but now I'm programming every day, full life cycle.  I really have no complaints.

Yet, a few weeks ago, I was taken aback.  I opened my email.  And there was a recruiter solicitation.  Now I get emails very often from people in state and out of state.  But this one caught my attention.  Because of the sender.

It was a recruiter from Facebook.  Asking me to apply for a Data Engineer position.  Holy smokes.  The big leagues.  After all these years, somebody finally noticed.

I don't want to discuss the details of the exchange, suffice to say, I'm not in a position to relocate at the moment.  And the position requires it.

That was a great honor to be asked to interview for such a prestigious position at the top internet company in the world.  With a chance to work on the largest data sets on the planet.

I write it here to validate that it happened, to brag a bit and to say that blogging could have played a factor in them reaching out.

Not being in a position to relocate, I had to decline the interview.

I'm just going to keep on doing what I've been doing for years.


And so it goes~!


What is Full Life Cycle Business Intelligence?

·        What is full life cycle Business Intelligence?
o   Source Data --> Scrub Data --> Business Rules --> Report --> QA --> Insight
·        Traditional Reporting
o   Static Reports with Parameters
§  State / Federal Reporting
o   Report Developer
§  SQL
§  Domain Knowledge
o   Reporting Tools
§  Crystal Reports
§  SQL Server Reporting Services
§  Actuate
o   Relational OLTP Databases
§  Oracle
§  Microsoft SQL-Server
§  Sybase
§  MySQL
§  Microsoft Access
·        Data Warehousing
o   Single version of the Truth
§  Join desperate data sources
o   Established Architecture
§  Kimball Methodology
·        Star Schema
·        Snow Flake Schema
§  Data Modeling
·        Dim Tables
·        Fact Tables
·        De-normalize Data
§  Cubes
·        Multi-Dimensional
·        Tabular Model
o   Data Warehouse Developer / Architect
o   Tools
§  SQL Server Data Tools
§  Universe Designer
o   OLAP Databases
§  Cognos
§  SQL Server Analysis Services
§  SAP Universes
o   Costly
§  Build
§  Maintain
§  Enhance
·        Self Service Reporting
o   Teach users to fish
§  Supply Data sets
§  Data Discovery
o   Tools
§  Tableau
§  Qlkview
§  Microsoft Excel / Power BI
·        Projects
o   What questions try to answer?
§  Who
§  What
§  Where
§  When
o   Scope
§  Define requirements
§  Report Specifications
o   Data Sources
§  Leads
§  Salesforce
§  Financial
§  Phone
o   Business Rules
§  Processes
§  Data Flow
§  Exceptions
o   Infrastructure
§  Applications
§  Databases
§  Tools
§  Servers
§  Source Control
o   Staff
§  Domain Experts
·        Company History
·        Product Experts
·        Information Experts
§  Project Sponsors
·        Funding Project
o   Data
§  Sources
§  Staging
§  Raw
§  Data Warehouse
§  OLAP Cube
§  Data Governance
·        Data Quality
o   Standardize Fields (St. Pete, Saint Petersburg, Saint Pete.)
·        Data Cleansing
o   Remove Duplicates
§  Master Data Services
·        Single location specific data
o   Maintained by Business
§  Credentials
·        Access to Databases
·        Servers
·        OLAP
·        Schedule Jobs
·        Services
·        Certificates
§  Security
·        Active Directory
·        Report / Group Level Security
·        Application Security
o   SharePoint
o   Reporting Services
o   Business Objects
§  Backups
·        Data Warehouse
·        OLAP Cubes
§  Source Code Repository
·        Administrator
·        Project Hierarchy
·        Users
·        Permissions
·        Delete vs. Destroy
§  Testing
·        Unit Testing (Developer)
·        User Acceptance Testing (Business)
·        Pass Criteria
§  Additional Requests
·        Out of scope?
·        Add to project
·        Add Developer
·        Remove other Functionality
·        Extend Deadline
·        Move request to next phase
·        Charge for additional time / work?
o   Deployment
§  Who deploys code to production?
§  Off Hours
§  Outage
·        Project
o   Project Manager
§  Spokesperson between Business and Development
·        Provides meetings based on frequency
·        Progress Reports
o   Estimates
§  Hours presented to Sponsor for approval
o   Project Schedule
§  Deliverables
§  Who does what when listed?
·        By Task Item
·        By Priority
·        By Constraints?