DawaBI Blog | DawaBI

Blog

DawaBI Merges with Quanta Intelligence to Broaden and Deepen our Service Offering

We’ve been working on this for a while now and I’m excited to announce the merger of Quanta Intelligence and DawaBI. This merger will deepen and broaden our service offering to provide more value to our customers through knowledge and expertise. With Datalere, you are not just provided a resource, you get our whole team!

Going forward, we’ll be doing business as Datalere. The name, Datalere was formed from a combination of Data and Alere. Data as anything we do will involve data and Alere which the Latin translation is ‘to care for’ or ‘tend to’. The name says it all, we are data experts and we care about your data.

Our teams have been involved in projects ranging from few hour consulting engagements to multi-year high profile projects for startups and Fortune 500 companies. The services we provide are Data Warehousing, Business Intelligence, Big Data, Software Development, Mobile Development and Database Administration.

Customer Data is growing at an alarming rate and these days, businesses are collecting information from so many sources, it’s challenging to manage. The intelligence we can obtain from this data is endless. Whether running reports or predicting trends, it all starts with how your data is cared for.

Many of our clients have the horsepower and expertise to keep Data Warehouse and Business Intelligence projects in house but the data that has been compiling over the years is unhealthy. If not modeled correctly, a DW/BI project can stall or never reach its full potential. Before starting your next project, Datalere can provide you with a free data health check.

A few of the many benefits our customers have received are:

  • Insights into data that enable data-driven decision making reducing error prone manual practices
  • Reporting efficiencies of reducing reporting run times from 25 minutes to 3 seconds enabling business users to access critical data for timely decision making
  • Predictions into customer profitability or lack thereof that allow the sales team to focus on higher return potential
  • Stalled project turnaround and creating a usable solution that meets business needs
  • Team mentoring, keeping customer’s staff skills up to date and progressing

To request a free data health check and initial consultation, visit our website or give us a call, (303) 223-0248. To stay up to date with the latest at Datalere, tips and tricks, lunch and learns and more, join our mailing list.

By |August 18th, 2016|Uncategorized|

PASS SQL Saturday Events: December 2014

 

Date Event
December 6 SQLSaturday #359 – Istanbul 2014
December 6 SQLSaturday #351 – Lima 2014
December 6 SQLSaturday #347 – Washington DC 2014
December 13 SQLSaturday #356 – Slovenia 2014
December 13 SQLSaturday #354 – Charleston 2014
By |November 15th, 2014|PASS, SQLSaturday|

PASS SQL Saturday Events: November 2014

 

Date Event
November 1 SQLSaturday #339 – Shanghai 2014
November 1 SQLSaturday #337 – Oregon 2014
November 22 SQLSaturday #355 – Parma, Italy 2014
November 22 SQLSaturday #353 – London – BA Edition 2014
November 22 SQLSaturday #350 – Winnipeg 2014

 

By |October 15th, 2014|PASS, SQLSaturday|

PASS SQL Saturday Events: October 2014

 

Date Event
October 4 SQLSaturday #335 – Athens 2014
October 4 SQLSaturday #336 – Holland 2014
October 4  SQLSaturday #344 – Tirana 2014
October 4 SQLSaturday #315 – Pittsburgh 2014
October 4 SQLSaturday #330 – Charlotte – BI Edition 2014
October 11 SQLSaturday #319 – Panama 2014
October 11 SQLSaturday #333 – St. Louis 2014
October 11 SQLSaturday #311 – Bulgaria 2014
October 18 SQLSaturday #345 – Riyadh 2014
October 18 SQLSaturday #341 – Porto 2014
October 18  SQLSaturday #314 – Providence 2014
October 25 SQLSaturday #352 – Sydney 2014
October 25  SQLSaturday #338 – Barcelona 2014
October 25 SQLSaturday #329 – Rio de Janeiro 2014
October 25 SQLSaturday #348 – Ica, Peru 2014
October 25 SQLSaturday #326 – Tampa – BA Edition 2014
October 25 SQLSaturday #332 – Minnesota 2014
October 25 SQLSaturday #349 – Salt Lake City 2014
By |September 15th, 2014|PASS, SQLSaturday|

PASS Virtual Chapter Events: September & October 2014

PASS Virtual Chapter Events: September 2014

Join SQL Server professionals from around the world for free online technical training and networking year round. PASS Virtual Chapters (VC) are interest-based forums that unite the SQL Server community through live webcasts with top SQL Server experts, online forums and resources including an archive of past presentations, and special training events.

Date Time Title Link
September 16th 11:00 CST The Roadmap to Better Performance: Reading Query Plans https://attendee.gotowebinar.com/register/3935830875052414466
September 17th 12:00 PST Learn JavaScript Properly Part 2 https://attendee.gotowebinar.com/register/4322399372235297538
September 17th 4:00 EST Absolute Beginners Presentation for Power BI for Office 365 https://attendee.gotowebinar.com/register/4757737807317828865
September 18th 12:00 EST Insider's Introduction to Microsoft Azure Machine Learning (AzureML) http://bit.ly/PASSBAVC091814
September 26th 11:00 EST Improving Query Efficiency with Data Access Layers http://oracle.sqlpass.org/
October 8th   12:00 CST Understanding your SQL Server IO Bottlenecks and how Flash can solve them https://attendee.gotowebinar.com/register/6093820357156042754
October 16th 10:00 EST MrExcel's Favorite Excel Tips & Tricks https://attendee.gotowebinar.com/register/8089449354432962050

 

By |September 11th, 2014|PASS, Virtual Chapters|

PASS Virtual Chapter Events: August 2014

 

Date Time Title Link
August 15th 12:00 EST Manage Your Shop with CMS and Policy Based Management https://attendee.gotowebinar.com/register/4279519516082609665
August 19th 11:00 CST The Roadmap to Better Performance: Reading Query Plans https://attendee.gotowebinar.com/register/2124351382524970497
August 20th 12:00 PST Learn JavaScript Properly http://bookreaders.sqlpass.org/
August 21st 12:00 EST Looking Through the Windshield: Creating Forward-looking Dashboards and KPIs  https://attendee.gotowebinar.com/register/5282114692357849346
August 21st 1:00 EST SQL Server Security Easy Button https://attendee.gotowebinar.com/register/4752057729799585025
August 27th   12:00 EST The Curious Case of Isolation Levels http://dba.sqlpass.org/

 

By |August 15th, 2014|PASS, Virtual Chapters|

PASS SQL Saturday Events: September 2014

 

Date Event
September 6 SQLSaturday #301 – Cape Town 2014
September 6 SQLSaturday #320 – Raleigh 2014
September 13 SQLSaturday #323 – Paris 2014
September 13 SQLSaturday #300 – Kansas City 2014
September 20 SQLSaturday #290 – Kiev 2014
September 20 SQLSaturday #310 – Dublin 2014
September 20 SQLSaturday #331 – Denver 2014
September 20 SQLSaturday #340 – San Diego 2014
September 27 SQLSaturday #325 РṢo Paulo 2014
September 27 SQLSaturday #343 – Medellin 2014
September 27 SQLSaturday #334 – Boston – BI Edition 2014
September 27 SQLSaturday #318 – Orlando 2014
September 27 SQLSaturday #342 – Mobile 2014
By |August 14th, 2014|PASS, SQLSaturday|

PASS SQL Saturday Events: August 2014

 

Date Event
August 2 SQLSaturday #324 – Baton Rouge 2014
August 9 SQLSaturday #304 – Indianapolis 2014
August 23 SQLSaturday #328 – Birmingham 2014
August 23 SQLSaturday #309 – Oklahoma City 2014
August 30 SQLSaturday #327 – Johannesburg 2014
August 30 SQLSaturday #317 – Oslo 2014
By |July 15th, 2014|PASS, SQLSaturday|

Will you be attending Mile High Tech Con?

Technical conferences have always been a great venue for networking and increasing my knowledge base. Mile High Tech Con will be no different. I would like to invite you to be part of the inaugural Mile High Tech Con which will be held July 24 -26 in Denver, Colorado. Mile High Tech Con will highlight innovative topics that will inspire strategic decision making while offering relationship building and networking.MHTC-Twitter

Here are the speakers and topics:

Speaker Topic
Bill Fellows SSIS package generation via Biml
Carlos Bossy Getting Started with Big Data and Business Intelligence
Jeff Renz Dashboards More Than One Way to Visualize Data
Meagan Longoria Somebody Got BIDS Helper in my Data Tools!
Peter Myers Taking Your Application Design to the Next Level with SQL Server Data Mining
Reeves Smith Master Data Services, How Does it Apply to My Enterprise?
Scot Reagin Strategies for Fixing Broken Information Environments
Tim Mitchell Error Handling Patterns in SSIS
Bill Fellows Getting started with R and SQL Server
Carlos Bossy SQL Server Replication in a Business Intelligence Framework
Dan Lewandowski Securing the cube
Jeff Renz Tabular Models
Meagan Longoria Don't Miss Out on Excel Cube Functions
Peter Myers Big Data Analytics with Excel
Reeves Smith Increase your SSIS productivity with Biml
Scot Reagin Taking the Mystery out of DAX
Tim Mitchell Introduction to Data Quality Services
Carlos Bossy Predictive Analytics in the Enterprise
Peter Myers Superhero Power BI
Tim Mitchell Real World SSIS

There is still time to register and attend. You don’t want to miss it!

By |July 9th, 2014|Networking|

A Simple TSQL Pivot

I’ve always had issues writing a TSQL Pivot statement without going back to books-on-line to figure it out.  Maybe because I haven’t done it enough, but it always seems a bit convoluted.  That meant it was time to write about it and break it down so I could make sense of it.  Let’s set up the demo data first.  Based on the data I was working on pivoting I wrote up some simple demo data.  It has a name, objective and a result for the said objective.  It has one record per objective and the pivot should line up those objectives so there is one row per name instead of one row per objective.

DECLARE @BaseData table (  

Name   varchar(10)  

,Objective  varchar(5)  

,Result   varchar(15)  )

 

INSERT @BaseData

SELECT ‘Bob’,’Obj1′,’Complete’

UNION

SELECT ‘Bob’,’Obj2′,’Pending’

UNION

SELECT ‘Lisa’,’Obj1′,’Pending’

UNION

SELECT ‘Lisa’,’Obj2′,’Pending’

UNION

SELECT ‘Don’,’Obj1′,’Complete’

UNION

SELECT ‘Seth’,’Obj1′,’Complete’

UNION

SELECT ‘Seth’,’Obj2′,’Complete’

UNION

SELECT ‘Megan’,’Obj1′,’Pending’

UNION

SELECT ‘Megan’,’Obj2′,’Complete’

 

 

SELECT * FROM @BaseData

 

 Now to work on understanding the pivot.  Here’s the finished Pivot statement for reference: 

PIVOT(MAX(Result) for Objective in ([OBJ1],[OBJ2])) as ResultPivot

 The [Result] column is the data that needs to be pivoted.  The first stumbling part of the pivot is that the data that is being pivoted has to be wrapped in some kind of accumulation.  So if you are dealing with numerical data it can be a MIN, MAX, AVG, etc.  With string data, which is what I dealt with the easiest is MIN or MAX.  Since there is only one result per objective in this data, the MAX will be that value.

The next part of the pivot is naming the columns that the [Result] data will be pivoted into.  I associated this to telling the PIVOT command what the key was to the data to be pivoted.  In this case there are consistent values in the Objective column of Obj1 and Obj2.  Not all Names in the data have an Obj1 and Obj2 which is fine as you will see.  If you don’t have a consistent value like this I could see using a derived column like a ROWNUMBER() or something similar to give the data values to create columns on.

After naming the columns that the pivoted data will go into the entire statement needs an alias.  It won’t necessarily be used in the SELECT statement but the PIVOT statement will throw errors if it is not aliased.

Here is how the PIVOT is worked into the query. 

SELECT Name, OBJ1 as Objective1, OBJ2 as Objective2                FROM @BaseData

                PIVOT(MAX(Result) for Objective in ([OBJ1],[OBJ2])) as ResultPivot

 Notice in the SELECT statement the columns from the pivot get the names from the PIVOT statement.  I did this because I wanted to alias the column names to be more descriptive.  If you get rid of the column names and just put in SELECT it will work too.  If you put the [RESULT] column in the columns to SELECT the statement will error and throw Invalid Column Name.’  The [RESULT] column basically no longer exists when the data is pivoted.  When the query is run (with the table variable above to create the data) the results will look like this:

Name Objective1 Objective2
Bob Complete Pending
Don Complete NULL
Lisa Pending Pending
Megan Pending Complete
Seth Complete Complete

 If you look at Don’s record you will see that Objective 2 is NULL.  That objective does not exist for him.  If there are hundreds or thousands of records that are being pivoted and a flag needs to be created if there are missing objective records it’s simple.  Those objective columns can be used in the query just as normal columns.  In the query below you can see how a CASE statement was added to flag those people with missing records.

SELECT Name, OBJ1 as Objective1, OBJ2 as Objective2                ,CASE

                                WHEN OBJ1 is NULL THEN ‘TRUE’

                                WHEN OBJ2 is NULL THEN ‘TRUE’

                                ELSE ”

                                END as ErrorFlag

                FROM @BaseData

                PIVOT(MAX(Result) for Objective in ([OBJ1],[OBJ2])) as ResultPivot

 The results of this query will have an additional column called ErrorFlag that will have a value of TRUE if there is a NULL in Objective 1 or Objective 2.  This will allow for analysis of the data to easily point out those records.

Name Objective1 Objective2 ErrorFlag
Bob Complete Pending  
Don Complete NULL TRUE
Lisa Pending Pending  
Megan Pending Complete  
Seth Complete Complete  

 Another way of making the NULL data stand out or if NULL data doesn’t matter at all the OBJ1 and OBJ2 could be wrapped in an ISNULL.  That could be used to put in an empty string or some value to indicate problems with the data.

Hopefully, this helps out.  I’m sure the next time I write a PIVOT I’ll have to dig out this posting and skim through it real quick to remember the details.

By |July 8th, 2014|TSQL|