This article outlines common data warehousing and business intelligence reference architectures. There are many variations to these that I will not cover, but I hope this will provide you with a general understanding of the different architectures and their pros and cons.

Data Vault Architecture

The Data Vault Architecture is a methodology that makes it easy to store structured data. Performance to load a data vault can reach near real time loads, as there are mostly inserts rather than updates or deletes. This architecture is largely used where auditing of the data is needed – what source systems changed what data. This is a flexible architecture that allows the addition of additional data sources to be integrated easily. To accomplish the speed of inserts and auditing of data, a large number of tables are needed when compared to a dimensional model. This can add additional complexity if trying to report from or get data out of a data vault. Therefore, the data vault is often between source systems and a data warehouse (dimensional model).

Figure 1: Data Vault Reference Architecture

Table 1: Data Vault Pros and Cons

Data Warehouse Architecture

The Data Warehouse or Dimensional Model follows the Kimball Methodology, which is a denormalized architecture that is designed for reporting. The denormalized architecture reduces the number of tables and complex table joins to improve performance on getting data out. A staging environment which could be a data vault, or a data lake is used to land data without transformations from each of the source systems. The data integration and interoperability layer handles the complex transformation logic to shape the data into a dimensional model.

Figure 2: Data Warehouse Reference Architecture

Table 2: Data Warehouse Pros and Cons

Modern Data Warehouse Architecture

With cloud offerings, such as those offered by Microsoft Azure, Amazon Web Services (AWS) or Google Cloud Platform (GCP), organizations have even more options to implement their data strategies. One newer data option is an IaaS (Infrastructure as a Service) offering called a Data Lake. Data Lakes allow you to store data, both structured and unstructured data, in a single location. This centralized data store can be a good option for organizations that have both structured and unstructured data that they plan to use, now or in the future. There is minimal design or architecture needed up front to plan how you will store the data. Think of a file system where you store your text documents, images, videos, PDFs, etc. and how you create directories and folders and then store files within. You will need to think through how you will setup this structure before loading. Once you have designed the structure and made a few other technical decisions, you can then load data. The data is loaded incrementally regularly.

With more companies looking at and implementing a data science solution, a data lake can be a key place for your business analytic or data scientist to access data from the entire organization. One enormous struggle faced by data scientists is accessing the data from multiple systems and bring that data into a centralized data store. With the data already loaded to the data lake, data scientists can focus more on building value from the data rather than gathering the data.

A data lake is not a data store that non-technical users would have access to, as there is still an effort in transforming the data in the data lake into a usable format.

Figure 3: Modern Data Warehouse Reference Architecture

Table 3: Modern Data Warehouse Pros and Cons

Modern Business Intelligence Architecture

The Modern Business Intelligence (BI) Architecture came about with the release of reporting tools with functionality to connect and report from multiple sources. Take Power BI as an example. Power BI is an growing reporting tool with functionality being added regularly that allows one to build interactive reports and dashboards from data sourced from multiple different systems. This approach can be a brilliant solution for smaller and less complex data to drive business decisions.

Power BI and other related tools can connect to different sources and transform that data into a reportable shape. Contrast this with other solutions that have a layer of ETL or Extract, Transform, and Load that does this transformation. This ETL layer does a lot of heavy lifting to transform the data into a usable shape and loads that data into a data model that makes reporting easier. The ETL processes can take a very long time to run due to complex transformations. By having a layer of ETL built into your reports, the time needed to run those reports and time to insights can be far longer reducing the value. If a business user must wait 15 minutes or longer for the insights to return, it is tough to drive decisions. Higher data volumes or more complex transformations will dampen the benefits of Modern BI Architecture.

A Modern BI Architecture can also be a great solution for building out POCs (Proof of Concept) against multiple systems to relay the ideas and possibilities to business users to get feedback. While this may not be the ultimate solution, it could require additional work based on the complexity and performance. I have found this approach to be less risky, less expense in the long run, and allows for quicker value to be added.

Figure 4: Modern BI Reference Architecture

Table 4: Modern BI Pros and Cons

As mentioned, there are variations in these reference architectures and many considerations that should be considered when selecting the best path for your organization. Reach out with questions or if I can be of assistance in helping you select a path forward for your organization.

The post Common Data Warehouse and Business Intelligence Reference Architectures appeared first on DawaBI.

Total cost of the laptop was $1,780 – I paid a $100 deposit when I submitted the order and the balance when it shipped.

Figure 1: Framework laptop configuration and pricing summary

Given the supply chain issues we had during 2021, Framework shifted to a batching process for their orders. When I ordered, it was monthly.  When you order on their website, they will show different configurations and what batches they are in.

Here is the timeline for my order. The process, start to finish, was smooth and issue free.

Configuration

There were three CPU options to choose from and I selected the Intel® Core i7-1165G7 because of the performance to price ratings. The next higher CPU was the ® Core i7-1185G7 which had marginal improvements in performance but was $400 more.

The storage I selected was 1TB – WD_BLACK SN850 NVMe. 1 GB as most of what I do these days is in the cloud and my storage solutions of Microsoft OneDrive and pCloud have files on demand which means you have access to the files but they are stored in the cloud where they don’t take up space on your drive. Also, most of my technical work consists of cloud-based products such as data lakes and databases where I no longer have to have copies on my laptop. So higher capacity wasn’t as important.

However, I do work with visualization tools such as Power BI and messaging apps such as Slack, Teams, and Chat, which consume a lot of memory and wanted to have enough capacity to run these. I therefore opted to get 64 GB (2 x 32GB) DDR4-3200 RAM.

Expansion Cards

One of the unique aspects of the Framework laptops is the expansion cards. These are removable cards with different cable interfaces, such as for HDMI, USB A, USB C, and others. I selected 1 HDMI, 1 USB-A, and 4 USB-C. While working at home, which is 98% of my work, I have the 4 USB-C cards in but wanted HDMI, and USB-A for when I travel or am out doing presentations.

Unboxing

The packaging was simple, with the laptop arriving in a box and expansion cards in a padded package.

Figure 2: Framework packaging

Figure 3: Framework open packaging

Figure 4: Framework packaging showing the Framework tool, which is the only tool needed to work on the laptop.

The Build

The Build was rather straightforward and simple. The only issue I had was with the network adapter and connecting wires which were rather small. My old eyes and hands took a while to connect. It was helpful to have my sons helping with much better eyes and smaller fingers.

Framework’s goal is to make the laptop upgrades or modifications assessable; they have a simple screwdriver tool that is used to perform all needed modifications. It has a fixed slotted screwdriver on one end and a removable T5 star bit.

Figure 5: Removing the screws with the Framework Screwdriver

Figure 6: Opened Framework laptop

For the operating system, I installed Windows 11 which was recently released. About one month in, I had an issue where the laptop crashed and after restarting it; it was incredibly slow. I tried several things but couldn’t get it back to normal. I therefore had to reinstall the operating system and eventually got it back to normal working condition. There were several non-windows software and firmware updates that I installed after reinstalling the operating system, which may have caused the crash.

Monitor

With this upgrade, I also wanted a wide screen monitor as I felt this would provide me a more efficient workspace. After much research, I went with the Samsung Odyssey g9 monitor. This monitor is 49 inches and had a 1000R curve. The width I felt was wide enough to be my main working monitor that provided plenty of screen real estate for development tools. The 1000r radius I felt was best for me and the distance I would work from the screen. It has a tighter curve than many other monitors I looked at that were 1800R. I felt the 1800R curve flattened out on the edges.

Docking Station

Having a good docking station has served me well over the years and wanted something that would connect the new monitor, besides my existing monitor and other peripherals as well. Another new release, I selected the Anker Apex 12-in-1 Thunderbolt 4 Dock with Max 90W Charging for Laptop, 20W Power Delivery, Single 8K, Triple 4K Display, 2 USB-A 3.1 Gen 2, 2 USB 2.0, Ethernet, Audio, SD 4.0, for Thunderbolt 4 Laptops. I did debate on getting an eGPU with a higher end graphics card, but opted not to for now. I am not a gamer and the refresh rate (120 Hz) I receive through the docking station is more than sufficient for now. If later I or my two boys’ game, I’ll look at an eGPU at that point if needed.

Overall Setup

Figure 7: Final setup with the Framework laptop and multiple monitors

The overall setup has been working well. I’m using the 49 inch monitor as my main screen. I have my old 28 inch 4k monitor mounted above and my laptop to the right. The 28 inch monitor I used as a home for messing apps such as slack, google chat and teams along with a reference/research browser. I use my laptop monitor for screen sharing (need to figure out another method though) and email.

Overall, I’m thrilled with the setup.

The post Framework Laptop and Setup appeared first on DawaBI.

The post PASS SQL Saturday Events: December 2014 appeared first on DawaBI.

The post PASS SQL Saturday Events: November 2014 appeared first on DawaBI.

The post PASS SQL Saturday Events: October 2014 appeared first on DawaBI.

Join SQL Server professionals from around the world for free online technical training and networking year round. PASS Virtual Chapters (VC) are interest-based forums that unite the SQL Server community through live webcasts with top SQL Server experts, online forums and resources including an archive of past presentations, and special training events.

The post PASS Virtual Chapter Events: September & October 2014 appeared first on DawaBI.

The post PASS Virtual Chapter Events: August 2014 appeared first on DawaBI.

The post PASS SQL Saturday Events: September 2014 appeared first on DawaBI.

The post PASS SQL Saturday Events: August 2014 appeared first on DawaBI.

Here are the speakers and topics:

There is still time to register and attend. You don’t want to miss it!

The post Will you be attending Mile High Tech Con? appeared first on DawaBI.