SQL Server’s AlwaysOn technology (available since SQL Server 2012) provides high-availability and disaster-recovery database functionality which largely supplants mirroring and log-shipping – in fact, mirroring is now deprecated. Exactly what functionality is available and how robust it is, varies by release (2012+), edition (Standard versus Enterprise) and the physical infrastructure devoted to it. AlwaysOn is fairly easy to set up (though it requires cooperation from both Windows Server and networking admins) and, relative to the required effort, provides exceptional durability for SQL Server databases. AlwaysOn is not a complete OOTB durability solution and has significant gaps – e.g. it does not maintain SQL Server logins and roles synchronized across multiple servers – but it is an excellent start for the needs it caters to.
This post assumes the reader has at least a basic familiarity with SQL Server backups, as well…
The X12 HIPAA transaction set is used across the healthcare industry to transmit claim, enrollment and payment information. Given the importance and ubiquity of these EDI files, you might assume that translating them from ANSI to a relational database format would be well-supported with a range of options.
In practice, a task as common as parsing a claim or encounter and storing it in a database can quickly escalate into a significant problem.
One solution we’ve seen involves archiving a snapshot of the EDI file using filestream storage. This can satisfy some retention requirements, but provides little in terms of fine-grained tracking or analytic capabilities.
A more complete approach is to parse the X12 file into its discrete elements and store them in a relational database. The ideal solution captures the full extent of the EDI transactions while also applying a reasonable leveling of flattening to keep in the number of table joins under control.
835 and 837 EDI transactions have transformed the adjudication cycle for providers and health plans over the last two decades, but challenges remain in reconciling payments with claims. Recently, we’ve broken down the requirements for SNIP 3 claim balancing. Today we’ll focus on the 835 Claim Payment/Remittance Advice. Health plans submit 835s to providers (or their intermediaries) to explain which claims are being paid, and any reductions to the submitted amount and the reasoning for the adjustment. This is an important function – a significant pain point experienced by providers is the reconciliation of their income against claims submitted.
Before this valuable information can be loaded in practice management software, the 835 should pass validation checks. Common issues affecting 835s are balancing errors between the header and detail payment amounts. Imbalanced 835s lower the quality of reporting and can lead to billing…
For reliability, accuracy and performance, both AI and machine learning heavily rely on large sets. Because the larger the pool of data, the better you can train the models. That’s why it’s critical for big data platforms to efficiently work with different data streams and systems, regardless of the structure of the data (or lack thereof), data velocity or volume.
However, that’s easier said than done.
Today every big data platform faces these systemic challenges:
Compute / Storage Overlap: Traditionally, compute and storage were never delineated. As data volumes grew, you had to invest in compute as well as storage.
Non-Uniform Access of Data: Over the years, too much dependency on business operations and applications have led companies to acquire, ingest and store data in different physical systems like file systems, databases and data warehouses (e.g. SQL Server or Oracle), big data systems (e.g….
Efficient data management keeps goods flowing smoothly in Denmark. Danske Fragtmaend, the country’s largest national transport and distribution firm, has been moving freight for more than a century. Today, Danske Fragtmaend delivers more than 40,000 consignments each day throughout Denmark, and businesses from small mom-and-pop operations to factories rely on its services.
The firm handles logistics in a central location, where 200 dispatchers keep an eye on the movement of thousands of trucks and their cargo. Both drivers and dispatchers need the latest information to operate efficiently, so they rely on a data platform based on SQL Server 2016. The storage system includes 160 terabytes of flash memory for fast I/O and high uptimes. Throughout the day, drivers continually scan transactions with PDAs and send shipping information including GPS coordinates to the data platform. Fast access to information is essential. Ulf…
1: Build data-driven apps that learn and adapt
Applications show intelligence when they can spot trends, react to events, predict outcomes or recommend choices—often leading to richer customer experiences, improved business process, or addressing issues before they arise. The three key ingredients to creating an intelligent app are:
Ingest data in real time
Query across historical and real-time data
Analyze patterns and make predictions with machine learning
With Azure, you can make your applications intelligent by establishing feedback loops, and applying big data and machine learning techniques to classify, predict, or otherwise analyze explicit and implicit signals. Today, apps for consumers and enterprises can deliver greater customer or business benefit by learning from user behavior and other signals.
Pier 1 Imports launched a mobile-friendly pier1.com, making shopping online easier. It enabled the selection of delivery options like direct shipment, picking up products in the local store,…
Recently, I was met with some friction by the IT department at a client where, they asserted, that a decision had been made years ago to ban Entity Framework. Like many enterprise environments, this client was understandably concerned with the potential pitfalls of embracing Entity Framework. That meant that my job was to convince them otherwise – not to discount their apprehension, but quite the contrary – to demonstrate how EF can be leveraged for its advantages, and avoided for its shortcomings.
Entity Framework (EF) is a broad framework with many optional parts. There are several aspects of EF that provide great benefit, while others are a source of great consternation – particularly from the perspective of the database purist. As the cliché goes, “with great power comes great responsibility,” and so this blog post explores different aspects of EF, and…
Securing customer data while maintaining the highest levels of privacy have always been top priorities for Microsoft and the SQL organization. As a result, SQL Server, which also powers Azure SQL Database and Azure SQL Data Warehouse, continues to be one of the most secure Relational Database Management Systems (RDBMS) on the market.
At the RSA Conference last year, Microsoft talked about their commitment to security and privacy. Microsoft wants to share a few examples of industry-leading security features they shipped since then and update you on their plans to deliver the highest levels of security across the SQL Database product lineup.
Announcing the April general availability of Azure SQL Database Threat Detection for proactive monitoring and alerting of suspicious database activities and potential vulnerabilities.
Using machine learning, SQL Database Threat Detection continuously monitors and profiles application behavior, and detects suspicious database activities…
TriZetto’s QNXT is a widely adopted platform for claim processing and membership administration. QNXT relies on the Microsoft stack, particularly BizTalk, .Net and SQL Server, to process and store EDI messages.
These technologies give developers many tools for customizing and tracking HIPAA transactions, but the complexity of implementing business rules and lifecycle reporting on EDI data are constant concerns for health plan payers.
Tallan’s T-Connect EDI Management Platform is an optimized integration solution founded on three core design principles:
An accessible API. One of the most common challenges our partners face is implementing business logic on EDI. T-Connect loads all HIPAA transactions into a fully compliant hierarchical data structure that can be manipulated with familiar tools such as Visual Studio and .Net.
Full database persistence. Going from EDI to a relational database is a frequent business need, but capturing the full set of fields present in an 837 alone represents…
In my previous post, I introduced the concept of temporal data, and explained at a high level how SQL Server 2016 implements temporal tables. This post dives into the details of exactly how you create and query temporal tables.
Let’s start with an ordinary table, and convert it into a temporal table. So I’ll create the Employee table, and load it up with some data.
To convert this into a temporal table, first I’ll add the two period columns and then I’ll enable temporal and set dbo.EmployeeHistory as the name of the history table.
Note that because we’re converting an existing table, this must be done in two separate ALTER TABLE statements. For a new temporal table, you can create it and enable it with a single CREATE TABLE statement. Also, and because this is an existing table with existing data, it’s necessary…