Posts

Building a On-Site Data Warehouse Using Microsoft Stack

Most IT specialists are aware of individual components within SQL Server and may use one or two of them regularly for data analytics or general operations. Outside of IT, others may be familiar with one of several analytics-oriented SQL Server add-ons such as those for Excel that are targeted at data analysis or data mining in general.

It is likely that far fewer staff, if any, have combined SQL Server components into a complete data analytics stack that enables the organization to build a high-performance data analytics solution using on-premise and external data sources that deliver meaningful analytics across the enterprise.

Fortunately, Microsoft provides all the tools and services necessary for a traditional, on-site data analytics system. This article outlines these components and their general usage. Of necessity, it cannot cover all the intricacies that may arise out of business-specific requirements.

Normalized vs. De-Normalized Databases

Relational databases use a normalized data organization optimized for rapid insert and update operations while minimizing data reads, which makes them ideal for OLTP processing. These types of databases are not suitable for data warehouses, which use an OLAP design that de-normalizes the data. This organization is optimized for the high-volume read performance required for analytical processing.

Other Reasons to Use a Separate Data Analytics Database

In addition to optimizing read performance, creating a separate OLAP database versus utilizing the OLTP corporate database has several advantages:

  • Analytical data queries requiring summarization of large data quantities will not bog down corporate database servers that collect related data items from disparate resources
  • Historical data can be cached rather than archived, which is typical on OLTP databases
  • You are free to consolidate data from several sources including off-premise databases, social media sites or a variety of document formats such as XML or Excel.
  • Cleansing and optimizing your data for analysis will be automated using your business-specific rules, queries and schedule.

The Microsoft Data Analytics Stack

SQL Server 2016 contains the entire suite of components necessary to build your first data warehouse. The three critical components are Integration Services, Analysis Services and Reporting Services.

SQL Server Integration Services (SSIS)

SSIS is SQL Server’s ETL component that connects to your data sources, migrates data, restructures it according to business-specific rules and populates the database. It manages acquiring data from multiple sources, cleaning this data plus performing type conversions and any other transformations required to streamline analysis. SQL Server Agent permits automatic scheduling of these processes so the database always contains the most relevant and timely data.

Use the particular business problems you are solving to guide ETL preparation and specification. A user-centric approach pares down and organizes the data being collected for the most efficient extraction and presentation.

SQL Server Analysis Services  (SSAS)

SSAS is critical to creating the most useful and highest-performing database queries. This component transforms normalized or relational data into multidimensional database “cubes” that are tuned to deliver rapid results to ad hoc queries. It does so through data indexing, data aggregation and enabling complex data calculations to be embedded at the cube level where processing is most efficient.

SQL Server Reporting Services (SSRS)

SSRS is used to make queries on an SQL Server database as well. It offers everything required to build detailed, complex reports with visualizations and facilities to manage and distribute reports securely. Users may view reports via the SSRS Web Portal, Power BI or a SharePoint server. End users are able to import reports directly to Excel or PowerPoint.

SQL Server Data Tools

This package works within Visual Studio (VS) and provides development tools for all the integration, analysis and reporting steps outlined previously. It is free and downloads a VS shell if you do not already have VS installed. SSDT lets you design and build your OLAP database and SQL Server content in much the same manner as you would for a VS application. It replaces the previous Business Intelligence Development Studio.

Conclusion

Microsoft’s SQL Server plus SSDT support development of a complete data analytics analysis system that includes an efficient OLAP database, optimized data schema and a full pipeline of data acquisition, pre-processing, analysis, delivery and visualization. Many organizations without a current data analytics solution may not have expertise in all the SQL Server components required, and those that do have typically only applied their skills toward OLTP type solutions.

Thus, many businesses employ an experienced partner for architecting a data analytics system that is scalable, extendable and highly secure. The most capable consultants are able to train and guide in-house staff through the entire development and maintenance lifecycle such that the enterprise becomes self-sufficient at further expanding and optimizing the system.

If you need help or advice on your data analytics system, let us know. Our team of data experts have worked with businesses large and small across all industries and have the right specialized technical skills to guide you towards a solution.

 

 

 

Cloud Data Warehousing with Azure SQL

If you are running an on-premise data analytics stack on Microsoft’s SQL Server, but running into maintenance, cost and scaling issues, you can consider moving your data system to a cloud-based database service such as Azure SQL Database. Especially for your first data analytics stack, Azure SQL Database provides low startup costs with the ability to easily expand as business grows.

Advantages of Azure SQL Database

There are several benefits to moving on-premise SQL Server infrastructure to Azure:

  • Physical acquisition, provisioning and maintenance of SQL Server deployments are a thing of the past. Furthermore, decreasing or increasing data infrastructure is instantaneous with SQL Database elastic pools.
  • Azure assists existing database migration to Azure SQL Database with wizard-based tools.
  • All stored and transmitted data are encrypted via client-side keys.
  • Microsoft accommodates third-party and open-source technologies, such as Python, Java, node.js, PHP and Python.
  • SQL developers feel right at home using SQLCMD or SQL Server Management Studio for development.

SQL Database Limitations

Although all SQL Server components, SSIS, SSAS and SSRS are available on Azure, there are still areas where the Azure version is not completely fleshed out. For instance, only a growing subset of T-SQL features are yet available such as cursors, transactions, triggers, all data types, all operators plus logical, arithmetic and string functions.

Additionally, many T-QSL statements in SQL Database do not support every option available in SQL Server 2016, such as CREATE/ALTER for databases, logins, tables, users and views. Collation of system objects, cross-database queries with three- or four-part names, database collector, diagrams and mail, some events and certain database characteristics that were managed manually in SQL Server but are automatic in SQL Database are also missing.

For a full list of deficiencies, see Azure SQL Database Transact-SQL differences

Additional Azure Capabilities

SSRS is actually replaced with a separate service, SQL Reporting, which incurs a separate charge for reports. It is not a general reporting service since it only works with SQL databases. It does offer a nearly identical development interface to traditional SSRS.

Azure Tables is a storage service targeted at non-relational database storage, which is a type preferred for data analysis processes. It stores up to 100TB of data via an Azure Storage account and supplies data in row form. Additional advantages include less cost than straight Azure storage and easy scaling.

Built on top of Hadoop, HDInsight offers unstructured data storage plus a number of tools, such as Sqoop, Pig and Hive for query processing. Your in-house SQL Server, Excel or SQL Database are all able to connect to this service.

Data Factory is Microsoft’s SaaS analogue to SSIS. It visually coordinates other services to transform raw, unstructured data via data flow pipelines into clean, transformed data ready for analysis engines such as HDInsight or Azure Machine Learning for predictive analytics.

In lieu of SQL Reporting, you can utilize Microsoft’s SaaS Power BI for report, dashboard and visualization creation. You can use this tool in conjunction with your on-premise SQL Server installation or stored spreadsheets too.

Steps to Migrating from SQL Server to SQL Database

SQL Database is, in theory, backward-compatible all the way to SQL Server 2005. In spite of this, the first step in migration is to test and fix any compatibility issues that may exist with SQL Database V12.

There are several methods to determine compatibility including the use of SQL Server Data Tools, the SqlPackage utility, SQL Server Management Studio’s Export Data Tier wizard and the Azure SQL Migration Wizard. SSDT, SSMS and SAMW can be used to fix any migration issues with your database also.

The next step is to create an Azure SQL Database logical server and migrate your existing data to it. Although other methods exist, the use of SQL Server transaction replication is the recommended solution since it minimizes live database downtime. Other solutions are to export/import BACPAC files when connection bandwidth is low or unreliable or use the SSMS database deploy wizard for smaller databases.

Running SQL Server in the Cloud Directly

There is nothing stopping you to begin or continue your SQL Server-based data analytics development and deployment without Azure SQL Database. Amazon AWS provides any level of SQL Server instantiation online with the advantages of computational, networking and storage elasticity on a pay-as-you-go basis. With a bit more lifting, you could do the same thing on Azure or AWS by utilizing their Virtual Machine services directly for your own SQL Server deployment.

Conclusion

Running data analytics in the public cloud brings all the usual benefits of cloud-based operation, the most important of which are elastic storage for big data crunching systems and high availability in-house or mobile across the enterprise.

Whether or not your business should consider a cloud-based data analytics deployment depends on several factors including TCO, data volume, bandwidth requirements, security and the need to scale operations up or down quickly.

A wise approach is to work with an experienced Optimus data analytics consultant to collate all factors and develop a full data architectural solution. Our experts can guide you towards the best solution for your needs.

 

 

 

Building Apps with Windows 10 Universal App Platform

With the release of Windows 10 this coming autumn, users and developers will enjoy a consolidation of device user interfaces and development platforms, respectively.

The promise for developers is that they can develop app business logic once for any Windows-based device such as a PC, phone, tablet, the Xbox One and a number of new devices such as the Hololens and Surface Hub. Eventually, the Universal App Platform will also encompass IoT devices.

Customers will be able to install a single app on all their devices and enjoy a more productive, unified mobile experience. Every device will have access to these single, all-platform apps from one online app repository.

Microsoft is creating this unified cross-device experience in response to customers’ desires for adaptive, seamless interaction on whatever device type is most convenient for accomplish a given task. With Windows 10 the ability to utilize whatever human interface is predominant on any device such as a touchscreen, keyboard and mouse, pen or game controller is taken care of automatically and intelligently.

What the Windows 10 Universal App Platform Means for Developers

Windows developers today must bridge the gaps between device types by writing separate apps for mobile devices, desktops or websites. With Windows 10, they write their app’s core logic once and the UAP, which contains a collection of “versioned contracts” for each device type, adapts the app to any Windows-based device.

Developers utilize extension SDKs to provide their apps with access to platform-specific functionality where necessary. These are turned on automatically at runtime to match the device upon which the app is running. Thus, recompilation is unnecessary to achieve cross-platform portability. Microsoft refers to these extensions as adaptive code.

The Adaptive User Experience

Adaptive UX works by sensing a platform’s human interface capabilities and the user’s interaction with the device to create a contextually appropriate experience. For instance, improvements to Microsoft’s ViewStateManager remove the need for separate UI definitions pertaining to screen size, though these can be included optionally if a developer prefers. User controls are adjusted at runtime based on how a customer is interacting with HIDs available on the machine. If a laptop has a touchscreen, for example, and it is being utilized, then controls targets are increased in size to accommodate taps instead of mouse clicks.

Natural Inputs

Windows 10 also adds the ability for developers to take advantage of other, less used, inputs such as gestures, inking, speech and user gaze. These are all handled automatically by Windows 10, which relieves developers from the need to parse such inputs themselves.

Cloud Services

In addition to Windows services already provided to developers, such as Windows Credential Locker, roaming data and Windows Notification Services, Windows 10 opens up additional cloud-related services including Cortana AI, OneDrive and Application Insights.

Backward Compatibility

Existing Windows apps will continue to be supported on the device types for which they were originally developed. Microsoft will offer tools and support for developers to easily migrate their existing apps that are based on previous versions of Windows to Windows 10.

Language and IDE Support

Windows 10 continues to support developers’ language preferences within Visual Studio plus the cloud services available through Azure. Everything you can do in Visual Studio now and more will exist after the new release. Furthermore, support will be increased for C# for delivering apps to iOS and Android devices including a full Android emulator. Developers will be able to take advantage of Xamarin and Unity within VS for cross-platform development also.

Jumpstarting Windows 10 Development

Developers who want to start working with Windows 10 can do so via Microsoft’s Windows Insider Program where they can access the latest pre-release builds.

Windows 10 holds much promise for both consumers and developers by sparking increased productivity and availability of Windows apps across the widest possible range of devices. App users will enjoy a reduction in user experience fragmentation and developers will spend far less time writing, testing, deploying and promoting their apps across all Windows-based platforms.

SharePoint in Knowledge Creation: SharePoint as a Basho

SECI-model SharePoint in Knowledge Creation: SharePoint as a Basho

While reading about knowledge management and knowledge creation, I came around a research/philosophy paper by Ikujiro Nonaka and Noboru Konno (1998)* about Ba (which I will explain later). I found really interesting how they connected a philosophical notion to a corporate problem such as knowledge management.

Kitaro Nishida’s Ba

While reading it, I was trying to come with examples for my own understanding about what a Ba was and to my surprise the answer was right in front of me: SharePoint.

So, what is a Ba? Ba roughly translates to place in English and it is a philosophical concept proposed by Japanese philosopher Kitaro Nishida.

Ba can be thought of as a shared space for emerging interactions. It can be a physical, mental or virtual place or even any combination of them.

When we have many Ba’s in the same environment then this environment becomes a Basho and that is exactly what I believe SharePoint is.

SharePoint as a Basho

To understand better how knowledge creation functions and how SharePoint, as a Basho, can be part of it, we have to take a look into Nonaka and Konno‘s (1998)* SECI model.

The SECI model (figure 1.) starts by differentiating between two types of knowledge: explicit and tacit. Explicit knowledge can be expressed in words and numbers and shared in the form of data, while tacit knowledge is more personal and as an attribute it is hard to formalize; tacit knowledge has two subsections the “know-how” section, which encompass personal skills or crafts, and the cognitive section which consists on ideals, beliefs, values, etc.

The secret to knowledge creation is to be able to turn the personal tacit knowledge into explicit and then back into tacit knowledge but for the whole organization. The model states that in order for this translation to occur, it has to go through Socialization, Externalization, Combination and Internalization and all of these steps are done through a different type of Ba (Socialization — Originating Ba, Externalization–Interacting Ba, Combination–Cyber Ba, and Internalization–Exercising Ba).

SharePoint has the capabilities to function as a Basho containing these different Ba’s. In the following table there is an explanation of what each sector of the model requires and the features SharePoint has that makes it a suitable Ba, or place, for this sector to emerge.

SECI Sector Sector Requirements SharePoint Features
Socialization Socialization involves the sharing of tacit knowledge between individuals. Social Networks where ideas can be shared, as well as other communication features such as Lync on line. 
Externalization The expression of tacit knowledge and its translation into comprehensible forms that can be understood by others. Wikis, Forums and the feature of collaborating on the same document with many different people around the organization.
Combination The conversion of explicit knowledge into more explicit sets of explicit knowledge. The fact that you can search key words, the use of Metadata and data maps to able to feed all the important and available information when people search for it.
Internalization Newly created explicit knowledge is converted into the organization’s tacit knowledge Achieved in SharePoint through the use of workflows, and how ideas can be turned into actions and processes that can help the productivity of the firm.

We can see how SharePoint can be seen as a Basho and therefore how in a philosophical and practical point of view it can help your business achieve a better and more efficient culture of knowledge creation.

Through a correct implementation of SharePoint, your company will be able to achieve more collaboration between its people and sectors, this will not only make processes more efficient but it will also help your company become more creative. SharePoint is a great service that can do many things and knowledge creation is only one of them.

If you have more questions about SharePoint and how implement it please don’t hesitate on contacting Optimus Information.

*Nonaka ,I. and N. Konno (1998). The concept of ‘BA’: Building a Foundation for Knowledge Creation. California Management Review, 40(3): 40-54.

 

SSRS: An Ideal Business Intelligence Reporting Tool

ssrs-150x150 SSRS: An Ideal Business Intelligence Reporting Tool

SSRS Reports are well integrated with the Microsoft stack.

Microsoft’s SQL Server Reporting Services (SSRS) is a flexible platform, incorporating a centrally managed reporting system and on-demand web-based and desktop applications. From report writing to deployment, the SSRS business intelligence reporting platform supports the entire report life-cycle.

Based on our experiences in SSRS report development, SSRS is an ideal tool for business intelligence reporting due to the following key factors:

Five Styles of Business Intelligence

The five styles of BI are:

  1. Reporting: Operational and financial reports that are mainly used by the junior to mid level management to facilitate their day-to-day work
  2. Scorecards and Dashboards: Graphical representation of data that is mainly used by the senior level executives to monitor the organization’s performance
  3. Ad-hoc query and analysis: Interactive reporting mainly used for scenario analysis
  4. OLAP analysis: Simple form of analysis that uses standard features such as page-by, pivot, sort, filter and drill up/down to flip through a series of report views.
  5. Alerting and Report Delivery: Proactive notification/reports that will be delivered directly to the users (via email, web, or XML files) whenever a critical  alert, alarm or system event occurs

SSRS is a single technology where all five styles can be used as plug-and-play modules. It allows companies to post reports as download items to a portal, email them to users, or allow users access via a web-based report server. Users can locate and run the reports as required using subscription, search and navigation features. Through personalized subscriptions, users can select the versioned format they prefer to personalize the reports.

Data and User Scalability

Companies collect and generate an enormous volume and scope of data over the years. The infinite number of systems in an organization, including supply chain management, sales force automation, ERP, websites and also CRM, contribute to this massive data influx.

SSRS and SQL Server Analysis Services (SSAS), when combined, can access very large databases, while comfortably interacting with the users. High volume environments can be supported by the scalability of its modularity and web-based design. As more people use the system, the performance is enhanced by a dynamic multi-level caching architecture, which makes each user’s experience faster and more interactive, while imposing the minimum load on database resources. One could serve thousands of web-based clients though creating a reporting server farm with multiple report servers accessing the same core reports.

Self Serve BI – Ad hoc Reporting

With the increase in user base and the amount of data, user self-service becomes critical. Users can create their own operational or financial reports and explore organizational data using an SSRS ad hoc reporting tool. Users without deep technical understanding can build reports through Report Builder’s user-friendly business query model.

Granular Analytic Capability

SSRS reports incorporate drill down and parameterized functionality, which has become standard in today’s BI market. Company’s performance can be easily measured with the help of SSRS’s advanced functionalities. By combining data planning and trending, SSRS can be used to provide valuable insights to future performances.

Security and administration

To protect reporting resources, SSRS implements a flexible and role-based security model. This model ensures that every employee of the organization is allowed access to data which their user role entitles them to. By using SSRS’s extensible interfaces, other security models can be easily integrated.

Microsoft Office integration

SSRS integrates with Microsoft Office products seamlessly. The end-user can view and manage reports completely from within a SharePoint document library. Additionally, SSRS can provide pixel perfect reporting by exporting reports into Excel and PDF. There is no requirement of any programming or customization for this integration. It also increases the productivity of the users due to it’ s familiar desktop environment.

Branding and Presentation

SSRS enables company branding with picture perfect formatting. Adding the company logo and client information with reports documents is a simple process due to the ease of integration with Microsoft Office and Adobe PDF.

Automated Maintainability

Through SSRS, reports can be updated automatically, reflecting continual changes to fundamental business definitions,  structures and database. All users have access to a single version of truth through the centrally defined and managed business rules provided by SSRS.

Cost Effective

SSRS is already available with an Enterprise or Standard edition of Microsoft SQL Server. If a company already has these running, it does not need spend extra money for purchasing SSRS. This is one of the key benefits of the Microsoft Business Intelligence stack which provide a complete set of tools for end-to-end BI solutions.

SSRS has the lowest licensing cost among the leading business intelligence vendors. This provides a cost effective benefit for companies that without any SQL Server, but are inclined to buy Microsoft’s BI stack. Additionally, the server-based license model of SQL Server is definitely more cost-effective compared to per-user-based licenses.

Open APIs

Traditional and interactive reports can be created, managed, and delivered through a complete, server-based platform offered by Reporting Services. Additionally, with the help of its modular design and extensive application programming interfaces (APIs), enterprises, data providers and software developers can integrate reporting services with legacy systems or third-party applications. Other environments such as Visual Studio .Net development can be easily integrated with SSRS.

To learn more about SSRS report development, connect with us. We are happy to provide free consultations.