Top 5 Ways to Use Aginity Pro’s Catalog

You’ve found Aginity Pro. And it’s helping. But you still might be writing code over and over again. (Think about it…you are and you know it).

We’ve tried to tell you, you really need to try out Aginity Pro’s active analytic catalog! But you still haven’t done it yet.

So today, I’m going to ramp up the persuasion. I’m sharing my top five ways you can get the most use of our active analytics catalog. Give it a try, and I bet it will make you and your team even more productive.
Aginity Pro Catalog

1. Standardize Inconsistent Business Rules (that get buried in WHERE clauses and CASE statements across your analysts)

If you are reading this and have never had a conversation like this one, please call me and tell me all about where you work:

YOU: Hey boss—you know how we kept reporting lower sales figures per customer than the marketing department’s reports?

BOSS: Yes, it’s driving the leadership team nuts.  Which number is right?

YOU (nervously): Theirs is… it turns out that 6 weeks ago, a new transaction code was added to the POS system and we never updated our logic.   So we’ve been missing these sales in our counts.

BOSS (head explodes): That’s it? One little code value??!?!?

They seem like such minor things, but in every company we work with, the logic in SQL CASE statements and WHERE clauses is, funnily, WHERE so many inconsistencies are introduced that make it such a challenge to maintain a “single version of the truth.”

Imagine manually modifying a CASE statement hundreds of lines long every time your web and mobile apps change. No thanks! But with Aginity’s active analytics catalog, you can store these complex rules as snippets instead, making a 200+ line SQL statement look much cleaner and beautiful, like this:

SELECT

     Event_ID

     ,Event_code

     ,Page_type_code

     ,Event_timestamp

     ,Visitor_ID

     ,App_ID

     ,Browser_type

     ,@{Business Event Type} (<=== replace the CASE statements with a catalog reference)

As long as reporting analysts, data scientists and ops teams ALL reference this object in their queries, a single change to the catalog will update everyone’s logic at the same time and everyone will get the updated rules the next time they update a query. Nifty!

2. Teach Your Team to Fish (create a standard, parameterized queries that prompt your users to self-serve)

Lots of SQL today finds its way from the “author” into the hands of business users who mostly execute the SQL to pull data that they need.  Just as in the CASE example above, as business logic changes, it’s hard to ensure this makes its way to all the downstream consumers.  And when you hand off a SQL statement, there is no way to ensure that those “helpful” analysts on the receiving end aren’t going to modify the query in some way that they think is better for their purpose.

Aginity Team’s catalog addresses both these challenges when supporting a network of SQL “consumers.”  Instead of giving raw SQL to an analyst, I can just share a reference with them:

SELECT

     *

FROM

     @{Team Catalog/Customer Profile}

This ensures that the author controls the definition of that Customer Profile and maintains the logic in one place. And this becomes even more flexible with the use of parameters.  Aginity Team and Aginity Pro support parameters which then prompt the user to fill in at run-time.  So if that Customer Profile was something that changed year to year, I might prompt a user to supply the Fiscal year they were interested in:

What is an
active analytics catalog?

3. Flatten Your Data Warehouse (standardizing many frequently used JOINs in normalized databases)

Do you ever feel like you spend 80% of your time joining the same tables together over and over and over?  When I worked in health insurance, if I had $1 for every time I joined CLAIM to MEMBER, PROVIDER, DIAGNOSIS, FACILITY, etc., well….let’s just say I wouldn’t be sweating my deductible anymore.

The catalog in Aginity Pro/Team is great for this as well.  Think of these JOINS as a “block” that you can call anytime you’ll need fields from across those tables. 

SELECT

     @{TypicalMedicalClaimsFields}

FROM

     @{MedClaims Block}

WHERE

     Patient_ID = $some_value

Looks like a lot less typing than this, doesn’t it:

SELECT  *

FROM   

     MED_CLAIMS_FACT  MF

     INNER JOIN MEMBER_DIM  MD ON MF.MEMBER_ID = MD.MEMBER_ID

     INNER JOIN PROVIDER_DIM PD ON MF.PROVIDER_ID = PD.PROVIDER_ID

     INNER JOIN DIAGNOSIS_DIM DD ON MF.PRI_DIAG_CD = DD.DIAG_CD

     INNER JOIN FACILITY_DIM FD ON MF.FACILITY_ID = FD.FACILITY_ID

WHERE

     Patient_ID = $some_value

4. Simplifying Complex Code (ugly sub-selects and joins that you frequently use)

Big nested sub-SELECTS are famously inconvenient: they make SQL hard to read, hard to write, and often introduce cardinality issues. Thinking of these sub-queries as “sets” or “views” in your catalog makes it really easy to grab the logic you want and create very readable logic. For example:

SELECT a.*, b.*

FROM

     CUSTOMER_MASTER_TABLE a

     INNER JOIN @{Sales by Customer – Last Week}  b

     ON a.customer_id = b.customer_id

WHERE

     a.customer_id in (@{Lapsed Customers – Last Month})

5. Selecting Common Fields (from large tables you use)

It’s great when IT gives you a data mart that has all the attributes you might want for analysis on as few tables as possible.  For the 10% of strange questions you get, it means you probably have what you need.  But for the 90% of pretty common questions, I find I’m often selecting the same set of 15 columns out of sometimes 150+ options.  So I end up pasting the same big block of columns into my query again and again.

Creating a catalog entry for this makes it much easier to insert these common fields and to change them in one place whenever there is a column name change or when something is added.

SELECT

     @{Product Master Data – Common}  (the 15 columns I always pick in the master table)

     ,product_class_code_4

     ,product_class_code_5

     ,etc.

FROM

   PRODUCT_MASTER  PM

WHERE

    Discontinue_DT > $prompt_discontinue_date

All right—all right, that’s my top 5 cases where Aginity Team’s active analytics catalog can help you out!  How are you using it?  Let me know.  We’d love to have you guest-blog for us!

Share on linkedin
LinkedIn
Share on email
Email
Share on twitter
Twitter
Share on facebook
Facebook