A Data Warehouse Design for Leading Retail Company

If you are a small, medium or large size corporation looking to set up any scale of business intelligence I would personally vouch for Teo Lachev and Prologika as the place to go. There is no other place to find a more comprehensive and well-rounded BI consultant.From developing complex dynamic data driven security, performing server optimizations, to leading data warehouse design and many other things, Teo has an encyclopedic and applied knowledge of the complete Business Intelligence Suite and best practices to go with it!

Burzin Daruwalla, BI Developer

Teo has outstanding talent Data warehousing, Business Intelligence, Information systems, and understanding of business value.

Guido Arroyo, System Analyst

inComm, a leading marketer and distributor of stored-value gift and prepaid products, needed an accurate and timely reporting system for internal users and external partners. Prologika delivered a data warehouse, cube, and framework to enable both internal reporting for data analysts and external reporting to inComm customers.

Business Needs

Realizing the value of data analytics, inComm envisioned a BI system but were unsure how to start. They sought external consulting help but were disappointed with the results. Their internal IT department lacked the skills to implement organizational BI solutions. Historically, the entire IT department was focused on producing paginated Reporting Services reports. This was a time consuming process that led to maintenance issues because business metrics were redefined in reports.

Besides internal reporting, inComm wanted to provide reporting capabilities to their external partners. They envisioned an external-facing self-managed portal where an external partner would provision the users who needed access to reports. Then, the portal would authorize the user to see only their data.

Solution

As a first step, Prologika evaluated the existing data warehouse design and extended it to support complex business requirements. Following the industry best practices, Prologika implemented a star schema design. Prologika designed the supporting data structures to authenticate users and enforce data-level security to their data.

Prologika implemented an enterprise Multidimensional cube layered on the top of the data warehouse which had 1.5TB of data. The cube provided excellent performance and promoted a single version of the truth. Then Prologika architected Active Directory and Kerberos-based security for external reporting.

Prologika mentored the inComm staff to get up to speed with date warehousing and cube development.

“Another important part of Teo’s skillset that goes unnoticed are his mentoring skills. Once Teo’s contract was over we did not feel lost or overwhelmed, in fact our entire team was empowered by him with all the knowledge he imparted onto us throughout his tenure at inComm.”

Benefits

Within the help of Prologika, the customer went from zero to modern BI in the course of a few months. The BI system that Prologika implemented delivered enormous value to the client.

Internal insights

With a few clicks, business analysts can create interactive reports to analyze their operations from many different angles. The cube centralizes business logic and metrics in the semantic model so that they are not defined and redefined in the database or in reports. The cube includes over 100 metrics to support comprehensive descriptive analytics, such as averages, variances, growth, ratios, and many more.

From an end-user perspective, data analysis is a matter of dragging and dropping attributes on the report to slice and dice data. Reports tools, such as Excel, auto-generate report queries, and the server takes care of aggregating data, such as to summarize sales at the year level. With a few clicks, users can gain insights across multiple subject areas, such as claims, enrollment, and customer care.

Customer satisfaction

External partners can have accurate reporting without relying on inComm to provide them with data.They have access to analytical and transactional report that help them understand better their business and audit revenue. For their convince, inComm provided ready-to-run paginated reports but partners can create interactive reports by connecting directly to the cube.

Knowledge transfer

The inComm internal developers acquired the necessary skills to extend and maintain the solution.

 

Insurance Company Transforms Organizational Data Into Strategic Asset

InSight transforms the organization’s data into a key strategic business asset, empowering employees like never before. It currently includes over 300 performance measures that can be analyzed across various dimensions, enabling our business to calibrate and share insights with rich data storytelling. We are extremely excited for the actionable intelligence and foresight this new tool will bring to our organization!

Andrew Teeple, Director Supply Chain Analytics

eSecuritel, a Brightstar company, needed a BI solution to help them understand and act upon their business.Prologika implemented an innovative self-service BI solution. InSight not only has self-service BI capabilities but provides the organization with a “single version of truth”.

Business Needs

Historically, reporting and analytics at eSecuritel used to be an exhaustive process, impacted by constant changes in the transactional data that made it challenging to report measures consistently across the organization.

When eSecuritel approached Prologika, they gave us a wish list of metrics that they wanted to track across the enterprise. This wish list was compiled by their data analysts but even management was skeptical that the “dream” can materialize one day. The complexity of the source data and the lack of in-house BI expertise influenced the perception that actionable BI remains an unattainable dream.

Solution

InSight is powered by SQL Server Enterprise Edition. The system extracts the transactional data form the source systems and then loads the data into an Operational Data Store (ODS). ODS keeps the raw data in its original form but tracks historical changes.

data

Another set of ETL processes loads the data into a data warehouse database. When doing so, ETL cleans the data and transforms it into a star schema format that is suitable for reporting. As typical with our organizational BI solutions, Prologika implemented a semantic data model powered by SQL Server Analysis Services. Read our “WHY SEMANTIC LAYER?” newsletter to learn more about the advantages of having a data model.

Benefits

The semantic data model brought several important benefits to business users.

  • Great performance – BISM is optimized to aggregate data very fast. Queries involving regular measures and simple calculations should be answered within seconds even, when aggregating millions of rows!
  • Implicit relationships – The semantic layer includes relationships between entities. As a result, end users don’t need to join tables explicitly because the relationships have already been established at design time. For example, a business user doesn’t need to know how to join the Date table to the Claims table or write SQL. You simply add the fields you need on the report, and then the model knows how to relate them!
  • Interactive data analysis – From an end-user perspective, data analysis is a matter of dragging and dropping attributes on the report to slice and dice data. Power BI auto-generates report queries, and the server takes care of aggregating data, such as to summarize sales at the year level.
  • Data security – BISM models apply data security based on the user’s identity, such as to allow a business analyst to access only the subject areas he’s authorized to access.

Single version of the truth

InSight centralizes business logic and metrics in the semantic model so that they are not defined and redefined in the database or in reports. The model includes over 300 metrics to support comprehensive descriptive analytics, such as averages, variances, growth, ratios, and many more.

Interactive data analysis

From an end-user perspective, data analysis is a matter of dragging and dropping attributes on the report to slice and dice data. Reports tools, such as Power BI and Excel, auto-generate report queries, and the server takes care of aggregating data, such as to summarize sales at the year level. With a few clicks, users can gain insights across multiple subject areas, such as claims, enrollment, and customer care.

The semantic layer includes relationships between entities. As a result, end users don’t need to join tables explicitly because the relationships have already been established at design time. For example, a business user doesn’t need to know how to join the Date table to the Claims table or write SQL. You simply add the fields you need on the report, and then the model knows how to relate them!

Instant self-service analytics

Data analysists don’t have to rely on IT anymore to provide the needed data to them. And they don’t have to create disjointed Excel “spreadmarts”. Now they just connect their favorite report tool to the data model and select which measures and dimensions they need on the report. Moreover, the data model is optimized to aggregate data very fast. Queries involving regular measures and simple calculations should be answered within seconds even, when aggregating millions of rows!

Records Management Company Gets Real-time BI, Boosts Sales with Mobile Solution

Ultimately, the new Microsoft mobile BI solution leads to more revenue for Recall and gives us deeper customer insight, helping us stay ahead of our competitors.

Todd Pinniger, Senior Manager of Architecture and Product Development

Recall, a records-management firm, needed faster access to key performance indicators and more intuitive business intelligence (BI) tools. The company consolidated four data centers into a Microsoft SQL Server 2012 data warehouse. The solution’s performance enhancements speed employee access to more detailed data. By consolidating into a data warehouse, the company saved $1 million in hardware and licensing costs.

Business Needs

Recall stores, protects, and streamlines vital information for banks, law firms, government organizations, healthcare providers, financial institutions, and other customers.

More than 200 Recall salespeople and account managers travel frequently, meeting with current and potential customers all over the world. This mobile sales force needed to have access to updated BI data, such as current key performance indicators (KPIs) and customer billing information. “The salespeople need to know detailed customer information prior to a meeting,” says Carlos Rodrigues, Business Intelligence Architect at Recall. “With that kind of information at their disposal, it’s easier to be successful in terms of sales.”

However, viewing this information was challenging because Recall had a limited ability to analyze customer data. “We simply didn’t have BI capabilities at all, let alone mobile BI capabilities,” says Todd Pinniger, Senior Manager of Architecture and Product Development at Recall. “If salespeople wanted updated customer data, they had to send email attachments to their laptops or iPads before going on the road.”

Also, because most of those employees do not have experience creating analytical reports, they relied on IT to build custom reports on specific customers. “The process wasn’t efficient, and it didn’t provide the real-time information they needed prior to meeting with a customer,” states Pinniger.In late August 2011, Recall began searching for a new self-service mobile BI solution.

Solution

After considering new BI solutions from companies including Panorama, CORDA Technologies, QlikTech, Tableau, and SAP, Recall chose to implement a new solution based on Microsoft SQL Server 2012 Enterprise software. “We have used Microsoft technology for many years, and we were eager to deploy this new solution because of our great experiences with Microsoft so far,” says Pinniger.

Prologika delivered a new solution, which wentlive in February 2012, and includes Microsoft SQL Server 2012 Analysis Services and SQL Server 2012 Integration Services, in addition to Microsoft SharePoint Server 2010 and SQL Server 2012 Reporting Services. The new data warehouse contains about 7 terabytes of data. Recall is also using the SQL Server 2012 xVelocity memory-optimized columnstore index feature to speed the processing and querying of large data sets.

To facilitate self-service BI, Prologika implemented PerformancePoint Services in Microsoft SharePoint Server 2010, a feature that provides easy-to-use tools for report building, as well as scorecards and dashboards used for monitoring KPIs.

For mobile self-service BI, Prologika created a URL that links to the new SharePoint site, so several hundred traveling sales professionals now have instant access to detailed customer information from their laptops, tablets, and smartphones. Recall is also taking advantage of Microsoft SQL Server 2012 Power View, a new interactive data-exploration and data-visualization feature in SQL Server Reporting Services that works within SharePoint Server 2010.

Benefits

Using its new Microsoft BI solution, Recall salespeople and account managers have deeper insight into the company’s customers, which helps increase sales. Additionally, Recall has avoided having to develop its own costly mobile BI software.

Gives Mobile Sales Force Easy-to-Use BI Tools

With the new mobile BI solution, Recall salespeople and account managers have a simplified way to get fast, real-time access to critical KPIs and other customer information. “Using PerformancePoint Services in SharePoint Server 2010, our mobile sales teams have an easy self-service BI tool they can access from their mobile devices, no matter where they are,” says Pinniger. “And they don’t have to have special training in analysis and reporting, so they can create reports without IT intervention.”

Mobile users can now view customer details before or during customer meetings. “Previously, salespeople would spend a lot of time generating the latest BI data before a customer meeting,” says Pinniger. “They now have instant access to dashboards and KPI reports on their iPads, so they can view a customer’s current revenue data during a meeting.”

Improves Customer Relationships and Increases Sales

Because they have better visibility into customer information, Recall sales professionals have improved relationships with their key customers. “With a deeper understanding of our customers’ business and needs, our salespeople can give more knowledgeable, thorough input to customers during meetings, which helps foster tighter relationships,” says Pinniger.

It also leads to better sales opportunities. “Because they have better insight, our sales managers can show a potential customer how another branch of that customer’s business is using Recall solutions in another region or country,” Pinniger says. “The sales managers can then more easily sell the solution to the potential customer. Ultimately, the new Microsoft mobile BI solution leads to more revenue for Recall and gives us deeper customer insight, helping us stay ahead of our competitors.”

Avoids Development Costs

Recall also avoided development costs because it did not need to create a separate mobile solution. “We already had the SQL Server 2012 BI solution in place internally, and we use the exact same solution for our mobile users,” states Rodrigues. “We initially looked at purchasing additional software to integrate the mobile functionality, but we saved a lot of time and money by not having to duplicate our development efforts. This is a great solution for our mobile salespeople, and we are very excited about expanding it to other mobile users in the future.”

 

An Independent Software Vendor Embraces the Cloud for Community-driven Data Analytics

VIA Consulting, an Independent Software Vendor (ISV), implemented an online platform (SynergyScape) to help communities collaborate and learn together. Prologika helped VIA to scale the platform on Microsoft Azure, and to implement cloud-hosted data analytics.

Business Needs

For VIA, deploying their SynergyScape online platform to the cloud was a no-brainer. The alternative was to make a significant investment in creating and maintaining an on-premises infrastructure. VIA selected Microsoft Azure because of its breath of features and flexible pricing. VIA distributed its platform on multiple Azure virtual machines.

However, as VIA quickly realized, outsourcing the infrastructure to Microsoft Azure was one thing but scaling to thousands of users was quite another.It’s easy to spin off a new Azure virtual machine, but expert help is required to make sure the performance of the cloud-hosted infrastructure meet service level agreements. VIA engaged Prologika to assess their platform and to ensure that can meet the expected OLTP and OLAP data volumes.

Solution

As a first step, Prologika assessed the existing SQL Server deployment and presented its findings and recommendations to the VIA management. Using Visual Studio Online, Prologika conducted load tests to determine the system capacity. Prologika made changes to the OLTP database to make it more scalable. By taking advantage of the innovative Windows Server storage pools, Prologika increased the system capacity by pooling multiple data disks and distributing the OLTP load across multiple data drives.To make the system highly-available, as required by Microsoft to be covered by the Azure service level agreement, Prologika implemented database mirroring so that the primary database fails over to a mirrored server.

With help from Prologika, VIA implemented a data warehouse database on hosted it on another Azure Virtual Machine. Prologika optimized the data warehouse database for analytical loads. As a next step, a hybrid architecture consisting of Analysis Services Tabular model and Power BI was implemented, to empower VIA’s users with interactive data insights.

Benefits

Prologika helped VIA to scale its OLTP and OLAP infrastructure to thousands of users.

Scaling OLTP databases

Because the SynergyScape platform is community driven, sudden capacity increases are common. For example, a celebrity might host a TV interview and encourage his followers to sign it to the SynergyScape platform. This might result in tens to hundreds concurrent OLTP requests. Powered by SQL Server, the VIA’s OLTP database can scale to accommodate such spikes.

Customer satisfaction

Data analytics is the most valued feature of the SynergyScape platform. Previously, VIA used another vendor’s tool for delivering standard reports. However, the reports had performance issues and were lacking in interactive features. By switching to Power BI, VIA delivers interactive and insightful reports to its customers.

With a few clicks, users can create interactive reports to analyze their community data and slice and dice it in different ways. The in-memory model centralizes business logic and metrics and promotes interactive analytics. Data-level model security ensures that users can access only their data.

Prologika Optimizes a Large 2.5TB Cube

This organization had a large 2.5 TB cube to analyze website traffic. Prologika optimized the cube and reduced the report query execution times from minutes to seconds.

Business Needs

This organization has implemented a large multidimensional cube to analyze website traffic, search results, and ad campaigns. This cube a strategic role in the company. Senior management based their decisions on the results from the cube.

The cube had grown to 2.5 TB, with some 25 billion rows in the fact table. The customer was complaining about long-running queries that would take minutes to execute. They had doubts if Analysis Services can scale.

Solution

Referred to by the Microsoft Analysis Services team, Prologika assessed the cube architecture with a focus on performance and provided remediation steps to optimize the cube performance.

We discovered issues with the way the MDX queries were written. Specifically, the query WHERE clause used arbitrary-shaped sets. They caused performance issue causing the Analysis Services server to scan all partitions. The cube was partitioned by hour and data was kept for 40 days, resulting in 960 partitions. Although a query might need much less data, the server would scan all the 960 partitions, resulting in enormous amount of data being read and processed. As it turns out, the WHERE clause is not optimized to project the filter on partitions. The solution was to replace the WHERE clause with SUBSELECT to bring the query execution time from minutes to seconds.

Benefits

Thanks to the findings and remediation steps from Prologika, the query execution times were greatly reduced and the company’s faith in Microsoft organizational BI was restored.