Author Archives: bob

Connect Microsoft Analytics and Business Intelligence Tools to Octo

Introduction

Features for Octo to be desirable in enterprise environments include support for:

  • SQL Views to simplify complex underlying data relationships to convey meaningful information
  • Paginated reports for administrative and regulatory reports
  • Dashboards for real-time analytics

Given the popularity of the Microsoft Windows ecosystem, the set of tools PowerBI Desktop, PowerBI Report Builder, and Excel have a large user base and between them provide the needed functionality. The PowerBI tools integrate with Windows Authentication, connecting the ability to view reports with a user’s identity.

ALL SAMPLE DATA SHOWN HERE IS FICTITIOUS.

 

PowerBI Desktop

With PowerBI Desktop you can create web-based interactive dashboards and front-line applications for decision making. The following screenshots show the creation of a sample report of allergy data in a VistA application. Rocto supports the standard PostgreSQL ODBC driver.

 

FIGURE 1: ODBC CONNECTION DIALOG

 

FIGURE 2: SELECT DATA SOURCE FROM POWERBI

 

FIGURE 3: NAVIGATOR: NAVIGATE TABLES

 

FIGURE 4: NAVIGATOR: TABLE DATA PREVIEW

 

FIGURE 5: NAVIGATOR: VIEWS

 

FIGURE 6: TRANSFORM DATA

 

FIGURE 7: TRANSFORM DATA: CHANGE DATA TYPE

 

FIGURE 8: RELATIONSHIPS VIEW

 

FIGURE 9: SAMPLE REPORT

 

PowerBI Reports Builder

PowerBI Reports Builder creates reports to be printed or shared as PDF documents.

FIGURE 10: REPORT SOURCE

 

FIGURE 11: REPORT QUERY DEFINITION

 

FIGURE 12: REPORT DESIGNER

 

FIGURE 13: FINAL REPORT

 

Excel

Excel is perhaps the most popular spreadsheet. The visual database tools work well with Octo. Here are some screenshots illustrating various operations with the visual database tools.


FIGURE 14: MICROSOFT QUERY LINKING PATIENT AND PATIENT ALLERGY DATA

 


FIGURE 15: DATA CONNECTION WIZARD SHOWING ALL THE VISTA TABLES

 


FIGURE 16: EXCEL SINGLE TABLE DATA

 


FIGURE 17: EXCEL GRAPHING DATA FROM PATIENT FILE

 

Conclusion

If you have PowerBI Desktop, PowerBI Report Builder and Excel on your computer, you can use the Docker Octo VistA VeHU image at https://hub.docker.com/r/yottadb/octo-vehu to run the examples for yourself, and much more.

The command:

docker run -d -p 2222:22 -p 1338:1338 -p 8089:8089 -p 9430:9430 –name=octo-vehu download.yottadb.com/yottadb/octo-vehu:latest-master

will start the Docker image, with Rocto at TCP port 1338 listening for incoming connections.

 

Optimistic Concurrency Control

Unlike most high-end transactional database engines, YottaDB uses Optimistic Concurrency Control (OCC) to implement ACID transactions. Since transactions do not typically collide, optimistic techniques scale up better than other techniques. OCC and the daemonless architecture reinforce each other to provide the extreme level of scalability that YottaDB users have come to expect.

How YottaDB OCC Works

Every database records a transaction number in its file header. Each database block header records the database transaction number when that block was last updated. When a process is inside a transaction:

  • The database engine tracks the file and block number of each database block read, and the transaction number of that block. Since a block to be updated must first be read, this list includes blocks to be updated.
  • When application logic updates the database, the update is retained in process-private memory and does not update the database. The update is visible to subsequently application logic within the process, but is not visible to any other process until the transaction commits.
  • When the application logic signals that it is ready to commit the transaction, the database engine checks whether any block read within the transaction has a higher transaction number than that it has recorded. If none has — the typical case — the engine commits the transaction.
  • If even one block has been updated since the process read it, the database engine discards its work and restarts the transaction logic. This restart is transparent to the application logic, which does not need to be coded to be aware of YottaDB transaction processing. This simplifies application code and improves maintainability. In the most common case, where a collision results from a random overlapping update by a concurrent process, it is able to commit on the second attempt.
  • If the second attempt also fails to commit, the engine makes a third attempt, and if that fails, the engine concludes that pathological application logic is causing the collisions, and makes a fourth and final attempt during which updates by other processes are blocked, allowing the transaction to commit. [Applications should be written to avoid pathological colliding concurrent transactions; YottaDB provides tools and support services for application developers to identify such pathology should it occur.]

OCC Enables Scalable ACID Transaction Processing

YottaDB implements transactions with strong Atomic, Consistent, Isolated, Durable (ACID) properties. Consider application logic to move $100 from a savings account to a checking account, which conceptually consists of the following steps:

  • Validate that the accounts exist, that the requested transfer is permitted, that there is sufficient balance, that the transfer request is authenticated, etc.
  • Subtract $100 from the savings account.
  • Add $100 to the checking account.
  • Compute and debit any applicable service charges.
  • Create a record to log the transaction.

Consider that application logic executing concurrently with an update to business rules affecting minimum balances and service charges, and a balance inquiry. Each of the three is implemented as an ACID transaction.

Atomicity is the property that the entire transaction happens or none of it happens. For example, in the event of a system malfunction, it should not recover to an intermediate state such as between the withdrawal from savings and the deposit to checking.

Consistency is the property that the database should never be observable in a state that is inconsistent (the process of course sees its own data that it is manipulating in a transient state).

Isolation is the property that each transaction execute and commit as if it were the only transaction active on the system. For example, if the money transfer transaction commits before the transaction that updates business rules,

There is a duality between Consistency and Isolation ­– as a practical matter, it is not possible to provide strong Consistency without strong Isolation and vice versa. YottaDB provides strong Consistency and Isolation. Strict Serializability implies strong Consistency and Isolation, and vice versa.

Durability is the property that once a transaction is committed, the database state change it represents is permanent, even if the computer crashes. YottaDB implements Durability by logging each transaction to a journal file, and “hardening” that journal file to non-volatile storage as part of the logic that commits a transaction.

Happy New Year and Welcome to the New YottaDB Web Site

2017 was an eventful year for YottaDB, LLC. We built a company from scratch, which turned out to be a more complicated endeavor than we had anticipated, but we now have all the trappings, including an office, a team, servers, a domain name, e-mail service, telephones and web site (for which I’d like to give a shout out to the folks at Zer0 to 5ive who made this new web site possible). Most importantly, we have software! YottaDB r1.00 and r1.10 demonstrated our ability to bring you the same high-quality software and support you have come to expect from the founding YottaDB team over the many years that we led GT.M projects.

Our expertise is in building and supporting an extremely robust, highly scalable and secure NoSQL database engine suitable for mission-critical applications. The database we built found success at the heart of several of the very largest real-time core-banking systems in the world, as well as a nation-scale electronic health record system.

Throughout 2018, we plan to build on that expertise and track record, and we are seeking to expand the code base beyond the narrow focus of GT.M to all four broad types of computing “tiers”:

  • In the periphery or edge
    • Devices, smart sensors, and actuators
    • Hubs / integration points and local system intelligence
  • In the data center or cloud
    • Transaction processing and decision support
    • Machine learning

While the tiers all have different database requirements, all can benefit from an industrial-strength database engine that provides persistence. Although the YottaDB code base can satisfy the requirements of all computing tiers, previous investment in GT.M only targeted two classes of applications in the third tier (real-time core-banking and electronic health records). We intend to take YottaDB to all tiers –  in effect, “YottaDB Everywhere.”

In the days and weeks ahead, you will see product releases from us as we work hard to make this vision a reality. Stay tuned, and please use the YottaDB site to tell us how we can better serve you.

Throughout, we remain committed to, “Rock Solid. Lightning Fast. Secure. Pick Any Three.”

I would like to take this opportunity to wish you a very Happy New Year in 2018. May you and yours find happiness in plenty, and success in your endeavors.

 

YottaDB r1.10 Released

YottaDB r1.10 released with support for Linux on ARM (e.g., Raspberry Pi), performance enhancements and more.

Visit r1.10 on GitLab for more details and to download YottaDB r1.10.

Tarball Hashes

sha256sum File
6f6f8eb36a307847e7becb6fd36b2281207352d33f921e16622c0933b7f648ec
yottadb_r110_linux_armv7l_pro.tgz
52e7160dce7fefb1ec91812012084e113c81e0af7e9c4baa9f560bc038ceecc3
yottadb_r110_linux_x8664_pro.tgz
3c4f5ec09f03de42c845c2d0800fb91249be92f8699a10793f230c4d341b7d02
yottadb_r110_rhel7_x8664_pro.tgz
2ee6d1c28b5bbb39dbfe2b52ad2fbef9327edcf462127ce31491d66ef06b7bf4
yottadb_r110_src.tgz