PointClickCare (PCC) has so many modules and rich data to pull from to help manage your business.

From the PCC data relay you can pull in info like

  • AR/Billing side of the world like agings, cash collections, DSO, revenue.
  • Census skilled mix, occupancy rate, therapy score breakdown, revenue per patient day
  • Clinical readmission rates, average length of stay, census, weight
  • MDS can analyze detail question and answer keys to track falls and so much more
  • Financials pull in income statement data, accounts payable, balance sheet data

By having data like the above readily available in a Microsoft Analysis Services Data Warehouse, you can automate reports & more importantly automate workflows throughout the organization.

What we are diving into is where newer cloud-based reporting solutions like Domo fall short.  They focus primarily on reporting.  What if you want to automate?  Below is an example of adding some automation to a write off process.

Automating & Adding Controls to your Write Off Process

For example, let’s say you have a write-off process that you manage through excel today.

By connecting near real-time data to your data warehouse, you can automate & add controls around business processes.  Below shows a dynamic list of write-offs from PCC AR transaction module.  It is comparing against a list of approved write-offs so you can quickly see which transactions from PCC went through that were not reviewed and approved.

The amounts like the $19,420 can be dynamically pulled into the tool using Analysis Services excel functions.

How does it work?


The cells in column J (Orange) all use the CUBEMEMBER function.  The cells to the right (in column K) show the formulas.  These functions help filter your query so you can pull in specific data from Analysis Services.  An example is like setting your query to “July 2018”)

CUBEMEMBER Example (Blue Cell in Column K)

  1. The first parameter is “DW OLAP Connection” which is your OLAP Data Warehouse connection
  2. The second parameter defines a member of an Analysis Services Data Warehouse.  Here is a breakdown!


Dimension (a way to look at your data in the data warehouse)


A Hierarchy (a specific way to look at your data from a time perspective,  other examples are fiscal view or weekly, etc.)

[“&F3&”]  which points to “July 2018” the member of the Date dimension

This makes the formula dynamic so that a user can type in any date in cell F3.  This helps future proof your tool.  All a user needs to do is type in “June 2018” for example, and now all of the data will automatically pull in for that period.

CUBEVALUE Example (Green Cells – How we pull in $19,420 value dynamically)

  1. The first parameter is “DW OLAP Connection” which is your OLAP Data Warehouse connection
  2. The rest of the references point to CUBEMEMBER functions (all of the orange cells)!  What this function says is, grab me the “Amount” from the data warehouse for “Meadow Groves”, for “July 2018” for Account “1201.00” with “X” transaction type. This produces the value $19,420 which comes from PCC AR transaction data.  Neat!

Why it is powerful

You can quickly change any of the cube member references to pull ANY data point about your company into excel!  The automation potential is limitless.  Have financial data in Analysis Services as well?  Pull account balances and automatically reconcile accounts.

By having your own Analysis Services Data Warehouse, this empowers your accounting/finance & IT teams to automate current processes and help streamline workflows.  It reduces manual efforts and mistakes and increases accuracy and productivity.  Investing in automation also helps protect your company from loss of knowledge due to turnover.  New employees come on board using these tools that help teach and ensure quality.

This technology allows your accountants & AR teams to do more valuable work like analyzing expense variances.

Want to build one of these Analysis Services Data Warehouses for your company?  We would love to help!

Kevin Baker
Data Services Partners

Hi, I’m Kevin.

As former Director of Data Services at Ensign Services, a publicly traded company, I have an extensive background and expertise in the Skilled Nursing sector and the needs of both the management and information technology sides of the business. We’re experts in the systems and metrics that support Skilled Nursing and Post Acute Healthcare businesses.

My passion is helping businesses transform their data into actionable information to make better business decisions. We are developers you can trust. Data is our passion.

If you have a question about this article, or want to have a discussion about it, let’s chat!

Did you like what you read?

Keep up with our analysis, coverage, and helpful information regarding the Skilled Nursing sector by joining our mailing list.

We are developers you can trust. Data is our passion.

Your email address is safe with us. We never pass your information to third-parties and you can opt out at any time.

Looking forward to connecting with you!

Join our mailing list!
First Name*
Last Name*