Home of the Data Gods

Become a Data Engineer

Tue, 03 Sep 2024 16:39:14 GMT

Welcome to the World of Data Engineering with Microsoft Fabric

Welcome to the world of data engineering, where the landscape is rich with opportunities to harness the power of data. Whether you're a small business or an enterprise, having a robust data engineering solution is essential for unlocking insights and driving growth. Let's dive into a comprehensive data engineering solution with Microsoft Fabric, which includes a data warehouse, a Lakehouse, a pipeline, a notebook, and a shortcut. Each component is crucial in the data engineering process, contributing to a seamless and efficient workflow.

Overview

In this blog post, we will explore the comprehensive data engineering solutions offered by Microsoft Fabric. We’ll delve into the following key components:

Data Warehouse : Learn how to set up and utilize a robust, cloud-native data warehouse to store, retrieve, and analyze large volumes of structured data efficiently.
Lakehouse : Discover the hybrid architecture that combines the flexibility of a data lake with the performance of a data warehouse, enabling seamless management of both structured and unstructured data.
OneLake : Understand the centralized storage solution that supports all types of data, ensuring easy accessibility, security, and readiness for analysis.
Shortcuts : Explore how to eliminate data duplication and improve data governance by centrally defining and managing data assets4.
Data Pipelines : Learn to design, implement, and maintain automated data pipelines that ensure smooth data flow from various sources into your data warehouse and Lakehouse.
Notebooks : Utilize industry-standard tools to manage ETL processes, transforming raw data into valuable insights through collaborative coding environments.

By the end of this post, you’ll have a clear understanding of how to leverage Microsoft Fabric to create a seamless and efficient data engineering workflow that maximizes the value of your data.

Guide:

1. Understand the Basics: Familiarize yourself with the fundamental concepts of data engineering. Explore online resources, courses, and tutorials to build a strong foundation. Microsoft Fabric offers extensive documentation and learning paths to help you get started.

2. Set Up Your Environment: Ensure you have access to the necessary tools and platforms. With Microsoft Fabric, you can leverage a cohesive suite of data engineering tools integrated within the Microsoft ecosystem, including cloud services and data engineering software.

Data Movement

Microsoft Fabric Data Factory Pipeline

Orchestration is critical in the data engineering pipeline. A robust data pipeline allows you to manage and automate data loads, ensuring data flows smoothly from various sources into your data warehouse and lakehouse. With Microsoft Fabric's cloud-native data pipelines, you can easily handle even the heaviest workloads, ensuring your data is always up-to-date and ready for analysis.

Guide:

1. Design Your Pipeline: Plan your data pipeline architecture. Identify the data sources, transformation processes, and destinations. Microsoft Fabric provides intuitive tools to help you design and visualize your pipeline.

2. Implement the Pipeline: Use Microsoft Fabric's integrated tools, such as Azure Data Factory, to build and automate your data pipeline. These tools offer seamless integration and powerful automation capabilities.

3. Monitor and Maintain: Regularly monitor your data pipeline to ensure it runs smoothly. Microsoft Fabric includes robust monitoring and logging features to help you implement error handling and troubleshoot issues efficiently.

Next Steps

· Course: Explore Azure Pipelines

· Course: Create a build pipeline with Azure Pipelines

Microsoft Fabric Notebook

Notebooks are your best friend when managing ETL (Extract, Transform, Load) processes. These industry-standard tools bring the power of programming languages like Python and Scala to your fingertips, allowing you to write, test, and execute code in a collaborative environment. Notebooks support many open-source capabilities, making transforming raw data into valuable insights easier.

Guide:

1. Set Up Your Notebook: Choose a notebook platform, such as Jupyter, Databricks, or Azure Synapse Notebooks.

2. Write ETL Code: Write ETL scripts using programming languages like Python or Scala. Leverage libraries and frameworks to simplify data transformation tasks.

3. Collaborate and Share: Share your notebooks with team members for collaboration. Use version control to track changes and maintain consistency.

Next Steps

· Course: Get started with OneNote

· Resource: OneNote Class Notebook

Data Management

Microsoft Fabric Data Warehouse

Imagine having a robust vault where all your structured data is securely stored, easily accessible, and ready for analysis. This is what a data warehouse in Microsoft Fabric offers. It is an enterprise-scale, cloud-native solution designed to handle large volumes of data while remaining cost-effective for businesses of all sizes. The data warehouse enables you to store, retrieve, and analyze data efficiently, ensuring that your business decisions are always data-driven.

Guide:

1. Set Up Your Data Warehouse: Choose Microsoft Fabric as your cloud provider and set up your data warehouse. Microsoft Fabric integrates seamlessly with other Microsoft tools, providing a cohesive ecosystem for your data needs.

2. Load Data: Import your structured data into the data warehouse. Utilize ETL (Extract, Transform, Load) processes to ensure your data is clean and ready for analysis. Microsoft Fabric offers robust ETL capabilities to streamline this process.

3. Query and Analyze: Use SQL or other query languages to retrieve and analyze your data. Create insightful reports and dashboards using Power BI, which integrates seamlessly with Microsoft Fabric, to visualize your findings and drive informed business decisions.

Next Steps:

· Course: Get started with data warehouses in Microsoft Fabric

· Course: Build A Data Warehouse in Azure

Microsoft Fabric Lakehouse

Next, we have the lakehouse—a hybrid that combines the best of both worlds: a data lake's flexibility and scalability with a data warehouse's performance and reliability. The lakehouse allows you to manage both structured and unstructured data seamlessly. This means you can store raw data in its native format and process it as needed, providing a unified platform for all your data needs.

Guide:

1. Set Up Your Lakehouse: Choose a platform that supports lakehouse architecture, such as Databricks or Azure Synapse Analytics.

2. Store Data: Import both structured and unstructured data into the lakehouse. Ensure data is stored in its native format for flexibility.

3. Process Data: Use data processing tools to transform raw data into a usable format. Leverage the lakehouse's capabilities to handle large-scale data processing tasks.

Next Steps

· Course: Get started with lakehouses in Microsoft Fabric

· Course: Implement a Lakehouse with Microsoft Fabric

Data Storage

OneLake

Microsoft Fabric offers a unified and robust storage solution through OneLake, designed to centralize and streamline your data management. OneLake is the backbone of Microsoft Fabric, providing a scalable, high-performance storage system that supports structured and unstructured data. This integration ensures your data is easily accessible, secure, and ready for analysis.

Guide:

1. Understand the Basics: OneLake is built on top of Azure Data Lake Storage (ADLS) Gen2 and supports any type of file, whether structured or unstructured. It centralizes data storage across different domains and tenants, reducing redundancy and improving efficiency.

2. Set Up Your Environment: Ensure you have access to Microsoft Fabric and OneLake. OneLake integrates seamlessly with other Microsoft tools, providing a cohesive ecosystem for your data needs. It supports managed and unmanaged data storage, offering flexibility in managing your data.

3. Utilize OneLake Features: OneLake offers full Delta support using VertiParq, a powerful feature for tracking data changes. It also integrates with DirectLake, providing robust support for Power BI and enabling easy data discovery and visualization.

4. Monitor and Maintain: OneLake simplifies security while ensuring that sensitive data is kept secure. It provides distributed ownership of data and security, allowing you to manage access and permissions effectively. Please regularly monitor your storage to ensure it meets your performance and security requirements.

By leveraging Microsoft Fabric and OneLake, you can create a unified, efficient, and secure data storage environment that supports your business's analytical needs.

Next Steps

· Course: OneLake, the OneDrive for data

Microsoft Fabric Shortcuts

Let's talk about shortcuts. In data engineering, shortcuts help eliminate data duplication across your ecosystem. You can centrally define and manage data assets using shortcuts, ensuring consistency and reducing redundancy. This improves data quality and streamlines data governance, making it easier to comply with regulatory requirements.

Guide:

1. Define Shortcuts: Identify data assets that can be centrally managed using shortcuts. Create definitions for these assets to ensure consistency.

2. Implement Shortcuts: Use data management tools to implement shortcuts. Ensure that data assets are referenced correctly across your ecosystem.

3. Maintain Data Quality: Regularly review and update shortcuts to maintain data quality. Implement data governance practices to ensure compliance with regulatory requirements.

Next Steps

· Resource: Office cheat sheets

Conclusion

Incorporating a data warehouse, lakehouse, pipeline, notebook, and shortcut into your data engineering strategy with Microsoft Fabric can revolutionize your data handling. Each component brings unique benefits, from efficient data storage and processing to streamlined data management and governance. By leveraging Microsoft Fabric’s comprehensive suite of tools, you can create a seamless and efficient data engineering workflow that maximizes the value of your data. Embrace this powerful solution today and unlock the full potential of your data with Microsoft Fabric.

Next Steps

1. Data Warehouse

· Course: Get started with data warehouses in Microsoft Fabric

· Course: Build A Data Warehouse in Azure

2. Lakehouse

· Course: Get started with lakehouses in Microsoft Fabric

· Course: Implement a Lakehouse with Microsoft Fabric

3. OneLake

· Course: OneLake, the OneDrive for data

4. Shortcuts

· Resource: Office cheat sheets

5. Pipelines

· Course: Explore Azure Pipelines

· Course: Create a build pipeline with Azure Pipelines

6. Notebooks

· Course: Get started with OneNote

· Resource: OneNote Class Notebook

Microsoft Fabric vs Databricks

Mon, 19 Aug 2024 19:21:40 GMT

Compare Microsoft Fabric and Databricks, two leading data platforms. Highlights their features, strengths, and unique offerings across various domains like data engineering, data analytics, data science, DevOps, security, integration with other tools, cost management, and governance. Microsoft Fabric is noted for its low-code/no-code solutions and seamless integration with Microsoft tools, making it accessible for users with varying technical skills. Databricks is praised for its high-performance capabilities in big data processing and collaborative analytics, offering flexibility and control for experienced data teams.

Data Wizardry and Epic Adventures: A Day in the Life of a Microsoft Fabric Engineer

Wed, 15 Nov 2023 18:45:04 GMT

In a dynamic data engineering scenario, Sam, a skilled professional, adeptly navigates urgent requests using Microsoft Fabric. Collaborating with Data Steward Lisa and leveraging OneLake, Sam streamlines data processes, creating a powerful collaboration between engineering and stewardship. With precision in Azure Data Factory and collaboration with a Data Scientist, Sam crafts a robust schema, leading to a visually appealing Power BI report.

Power BI usage scenarios: Lakehouse to Power BI

Fri, 28 Apr 2023 11:00:00 GMT

NOTE: This is the first draft of this document that was assembled yesterday as a solo effort. If you would like to contribute or have any suggestions, check out my first public GIT repository - KratosDataGod/LakehouseToPowerBI: Architectural design for incorporating a Data Lakehouse architecture with an Enterprise Power BI Deployment (github.com)

This article is NOT published, reviewed, or approved by ANYONE at Microsoft. This content is my own and is what I recommend for architecture and build patterns.

Enterprise Data & Analytics Engineering

This article is A TRIBUTE TO the Power BI implementation planning series of articles. It describes using the medallion lakehouse architecture and the star schema design to create and manage data models optimized for performance and usability in Power BI. It also explains how to integrate these data models with an enterprise power bi data model that can serve as a single source of truth for your organization.

What is the medallion Lakehouse architecture?

The medallion lakehouse architecture is a data design pattern used to logically organize data in a lakehouse, aiming to incrementally and progressively improve the structure and quality of data as it flows through each layer of the architecture (from Bronze ⇒ Silver ⇒ Gold layer tables). The medallion architecture guarantees atomicity, consistency, isolation, and durability as data passes through multiple layers of validations and transformations before being stored in a layout optimized for efficient analytics.

A lakehouse is a new data platform architecture paradigm that combines the best features of data lakes and data warehouses. A modern lakehouse is a highly scalable and performant data platform hosting raw and prepared data sets for quick business consumption and to drive advanced business insights and decisions1. It breaks data silos and allows seamless, secure data access to authorized users across the enterprise on one platform.

The medallion architecture consists of three main layers: bronze, silver, and gold.

The bronze layer contains unvalidated data. Data ingested in the bronze layer typically maintains the raw state of the data source, is appended incrementally and grows over time, and can be any combination of streaming and batch transactions.
The silver layer contains validated, enriched data trusted for downstream analytics. Data in the silver layer is matched, merged, conformed, and cleansed ("just enough") so that it provides an "Enterprise view" of all its key business entities, concepts, and transactions.
The gold layer contains highly refined and aggregated data that powers analytics, machine learning, and production applications. Data in the gold layer is transformed into knowledge rather than just information.

What is the star schema design?

The star schema design is a dimensional data model used to organize data in a database that is easy to understand and analyze. The star schema design can be applied to data warehouses, databases, data marts, and other tools. The star schema design is optimized for querying large data sets.

The star schema design requires modelers to classify their model tables as either dimension or fact. Dimension tables describe business entities—the things you model. Entities can include products, people, places, and concepts including time itself. Fact tables store observations or events, including sales orders, stock balances, exchange rates, temperatures, etc.

The star schema design uses a denormalized approach to data, adding redundant columns to some dimension tables to make querying and working with the data faster and easier. The purpose is to trade some redundancy (data duplication) in the data model for increased query speed by avoiding computationally expensive join operations.

The star schema design has several benefits, such as:

It is simple to understand and implement
It enables incremental ETL
It can recreate your tables from raw data at any time
It supports ACID transactions and time travel
It is excellent for simple queries because of its reduced dependency on joins when accessing the data
It adapts well to fit OLAP models
It improves query performance as compared to normalized data

How does the star schema fit in the medallion lakehouse architecture?

The star schema design can be applied to any layer of the medallion lakehouse architecture. However, it is most commonly used for the gold layer tables that power analytics, machine learning, and production applications. The gold layer tables are often highly refined and aggregated, containing data that has been transformed into knowledge.

How to integrate with an enterprise Power BI data model

An enterprise Power BI data model is a data model that can serve as a single source of truth for your organization. It can provide consistent and accurate data for various reports and dashboards across different departments and business units. An enterprise Power BI data model can also enable self-service analytics and user collaboration.

To integrate your medallion lakehouse architecture and star schema design with an enterprise Power BI data model, you can follow these steps:

Identify the key business entities, concepts, and transactions relevant to your organization and align them with your lakehouse's bronze, silver, and gold layers.
Define the granularity, dimensions, and measures of your fact tables and the attributes and hierarchies of your dimension tables.
Use Azure Data Factory to connect to your data sources and ingest raw data into the bronze layer of your lakehouse. You can use incremental refresh to append new data over time.
Use Spark notebooks (in either Synapse or Azure Databricks) to validate, conform, and cleanse your data from the bronze layer and load it into the silver layer of your lakehouse. You can use query folding to optimize the performance of your transformations.
Use Spark notebooks (in either Synapse or Azure Databricks) to transform, enrich, and aggregate your data from the silver layer and load it into the gold layer of your lakehouse. You can use variables to improve your formulas.
Use Spark notebooks (in either Synapse or Azure Databricks) to create a star schema design for your gold layer tables by creating one-to-many relationships between your dimension and fact tables.
Use Tabular Editor to load tables directly into your analytical model from your gold layer. Enterprise solutions should contain no transformations between the gold and analytical layers. In TE3, you can use the waterfall technique or the star schema approach to organize your tables in the model view.
Use Tabular Editor to create measures using DAX to calculate business metrics based on your gold layer tables. You can use appropriate error functions, avoid converting blanks to values, use the divide function instead of the divide operator, use countrows instead of count, use selectedvalue instead of values, etc.
Use Power BI Desktop to create a separate report from your model using a live connection or direct query mode. You can use report page tooltips, report page drill-through, etc., to enhance your report design.
Use Power BI service to publish your model from Tabular Editor to a workspace dedicated to enterprise data models. While you can use deployment pipelines to manage development, test, and production environments, many enterprises find it worthwhile to build a CICD pipeline for model management, including data validation and regression testing.
Use the Power BI service to share your model and report with other workspace users. You can also enable row-level security (RLS) to restrict data access based on user roles.
The Power BI service includes increased controls over who is allowed to Read, Build, and Share reports that have access to your analytical models.
Use Power BI service to monitor and troubleshoot your model's performance and report. You can use query diagnostics, performance analyzer, etc., to identify and resolve issues.

Scenario diagram

The following diagram depicts a high-level overview of the most common user actions and relevant Data Engineering and Power BI components that support enterprise Data and Analytics. The focus is on the use of Azure DevOps to manage and publish content programmatically at scale through development, test, and production workspaces in the Azure service.

The scenario diagram depicts the following user actions, processes, and features.

Azure Data Factory is a cloud-based data integration service that allows you to create data-driven workflows (called pipelines) for ingesting data from various sources and orchestrating data movement and transforming data at scale.
The bronze layer is the first layer of the medallion lakehouse architecture that contains raw and unvalidated data from various sources, both streaming and batch .
The silver layer is the second layer of the medallion lakehouse architecture that contains validated and enriched data that can be trusted for downstream analytics.
The gold layer is the third and final layer of the medallion lakehouse architecture that contains highly refined and aggregated data that powers analytics, machine learning, and production applications.
Data in the silver layer is matched, merged, conformed and cleansed ("just-enough") so that it provides an "Enterprise view" of all its key business entities, concepts and transactions
The Gold layer also houses departmental and data science sandboxes to enable self-service analytics and data science across the enterprise. Providing these sandboxes and their own separate compute clusters prevents the Business teams from creating their own copies of data outside of the Lakehouse.
No Power Query transformations are allowed in enterprise models ensuring full query folding to the source to support both direct query and import load patterns. The only exception on transformations is for incremental loads and for aggregations on top of a direct query table.
Power BI workspaces dedicated for model deployments are workspaces that contain only data models and no reports or dashboards. These workspaces are used to create a single source of truth for enterprise data products and to enable centralized datasets that can be shared and reused by other workspaces .
Power BI workspaces for thin reports are workspaces that contain only reports and no data models. These workspaces are used to create reports that connect to existing datasets in other workspaces using live connection mode l.
To connect to data sources that reside within a private organizational network, an on-premises data gateway is required. You can set up Azure Pipelines to perform gateway configuration activities by using the Power BI REST APIs (not shown in this scenario diagram).
Data Engineers, Analytics Engineers, and Content creators regularly commit and push their changes to a remote repository during development by using a Git client such as Visual Studio Code. In this diagram, the remote repository is Azure Repos.
Changes to content in the remote repository trigger Azure Pipelines. A validation pipeline is the first pipeline that is triggered. The validation pipeline performs automated tests to validate content before it's published.
Content that passes the validation pipeline triggers a subsequent build pipeline . The build pipeline prepares content for publishing to the Synapse/ Databricks environments and to the Power BI service. The process up to this point is typically referred to as continuous integration (CI).
Content is published and deployed by using release pipelines . The release pipelines use Synapse/Databricks API's, either the Power BI REST APIs or the workspace XMLA endpoint to programmatically import content to the Power BI service. Deployment by using release pipelines is typically referred to as continuous deployment (CD).
Data Engineers manage the Lakehouse code deployments and promotions through Dev / Test / Production keeping in close contact with the Analytics Engineers always monitoring for breaking changes (schema or cardinality changes).
Analytics Engineers manage the tabular model deployments and data refreshes of the model through Dev / Test / Production keeping in close contact with the Data Engineers for up stream breaking changes, and with the Content Creators for down stream breaking changes.
Content Creators work with both the Data Engineers and Analytics engineers to monitor changes to the models, data quality, and refresh timing.
Data Teams may be all within IT, or they may be broken out by different domains and domain owners.
Data Engineers build and manage data pipelines, preparing for analytical consumption, and for ensuring data quality.
Analytics Engineers build and manage tabular models for consumption by Content Creators.
Content Creators focus primarily focused on delivering value to the business through the consumption of analytics.

Next steps

In this article, you learned how to use the medallion lakehouse architecture and the star schema design to create and manage Power BI data models optimized for performance and usability. You also learned how to integrate these data models with an enterprise power bi data model that can serve as a single source of truth for your organization.

To learn more about Power BI guidance documentation, you can refer to this link: https://learn.microsoft.com/en-us/power-bi/guidance/

To learn more about Power BI implementation planning series, you can refer to this link: https://learn.microsoft.com/en-us/power-bi/guidance/implementation-planning

To learn more about Power BI adoption roadmap series, you can refer to this link: https://learn.microsoft.com/en-us/power-bi/guidance/adoption-roadmap

To learn more about Power BI migration series, you can refer to this link: https://learn.microsoft.com/en-us/power-bi/guidance/migration

To learn more about Power BI Center of Excellence (COE) series, you can refer to this link: https://learn.microsoft.com/en-us/power-bi/guidance/coe

References

https://addendanalytics.com/blog/data-modelling-best-practices/

https://blog.enterprisedna.co/data-modelling-in-power-bi-tips-best-practices/

https://codingsight.com/power-bi-star-schema-the-easy-how-to-guide-for-starters/

https://community.powerbi.com/t5/Community-Blog/Data-Modelling-In-Power-BI-Helpful-Tips-amp-Best-Practices/ba-p/1977956

https://docs.databricks.com/lakehouse/medallion.html

https://learn.microsoft.com/en-us/azure/databricks/lakehouse/medallion

https://learn.microsoft.com/en-us/azure/data-explorer/power-bi-best-practices

https://learn.microsoft.com/en-us/power-bi/guidance/

https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

https://radacad.com/power-bi-basics-of-modeling-star-schema-and-how-to-build-it

https://www.databricks.com/glossary/medallion-architecture

https://www.mssqltips.com/sqlservertip/7597/star-schema-data-modeling-for-power-bi/

https://www.powercommunity.com/design-your-data-model-efficiently-with-star-schema-for-power-bi-must-know/

The Value of Hard Work and Resilience

Fri, 14 Apr 2023 19:03:53 GMT

Work Hard - Let's GO!

Hard work and resilience are two essential qualities for success in any field. Whether you are an athlete, a business person, a politician, or anything else, you will face challenges, setbacks, and failures. How you deal with them will determine your outcome.

One of the most inspiring examples of hard work and resilience is Kobe Bryant, the legendary basketball player who passed away in 2020. Kobe was known for his insane work ethic, which he developed from a young age. He would wake up at 4 a.m. to practice before school, continue to train after school, and even late at night. He would push himself to the limit, often playing through injuries and pain. He would study his opponents, his teammates, and himself, constantly looking for ways to improve. He would also seek out mentors and coaches who could help him grow as a player and a person.

"I wanna be the best, simple, and plain." Kobe Bryant

In Data

As a Power BI developer, you can learn from Kobe's example and apply his Mamba Mentality to your data analysis and visualization projects.

To master Power BI, you need to work hard and be resilient. You must practice your skills regularly, learn from your mistakes, seek feedback from others, and keep up with the latest updates and features. You must overcome data quality, security, performance, and governance challenges. You must find creative ways to tell stories with your data and communicate insights to your audience.

By doing so, you will improve not only your Power BI skills but also your career prospects. Power BI is one of the most in-demand skills in the market today, as more and more organizations rely on data-driven decision-making. Power BI can help you stand out, showcase your value, and advance your career goals.

In Personal Life

Hard work is essential not only for your professional success but also for your personal happiness. Working on your personal relationships means investing time, energy, and care into the people who matter to you. It means being supportive, respectful, and honest with your partner, family, friends, and colleagues. It means communicating effectively, listening actively, and resolving conflicts constructively. It means expressing your feelings, needs, and expectations and understanding those of others. It means showing appreciation, gratitude, and affection and receiving them in return.

Working on your personal relationships requires hard work, but it also brings great rewards. Working hard on your personal relationships can strengthen your bonds, deepen your trust, and enhance your intimacy. You can also improve your well-being, health, and happiness. You can also cope better with stress, challenges, and difficulties. You can also learn from your personal relationships and grow as an individual and a partner.

So keep going with Power BI. Work hard and be resilient. As Kobe said: "Everything negative - pressure, challenges - is all an opportunity for me to rise."

The Value of Hard Work, Perseverance and Optimism

Perseverance and optimism are essential qualities for success in any field. Whether you are an athlete, a business person, a politician, or anything else, you will face difficulties, disappointments, and failures along the way. How you cope with them will determine your ultimate outcome.

One of the most inspiring examples of perseverance and optimism is Naoki Higashida, a Japanese author with severe autism. Higashida has difficulty communicating verbally, but he learned to express himself through writing using an alphabet grid. He wrote his first book, 'The Reason I Jump', when he was 13 years old, and it became an international bestseller. He later wrote another book, Fall Down 7 Times Get Up 8, where he shared his thoughts and experiences as a young man living with autism. He reveals his struggles, joys, hopes, and dreams in his books with honesty and courage.

"With ordinary talent and extraordinary perseverance, all things are attainable" Thomas Fowell Buxton

In Data

As a Power BI developer, you can learn from Higashida's example and apply his perseverance and optimism to your data analysis and visualization projects.

To master Power BI, you need to persevere and be optimistic. You need to face the realities of data availability, quality, and security. You need to cope with the uncertainties of data sources, formats, and models. You need to handle the complexities of data transformations, calculations, and visualizations. You need to trust your data and your insights.

In Business

Your company wants to develop a new Power BI feature for its product to allow users to create and share custom reports. The company knows that this feature will be a game-changer for their customers and a massive challenge for them. They will have to overcome many difficulties, uncertainties, and failures.

The company repeats this iterative design process until they achieve a satisfactory solution. They then launch the new feature to their customers and celebrate their success. They are proud of their hard work and grit but also humble and eager to learn and improve.

You need to follow an iterative design process where you plan, build, test, and refine your data solutions. You must experiment with different data sources, transformations, calculations, and visualizations. You need to solicit feedback from your stakeholders, users, and peers. You need to learn from your failures and successes.

By doing so, you will improve not only your Power BI skills but also your career prospects.

In Personal Life

Imagine you are facing a mountain of challenges and struggles in your life. You are drowning in debt, battling a chronic illness, dealing with a broken family, or suffering from a stressful job.

You feel like giving up like there is no hope, no way out.

But you are not alone.

You have the power to climb that mountain and reach the summit. You have the power of hard work, perseverance, and optimism. Hard work means you don't quit, you don't settle, and you don't stop. You put in the effort, the time, and the dedication to improve yourself and your situation.

Perseverance means you don't let anything or anyone stop you from pursuing your goals. You persist, you endure, and you overcome. Optimism means you maintain faith in yourself and your future. You have a positive outlook and attitude that inspire you and others.

By persevering and being optimistic, you can conquer any challenge and create any opportunity. You can also care for your loved ones and deal with personal tragedies. You can grow as an individual and a professional and positively impact the world.

The Value of Hard Work and Personal Resilience

Hard work and personal resilience are essential for success in any field. Whether you are an athlete, a business person, a politician, or anything else, you must work hard and overcome adversity to achieve your goals.

One of the most inspiring examples of hard work and personal resilience is Cristiano Ronaldo, the Portuguese soccer star widely regarded as one of the greatest players ever. Ronaldo was born into a poor family in Madeira, Portugal. He had a difficult childhood, marked by poverty, hunger, and bullying. He also had a heart condition that threatened his career. But he did not let these obstacles stop him. He worked hard to improve his skills, fitness, and mindset. He moved to Lisbon at 12 to join Sporting CP, a professional soccer club. He later transferred to Manchester United, Real Madrid, and Juventus, where he won numerous trophies and awards. He also became the captain and leading scorer of the Portuguese national team, leading them to their first European Championship title in 2016.

"Talent without hard work is nothing" Cristiano Ronaldo

Ronaldo's hard work and personal resilience paid off. He became one of the most successful and influential soccer players in history. He broke many records, including being the first player to score more than 50 goals in six consecutive seasons, the first to win four European Golden Shoes, and the first to score in four World Cups. He also won five Ballon d'Or awards, the most prestigious individual honor in soccer. He is also known for his philanthropy and social activism, supporting various causes such as education, health, and disaster relief.

In Personal Life

Hard work and personal resilience can also help us care for our loved ones and deal with personal tragedies. Ronaldo knows this very well, as he has faced many losses and hardships. He lost his father to alcoholism when he was 20 years old. He lost his brother-in-law and his grandfather in the same year. He also lost his close friend and former teammate, Diego Maradona, in 2020. He has also been involved in legal battles, tax fraud allegations, and rape accusations. But he has always shown compassion, generosity, and strength during difficult times. He has supported his mother, who suffered a stroke in 2020. He has raised four children, three of whom were born through surrogacy. He has donated millions of dollars to various charities and causes. He has also maintained his professionalism and excellence on the field, inspiring millions of fans worldwide.

You may be going through some challenges and struggles right now. You may face financial difficulties, health issues, family problems, or work stress. You may be feeling overwhelmed, discouraged, or hopeless. But you are not alone. You have the power to overcome any obstacle and achieve any goal. You have the power of hard work and personal resilience. Hard work means putting in the effort, the time, and the dedication to improve yourself and your situation. Emotional stability means bouncing back from setbacks, failures, and difficulties with courage and optimism. You can cope with any challenge and create opportunities by working hard and being resilient. You can also care for your loved ones and deal with personal tragedies. You can grow as an individual and a professional and positively impact the world.

In Data

Data validation and data quality require hard work and personal resilience. You need to work hard to identify and correct any errors, gaps, or anomalies in your data. You must be resilient to cope with your data sources' changes, updates, or issues. You must follow best practices and guidelines for data collection, processing, and storage. You must use appropriate data validation and quality assessment tools and methods. You need to document and communicate your data validation and quality results.

You can enhance your data analysis, visualization skills, and outcomes by working hard and being resilient in data validation and quality. You can increase your confidence and credibility in your data and insights. You can provide more value and impact to your stakeholders and customers. You can also learn from your data validation and quality process and improve future projects.

Ronaldo's story shows us that hard work and personal resilience can help us overcome challenges and achieve dreams. Hard work means putting in the effort, the time, and the dedication to improve ourselves and our performance. Personal resilience means bouncing back from setbacks, failures, and difficulties with courage and optimism. By working hard and being resilient, we can grow as individuals and professionals and positively impact the world.

The Value of Hard Work and Long Hours

Hard work is a virtue that many people admire and aspire to. But what does hard work mean in different cultures and contexts?

For a dairy farmer in Wisconsin, hard work might mean waking up before dawn, milking the cows, feeding the animals, cleaning the barn, and delivering the milk to the market. For a rice farmer in Vietnam, hard work might mean planting the seeds, tending the fields, harvesting the crop, and selling the rice to the buyers. Both farmers work long hours and face many challenges, but they have different lifestyles and values. The dairy farmer might enjoy the comfort of a warm house, a car, and a television, while the rice farmer might live in a simple hut, ride a bicycle, and listen to the radio. The dairy farmer might value independence, efficiency, and innovation, while the rice farmer might value community, tradition, and harmony. Hard work is not a universal concept but a relative one that depends on the culture and context of each individual.

The number of hours each type of farmer may work can vary depending on the season, the weather, the market, and other factors. However, a general estimate can be made based on some assumptions and averages. A dairy farmer in Wisconsin may work about 12 hours a day, from 5 a.m. to 5 p.m., with some breaks in between. A rice farmer in Vietnam may work about 10 hours a day, from 6 a.m. to 4 p.m., with a more extended break at noon. Both farmers may work six or seven days a week, depending on the demand and supply of their products. Therefore, both farmers may work about 70 hours a week on average, more than twice the typical 40-hour workweek of an office worker in the United States. However, the number of hours does not necessarily reflect the quality or the outcome of the work, as many other factors influence each farmer's productivity and profitability.

In Business

Hard work can also mean different things at different phases of a career or when a person decides to change jobs or advance their career. For example, a young professional just starting out in their field may work hard to learn new skills, gain experience, and build a network. They may work longer hours, take on more responsibilities, and seek feedback and mentorship. For them, hard work is a way to establish themselves and grow professionally. On the other hand, seasoned professional who has been in their field for a long time may work hard to maintain their reputation, update their knowledge, and adapt to changes. They may work smarter hours, delegate tasks, and share their expertise. For them, hard work is a way to sustain themselves and stay relevant. Finally, a person wanting to change careers or advance their job may work hard to explore new opportunities, acquire qualifications, and overcome challenges. They may work extra hours, pursue education or training, and take risks. For them, hard work is a way to reinvent themselves and achieve their goals.

In Personal Life

Hard work and long hours are not only applicable to work life but also to personal life. Working hard in one's personal life can benefit one's well-being, happiness, and relationships. For example, a person who works hard in their personal life may invest time and energy in their hobbies, passions, and interests. They may pursue their dreams, express their creativity, and enjoy leisure. For them, working hard in their personal life is a way to enrich themselves and have fun.

Similarly, people working hard in their personal lives may devote time and attention to their family, friends, and community. They may nurture their bonds, support their loved ones, and contribute to society. Working hard in their personal life is a way to connect with others and make a difference.

Moreover, the experiences gained from working long hours at a job can positively impact one's ability to work hard in their personal life. For instance, a person who has worked long hours at a job may have developed discipline, resilience, and problem-solving skills. They may also have learned integrity, responsibility, and teamwork. These skills and values can help them to work hard in their personal life by setting goals, overcoming obstacles, and collaborating with others.

Conclusion

Now you know the secret to success and happiness in any aspect of life: hard work.

Hard work is not a burden but a blessing.

Hard work is not a punishment but a privilege.

Hard work is not a sacrifice but a reward.

Whether you are working on your professional skills, relationships, or resilience, you need to work hard to improve yourself and your situation. You also need to work hard to overcome any challenge, failure, or difficulty that comes your way. You must work hard to believe in yourself and your potential and never give up on your goals and dreams.

As the stoic philosopher Seneca said:

“It is not because things are difficult that we do not dare; it is because we do not dare that things are difficult.”

Dare to work hard.

Dare to overcome adversity.

Dare to achieve greatness.

You have the power within you to do so.

Data God Guide to Learning DAX

Sun, 22 Jan 2023 22:16:07 GMT

DAX is not just the name of my new puppy

DAX (Data Analysis Expressions) is a formula language used in Power BI to create custom calculations and aggregations unavailable in the standard user interface. It is similar to Excel formulas but is designed to work with relational data and perform dynamic aggregation.

Here is a guide to help you get started with learning DAX:

Understand the basic structure of DAX formulas: DAX formulas begin with an equal sign (=) and are composed of functions, operators, and constants. The basic structure of a DAX formula is similar to that of a regular mathematical formula.
Learn the essential DAX functions: Several are commonly used in Power BI, including SUM, COUNT, AVERAGE, MAX, and MIN. These functions can perform basic calculations and aggregations on your data.
Understand the difference between calculated columns and calculated tables: In Power BI, you can create calculated columns and calculated tables. Calculated columns are added to an existing table, while calculated tables are made from existing tables. The critical difference between the two is that calculated columns are calculated at the row level, while calculated tables are calculated at the table level.
Learn how to use filters in DAX formulas: DAX formulas can use filters to include or exclude specific rows of data from the calculation. This can be useful when you want to create measures based on particular data or conditions.
Practice with sample data: The best way to learn DAX is to practice using it on a sample dataset. You can use the Power BI sample data on KratosBI.com.
Understand the context of the calculation: DAX formulas work with the context of the calculation, which is the set of rows and columns on which the formula is currently working. Understanding the context of the measure is crucial for getting the correct results from your DAX formulas.
Learn about time intelligence functions: Power BI has a set of time intelligence functions that make it easy to work with date and time data. These functions allow you to perform calculations such as year-to-date, quarter-to-date, and the same period as last year.
Understand the difference between relationships and context: To get the most out of DAX formulas, you need to understand the difference between relationships and context. Relationships define how tables are related to each other, while context defines which table rows are used in a calculation.
Learn about advanced DAX functions: Many advanced DAX functions can be used to perform more complex calculations. Some of these functions include RANKX, RANK.EQ, and TOPN.
Keep learning and experimenting: DAX is a powerful language that can be used to perform a wide variety of calculations and aggregations. The more you know about DAX, the more you can do with it. Keep experimenting with different formulas and try new things to see what works best for your data and analysis.

Remember that practice is the key to mastering DAX, and keep experimenting with different formulas and try new things to see what works best for your data and analysis.

What is a Power BI Datamart?

Tue, 03 Jan 2023 18:53:46 GMT

And why you should include them in your solution

A blog by ChatGPT with a little help and guidance.

In a data architecture, PowerBI datamarts are usually positioned downstream from other components, such as data warehouses and data lakes. This means data is typically loaded into a datamart after it has been processed and transformed in some way.

For example, data from transactional databases, log files, and web analytics tools may be extracted, transformed, and loaded into a data lake. The data may be further transformed and organized into a data warehouse. Finally, the data may be loaded into a PowerBI datamart for fast querying and analysis.

The online Power BI Architecture has many similarities to the classic Microsoft Business Intelligence (MSBI) toolkit.

SQL Server Integration Services (SSIS) >>> has become >>> Power BI Dataflows

SQL Server Database >>> has become >>> Power BI Datamarts

SQL Server Analysis Services (SSAS) >>> has become >>> Power BI Datasets

SQL Server Report Server (SSRS) >>> has become >>> Power BI Paginated Reports

New Paragraph

In Power BI, a dataflow is a reusable data pipeline that you can use to extract, transform, and load data into the Power BI service. Dataflows allow you to centralize your data preparation logic, so you can easily share your data with others and reuse it in multiple reports and dashboards.

A dataflow consists of one or more entities defined by a query specifying how to extract and transform data from a source. You can create dataflows by connecting to various sources and using the Power Query language to shape and transform the data. You can then use the data in the dataflow to create reports and dashboards in the Power BI service.

A PowerBI datamart is a specialized database that queries and analyzes data quickly. It is typically used to store large amounts of data from various sources, such as transactional databases, log files, and web analytics tools downstream from the data warehouse.

The primary benefit of using a PowerBI datamart is the ability to quickly and easily access and analyze large amounts of data. With a datamart, you can easily create interactive dashboards and reports that visualize your data in meaningful ways. This can help you identify trends, patterns, and insights that can inform business decisions and strategies.

Another advantage of PowerBI datamarts is their flexibility. You can easily connect to various data sources, such as SQL databases, Excel spreadsheets, and CSV files. This makes it easy to bring all of your data together in one place and analyze it in a unified way.

In Power BI, a dataset is a collection of data imported from one or more sources. It can include tables, columns, and measures (calculations) created from the data. You can use datasets to create visualizations, reports, and dashboards in Power BI.

You can create a dataset by connecting to a data source and importing data or by using the data modeling features in Power BI to shape and transform the data. Once the dataset is created, you can use it as the basis for creating visualizations and reports in Power BI.

Overall, a PowerBI datamart is a powerful tool for storing, querying and analyzing large amounts of data. Whether you're a small business owner looking to gain insights from your sales data, or a large enterprise looking to optimize your operations, a PowerBI datamart can help you make better, data-driven decisions.

Is Power BI PRO Enough?

183:780143518 (Christopher Wagner) — Mon, 07 Feb 2022 19:03:37 GMT

TOP 6 7 reasons why EVERY company should have Power BI Premium

With Power BI Pro becoming part of an Enterprise E5 license, it's become a common question- Is Power BI PRO Enough?

This is the answer I give to almost everyone.

You 100% can operate on just Power BI PRO. If you ONLY want Power BI to support Adhoc users, then PRO may be fine.

There will be some notable omissions that you may find very valuable. This is especially true if you are looking to have any enterprise-wide reporting and need to engineer the solution.

Every organization that is looking for more than Adhoc reporting needs to develop a Power BI Premium Strategy.

Premium Per User (PPU)

If you have fewer than 500 people in your organization, at a minimum you should consider Power BI Premium Per User (PPU) as the baseline licensing level for your company. This basically takes your licensing cost from $10 per month per user to $20. Power BI is still by far the least expensive and highest ROI of any BI tool. See the below features for WHY you should take this Jump.

Premium Capacities

If you have more than 500 people in your org, or you have a high demand for resources (it's possible to need dedicated capacity beyond the shared PPU capacity), at a minimum you should consider including at least one P1 ($5k per month- the break-even of PPU is 500 users). Depending upon your size, you will want to develop a strategy for how you Scale UP (going from P1 >> P2 >> P3 >> P4 >> P5) and how you Scale OUT (going to several P1s etc.). These strategies will be dependent upon your organization and budgetary needs. Connect with your Microsoft Sales team for walking through what will work best for your company.

Premium Capabilities

Below are the TOP 6 reasons why EVERY company should have Power BI Premium and NOT just Power BI Pro.

1. XMLA Endpoints - Model management and advanced API controls - This is the #1 item that in and of itself makes it worthwhile for Premium. The development and management time saved with XMLA endpoints for any of your larger models makes Premium a 'must have' for an organization.

2. Larger Models - A cap of 10GB total model sizes of PRO can be limiting.

3. Dataflows - Pro only has a light version of Dataflows. The full features you will want/need for Power BI management will need to include Dataflows for managing data INTO the service that's used by many ppl and have Premium compute.

4. Paginated Reports - You will have needs for detail level reporting that PBI is not designed to provide (anything more than 100k records) in an efficient manner. Paginated Reports allow you to write reports straight off the backend with no data limits.

5. Hybrid Tables - Hybrid Tables allow you to have both In-Memory data as well as a partition that is Direct Query to the backend. This allows for real-time feeds with the bulk of the data cached.

6. AI/ML Advanced Data Science Workloads without requiring a Data Scientist. This allows people to leverage the power of data science without having to get a Data Scientist.

7. Power BI Pipelines brings DevOps to the analytics world in a way that's easy to use, robust enough for most use cases, and starts the journey into hard core enterprise DevOps practices. Editor's Note: Thank you James Howell for pointing out this missed feature.

QUALITY DATA - Part 6

183:780143518 (Christopher Wagner) — Thu, 13 Jan 2022 14:45:00 GMT

Lets wrap this up!

Storing the results of Data Quality tests can be very helpful in determining the overall quality of the Data over time, identity poorly performing sources/processes. This quality data is also instrumental for In-Line quality tests to ensure that insufficient data doesn't make it to Production and Off-Line test for automatically rolling back issues in Production.

Example:

Customer ABC had more than 12,000 addresses for the last month. Today customer ABC only has 11,000 addresses (more than a 10% drop in addresses). All files have been processed successfully, and the other QA tests are all passed.

This variance could trigger a Rollback with notification to Data Engineering and the Business to review. Maybe client ABC closed over 10% of their locations, or a change to the source system introduced a special character in the business key that's incorrectly dropping the addresses.

The Rollback keeps the data for the customer in its last know good state until this can be reviewed and confirmed by engineering and the business.

DATA QUALITY SUMMARY

Data Quality is a highly complex topic that is critical to the success of any data platform. How you handle data quality can make or break your data platform. We know issues with data quality will arise.

How you deal with them is the challenge.

DATA QUALITY BLOG SERIES

Each day the Data Quality Blog post will be released at 8:45 AM each day.

DATA QUALITY - Part 1 January 6th

DATA QUALITY CONCEPTS - Part 2 January 7th

DATA QUALITY FOR EVERYONE - Part 3 January 10th

DATA QUALITY FRAMEWORK - Part 4 January 11th

DATA QUALITY DEVELOPMENT - Part 5 January 12th

QUALITY DATA - Part 6 January 13th

DATA QUALITY DEVELOPMENT - Part 5

183:780143518 (Christopher Wagner) — Wed, 12 Jan 2022 14:45:00 GMT

You want quality data? You gotta build it!

Data platforms do not spring forth fully built and operational. They take time to develop and build out. The same is true for a Data Quality practice. To ensure you have the appropriate Data Quality checks on your platform, include QA Elements to your Quality Framework for each Feature, HotFix, and Defect.

Feature - every feature added to a data environment should include a series of tests and validations necessary to say that the feature is delivered successfully. Define the QA tests when creating each new feature so Quality Engineering, Data Engineering, and Analytics Engineering understand the scope and complexity of the feature. Validation tests should be a baseline requirement for every code deployment.

It's essential to understand the Quality requirements as you design features, the acceptable variance within the system, and the appropriate actions to take given an issue.

Some data may have no tolerance for variance, and some may have a degree of variance and still be acceptable.

Examples:

Batch loads of data from a live system of record will have a variance between when the batch loads start and when validated against the source.

Large numbers of transactions added/multiplied together can create variance due to rounding differences between systems. Enough records with '.00000000012' cents can add up.

Setting up expectations for reasonable variance levels for any feature is vital to establish at the start.

These are the recommended thresholds and responses:

0-1% Feature passes QA

1-3% Feature has a Level 1 Defect. Log the Defect in the backlog, and the business provides approval/ rejection of moving to Production.

3-5% Feature has a Level 2 Defect. Log the Defect in the backlog, and the business provides approval/ rejection of moving to Production. A warning is added to the Defect as a potentially significant issue. If this issue is detected in Production, follow rollback/ 3x retry, then manual review process: Alert engineering, quality, and business of a potential problem.

5%+ feature fails QA and is sent back to engineering as a bug where code cannot move to Production. If detection occurs in Production, alerts are sent to engineering, quality, business, and any change review process warning of the issue and the need for a HotFix. The case is logged on the production quality dashboard.

HotFix : Issues with upstream data quality will always occur. Upstream changes impact data consumption, leading to Hot Fix changes to align data ASAP.

If there is ever a need for engineering to build code and implement a Hot Fix that was not triggered by a QA test, then QA needs to add quality tests when the Hot Fix change request goes live. This ensures that:

1. QA has 'skin in the game' and is working closely with the business and engineering.

2. Any defects addressed by the Hot Fix have quality tests associated with the issue, ensuring that these issues are blocked or engineering is alerted to the problem in the future.

3. Encourages QA to have robust data quality checks in place to minimize their Hot Fix engagements (no one wants to respond to issues in the dead of night).

Defect : In every complex system, imperfections will exist. When a Defect is identified, and engineering develops a fix, quality should build tests to ensure the issue is resolved correctly. Treat defects in the same way features are treated.

DATA QUALITY BLOG SERIES

Each day the Data Quality Blog post will be released at 8:45 AM each day.

DATA QUALITY - Part 1 January 6th

DATA QUALITY CONCEPTS - Part 2 January 7th

DATA QUALITY FOR EVERYONE - Part 3 January 10th

DATA QUALITY FRAMEWORK - Part 4 January 11th

DATA QUALITY DEVELOPMENT - Part 5 January 12th

QUALITY DATA - Part 6 January 13th

DATA QUALITY FRAMEWORK - Part 4

183:780143518 (Christopher Wagner) — Tue, 11 Jan 2022 14:45:00 GMT

If you measure it, you change it

The Quality Framework will address these dimensions of quality across these different levels and comprise In-Line, Off-Line tests, and automatic fallback processes.

FRAMWORK OF DATA QUALITY

There are many areas that play into the Framework of Data Quality. A shout out to Daryl Lynch for providing this structure.

1 - Governance

2 - Processing

3 - Data Quality

4 - Controls

5 - Reporting

Governance is not Data exclusive role. Business teams needs to define the Governance. This informed the front line workers that do the data capture at the process step. The Governance is less about Data requirements and more about Business Process requirements or Risk Management requirements.

Processing of data and understanding the quality at each step is very important in successfully moving to the next step and in troubleshooting any quality issues at any downstream level.

Data Quality often needs to be viewed and understood on a table by table and field by field basis. This helps when it comes to Governance and any data cleansing efforts you may need to undertake.

Controls and access allow you to better manage the data within your organization is a direct impact of the Data Quality of the platform.

Reporting , while the final piece, is where most users interact with the data. Providing clear visibility to the Quality of the data on the platform can be very beneficial to users in building trust and growing adoption.

FRAMWORK OF DATA QUALITY TESTS

IN-LINE TESTS

In-Line Tests are designed to test the quality of the data as it flows through the system. These tests impact the load times of processes, so tests must have a targeted scope to run within a brief period. Move any test that takes more than a minute to run to an Off-Line test.

At a minimum, In-Line tests should define:

Pass - continue processing

Fail - stop processing

Alert - notify administrators of a potential issue.

A fourth category of 'Retry' may be defined as part of the Fail/ Alert status.

In-Line testing for duplication is critical for both Type 1 (static/ current values) and Type 2 (historic) values. While Type 1 failures will

have a larger immediate impact, they are also easier to test and fix. Type 2 failures can be tough to resolve and can go unnoticed for extended periods.

Think of In-Line tests as Counts, Amounts, and Hash tests.

Counts- How many records should have been processed?

Amounts- What's the total value of the records processed? (Sum of defined field(s))

Hash- What's the total size of the Data processed? (Can be by file or by record)

PROTIP - Many failures can come from locked sources. A retry count of 3 over a 5 min window allows for locks to expire and processes to complete. Anything longer than 5 min can indicate a system issue, and manual review is usually recommended.

OFF-LINE TESTS

Off-Line Tests are more extended running Data Quality tests that cannot be inserted into In-Line processing without negatively impacting processing.

Off-Line tests should start to break out the Counts and Amounts by business value and compare this breakout and values to a secondary data feed explicitly designed for validation.

Note- Validation data feeds should be specifically designed to validate the breakout. To avoid circular validation fallacies (validating code/data against the same code/data), validation data should avoid any translations from processing and use different data feeds used by processing.

Common Off-Line tests :

Number of Sales People vs. Sales People in Source

Sales by Sales Person for current day/ month/ year.

Sales by Fiscal year

Sales by product for current day/ month/ year.

Off-Line validation's initial goal is to have measures broken out by every dimension in your data model. Once this is done, add additional fields from key Dimensions. Ensuring that one field from each dimension validates to a known source ensures that you first test referential integrity and then test any other mappings.

Off-Line Test Caution

Off-Line tests still need to be concerned with the time it takes to execute and the ROI from the test. While people often think that the best test is to compare record by record and field by field, the reality is that attempting to achieve these results will take an inordinate effort, time, and compute. If it takes 3-9 Months to do validation, you will be unable to leverage these validations in real-world situations.

DATA QUALITY BLOG SERIES

Each day the Data Quality Blog post will be released at 8:45 AM each day.

DATA QUALITY - Part 1 January 6th

DATA QUALITY CONCEPTS - Part 2 January 7th

DATA QUALITY FOR EVERYONE - Part 3 January 10th

DATA QUALITY FRAMEWORK - Part 4 January 11th

DATA QUALITY DEVELOPMENT - Part 5 January 12th

QUALITY DATA - Part 6 January 13th

DATA QUALITY FOR EVERYONE - Part 3

183:780143518 (Christopher Wagner) — Mon, 10 Jan 2022 14:45:00 GMT

Everyone Plays A Role In Data Quality

Data Quality is not something that 'just happens.' Data Quality takes a great deal of work from a wide variety of different roles. Everyone who interacts with data should play a role in defining what does data quality mean to them.

Data Engineering - Data Engineering is the first line of defense for ensuring high-quality data. The checks Data Engineering will build will enable a high degree of foundational data quality but can be the most susceptible to being technically correct but fail business needs. Data Engineering needs to make sure the process allows for both technical and business validation checks. Rollback of data entities needs to be built and understood in the recovery process.

Analytics Engineering - Analytics Engineering is where the data meets the business. Analytics Engineers need to keep an eye on expectations between data engineering and the company.

Analyst - As SMEs with a deep understanding of the business. Analysts are excellent at knowing when data is not suitable for a given subject. This knowledge needs to be distilled and transformed into quality tests.

Product/ Project Manager - Like Analysts, Product/ Project Managers have a broad understanding of the business and set the baseline standards for data quality. Product Managers will ensure that each feature includes the necessary Quality Tests to ensure that the end result aligns with the expectations of people within the business. The Product/ Project Manager is a critical role that is often overlooked and can cause many data quality issues.

Business Leadership - Like Analysts, Business Leadership has a broad understanding of the business. Business Leadership is an excellent source for comprehensive data quality requirements that individual SMEs may miss.

DATA QUALITY BLOG SERIES

Each day the Data Quality Blog post will be released at 8:45 AM each day.

DATA QUALITY - Part 1 January 6th

DATA QUALITY CONCEPTS - Part 2 January 7th

DATA QUALITY FOR EVERYONE - Part 3 January 10th

DATA QUALITY FRAMEWORK - Part 4 January 11th

DATA QUALITY DEVELOPMENT - Part 5 January 12th

QUALITY DATA - Part 6 January 13th

DATA QUALITY CONCEPTS - Part 2

183:780143518 (Christopher Wagner) — Fri, 07 Jan 2022 14:45:00 GMT

You have to know where you are before you know where you are going

Before we go too deep into Data Quality, we must first establish standard verbiage and the breadth and depth of the concepts at hand. These brief definitions of the topics need to be addressed in data quality. By no means is this an in-depth list of Data Quality terms, nor are they fully defined. The below definitions are just enough to get you started.

Completeness - Do you have a complete record or dataset?

Accuracy - Does the data match the real world?

Consistency - Is the data consistently cleansed and formatted? (Chris, CHRIS, chris, cHrIS)

Validity - Does the data represent real-world expected values? (ZIP codes are ZIP Codes, Countries are Countries, etc.)

Uniqueness - Is each record suitably unique? (You should only have one business key in a type one dimension.)

Integrity - As data moves through a system, are you losing or duplicating records?

Accessibility - Do the right people have access to the correct data in the right way? (Business users via reports and dashboards, analysts via query tools, data engineers via sources, transformation, errors)

Timeliness - Do you have the data within the acceptable SLA? (Real-time/ near real-time, micro-batch, batch)

Relevance - Is the data relevant for the business needs?

Actionable - Do you have a complete record or dataset? Is the data Actionable, and is the data recommending the right actions?

DATA QUALITY BLOG SERIES

Each day the Data Quality Blog post will be released at 8:45 AM each day.

DATA QUALITY - Part 1 January 6th

DATA QUALITY CONCEPTS - Part 2 January 7th

DATA QUALITY FOR EVERYONE - Part 3 January 10th

DATA QUALITY FRAMEWORK - Part 4 January 11th

DATA QUALITY DEVELOPMENT - Part 5 January 12th

QUALITY DATA - Part 6 January 13th

DATA QUALITY - Part 1

183:780143518 (Christopher Wagner) — Thu, 06 Jan 2022 14:45:00 GMT

Data Quality Will Make Or Break Your Analytics

Now more than ever, companies are striving to make data-driven decisions. With the vast amount of data at our fingertips, one can easily mistake the quantity (or speed) of the data available for the value derived from the data.

'I need sales reports' quickly turned into "I have far too many sales reports' when executives realized that the 1k reports they now have access to were producing skewed actions, different results, and confusion across the organization.

Just because we can turn analytics around in a day does not mean that we should.

The value of Data platforms is measured not in size, speed, or the tools used to operate them but in the value generated by the actions they enable broadly across an organization.

These actions range from low ROI activities (debating the quality, accuracy, and timeliness of the data) to high ROI activities (generating sales, identifying opportunities, managing risks, to data as a service). They include harmful ROI activities (data that lead to the wrong activities - think mortgage crisis).

This blog series will guide you on a wide range of data quality topics, roles, frameworks, development, and much more.

DATA QUALITY BLOG SERIES

Each day the Data Quality Blog post will be released at 8:45 AM each day.

DATA QUALITY - Part 1 January 6th

DATA QUALITY CONCEPTS - Part 2 January 7th

DATA QUALITY FOR EVERYONE - Part 3 January 10th

DATA QUALITY FRAMEWORK - Part 4 January 11th

DATA QUALITY DEVELOPMENT - Part 5 January 12th

QUALITY DATA - Part 6 January 13th

(Power) BI Developer Profiles

183:780143518 (Christopher Wagner) — Tue, 26 Oct 2021 19:26:16 GMT

An interviewers guide for hiring (Power) BI Developers

Working in data takes all types of skillsets, abilities, goals and work styles. The same is true when it comes to Power BI Developers. Knowing the type of Power BI developer you are, or that you are looking to hire, can help you focus on the work that best suits your personality type. Understanding these profiles can really help people looking to successfully hire new resources.

SKILLS

When evaluating a Power BI developer, you need to understand these primary skill categories to understand where an individual fits into the different Power BI developer profiles.

Sourcing - BI developers will access and use data from a variety of sources. SQL, Excel, CSV, JSON, XML, SharePoint lists, etc., are all potential data sources for BI. Understanding a candidate's experience with a variety of data types is essential.
Interview Question: What are the three main parts of a SQL Query?
Answer: Select / From / Where

Transformation - Data must be reshaped to be useful for analytics. An engineer's experience in transforming data speaks to their ability to work with various formats and conform them to meet the analytics needs.
Interview Question: What is the purpose of a surrogate key?
Answer: A surrogate key is a platform-specific representation of a business key

Modeling - Creating an Analytical (tabular) Model can be highly complex and encompass advanced modeling techniques that require a high degree of expertise with an eye to usability and best practices.
Interview Question: What are the two types of tables in an Analytical Model, and what are they used for?
Answer: Fact Table - used for measures. Dimension Table - used for storing attributes.

DAX - DAX is the analytical formula language used in Power BI. DAX can be very basic and straightforward, and DAX can be nearly infinitely complex, spanning N number of dimensions.
Interview Question: In DAX, how do you divide two numbers?
Answer: Expression = DIVIDE(Numerator, Denominator, else value)

Security - Zero trust Data Security is a foundational principle in all cloud data. Resources working in Business Intelligence need to understand the different layers of data security and various ways to implement them.
Interview Question: What is Row Level Security (RLS) and Object Level Security (OLS)?
Answer: RLS secures the row(s) of data to filter data for users. OLS prevents users from seeing data objects.

Report Experience - There is a big difference between building out Paginated Reports or Business Object reports used to dump data out to users vs. interactive reports designed to help dive action by highlighting the most desirable action.
Interview Question: Describe how you have built out interactive reports and the goals of these reports.
Answer: Listen for solutions that will drive action.

Size/Audience - Business Intelligence workers can build reports for 2-5 people or 50k. Report audience of team members, the C-suite, or the board of directors indicates the degree of attention, support, and conformity necessary.
Interview Question: Describe your audience (size and role)
Answer: Listen to solutions that align with your needs

PRO TIP - from Nigel Davison

"I always ask to see their work, or give them a dataset and some questions to answer. Before interview, you can screen out developers pretty quickly that way."

A career in Power BI is much like that of a graphic artist. Reviewing their portfolio of solutions can help you understand the level and quality of work that they produce.

PROFILES

These profiles are reasonably generic, where you may find that people fit into one or more of these different profiles. Use these as general rules of thumb to question yourself around strengths and weaknesses to see if these apply or are desired.

BUSINESS INTELLIGENCE GENERALIST

A BI Generalist knows a bit of everything. BI Generalists are great in a wide variety of places, knowing that they can skill-up in any given topic for a given purpose. You will find MANY consultants fall into the BI Generalist category. BI Generalists often build out solutions for 1-50+ people to consume.

PROS - BI Generalists are known for getting the job done and getting it done ASAP.

CONS - BI Generalists may not follow (or even know) best practices across any given skill, and thus pieces of a solution may require increased downstream support, maintenance, and upkeep.

BUSINESS INTELLIGENCE ANALYST

A BI Analyst will know a great deal about analyzing data and the business that they work with. The BI Analysts will be done rapidly to meet the needs of executives and leaders taking on new and changing challenges. You will find many data analysts have morphed into the BI Analyst category. BI Analysts often have a smaller, more targeted audience than a BI Generalist, but that audience can often be the C-level of an organization.

PROS - BI Analysts are great at understanding the business and building out excellent front-end solutions to meet people's needs.

CONS - BI Analysts may have low understanding or abilities around the Sourcing/ Transforming/ Modeling of data.

BUSINESS INTELLIGENCE ROOKIE

A BI Rookie has limited experience, if any when it comes to data and analytics. They are often fresh out of school, switching career paths, or have no idea what they are getting into. Understand that everyone starts as a BI Rookie. Depending upon what you are looking for, this may be ideal.

PROS - BI Rookies are readily available both on-shore and off-shore and can be found within schools and internship programs. BI Rookies get trained up to deliver per the needs of your organization.

CONS - BI Rookies will need a great deal of training and hand-holding. Make sure you have time and resources to do so.

BUSINESS INTELLIGENCE SPECIALIST

A BI Specialist will have a deep understanding of a tool (Power BI, Tableau, Spitfire, etc.) but may struggle to adapt and understand how to use the other tools.

PROS - Specialists are great if you have a primary tool in building out solutions quickly to address many needs in the tool they know.

CONS - Specialists can struggle when moving between tools. This struggle can cause problems for projects and delivery.

BUSINESS INTELLIGENCE ENTERPRISE ENGINEER

A BI Enterprise Engineer is similar to a BI Generalist. Still, where a BI Generalist may not follow best practices, a BI Enterprise Engineer broadly follows best practices across their solutions. A BI Enterprise Engineer is used to building and supporting solutions for hundreds or thousands of users.

PROS - BI Enterprise Engineers build good solutions to help drive your business forwards in data and analytics.

CONS - Good BI Enterprise Engineers are hard to find, can be very expensive, and can build solutions that require BI Enterprise Engineers to support.

DATA ENGINEER

A Data Engineer takes the Sourcing / Transformation/ Modeling of data to the next level. Data Engineers are masters in this space with a deep understanding of tools, processes, optimization, etc. Great Data Engineers will have deep knowledge and experience of the underlying data. Often the Data Engineer is brought in to complement the work of BI Generalist/ Analyst/ Enterprise Engineer, freeing them to focus on other aspects of Business Intelligence.

PROS - Data Engineers are a critical piece of any data and analytics platform.

CONS - Data Engineers are hard to find and expensive. They typically have little to do with front-end development.

CITIZEN DATA ENGINEER

A Citizen Data Engineer is primarily a sub-profile of BI Generalist/ Analyst/ Rookie tracks and is comprised of people who show an affinity to doing more around Sourcing / Transformation / Modeling and management of data. Citizen Data Engineers will often work with Power BI Dataflows and typical Power BI entities for general use.

PROS - Citizen Data Engineers can be an excellent source for Data Engineers and getting working code for Data Engineers. They think broadly and are more globally oriented.

CONS - Citizen Data Engineers often lack the skills and abilities of classically trained Data Engineers. This can lead to solutions that require more management and support.

ANALYTICS ENGINEER

An Analytics Engineer will delve deep into engineering the Analytic Modeling of data, DAX, row-level-security (RLS), refreshes, cache/direct query of data, and all things analytical modeling. An Analytics Engineer will work very closely with Data Engineers to build out and support the Enterprise Analytics Models. An Analytics Engineer is used to building and supporting solutions for hundreds or thousands of users.

PROS - Analytics Engineers are well versed in building large and highly performant analytical models.

CONS - Analytics Engineers are hard to find and expensive. They typically have a great deal to do with front-end development.

CITIZEN ANALYTICS ENGINEER

Like the Citizen Data Engineer, the Citizen Analytics Engineer is a subset of BI Generalist/ Analyst / Rookie tracks and is comprised of people who show an affinity to doing more around the modeling of data, DAX, RLS, and other Analytics Engineer capabilities. Citizen Analytics Engineers will often work with shared Power BI datasets used by several BI Generalists / Analysts / Rookies.

PROS - Citizen Analytics Engineers are an excellent source for Analytics Engineers. They think broadly and are more globally oriented.

CONS - Citizen Analytics Engineers often build models that need significant refactoring or have actual performance issues. This can lead to solutions that require more management and support.

POWER BI CITIZEN DATA SCIENTIST

A Power BI Citizen Data Scientist works with R, Python, and other tools to build out advanced analytical solutions to project what will happen in the future. Power BI Citizen Data Scientists will prefer to work with large wide tables.

PROS - Power BI Citizen Data Scientists will provide valuable insights into what is coming next and optimal paths forward.

CONS - Power BI Citizen Data Scientists are often clamoring to move on to full-time Data Science work and may be only around for a short time.

Did I miss any Power BI Developers profiles? Please let me know! Send an email to: PowerBIProfiles@kratosbi.com or reach out to me on either LinkedIn or Twitter.

The Right Tool for the Job- the Power Platform

Sat, 01 Feb 2020 03:36:14 GMT

Self Service and Low-Code / No-Code Framework for Success

As the need for technology continues to grow and expand across organizations, access to tools that enable teams to meet their changing technical needs is critical. At the same time, access to highly skilled technical resources continues to be an increasing challenge for every organization. Enter the Power Platform.

Self-Service + Low Code / No Code

The Power Platform was designed as a Self-Service + Low Code / No Code solution to enable everyone to solve their own technical needs. The Power Platform allows companies to rapidly prototype solutions to meet small needs and quickly adjust to meet the team's needs.

Power - Power - Power - Power OR the Four P's of Self-Service

This article is a quick guide to the tools in the Power Platform. If you are looking for more details, check out Microsoft's online Overview of the Power Platform .

POWER BI - You can use Power BI to quickly build out interactive dashboards and reports and distribute them across your company. To get started in Power BI, go to app.powerbi.com, register for a free account, and download Power BI desktop. Proper introductory training for Power BI is available through the Microsoft Learn site . If you are looking for training in Power BI, check out Microsoft's Dashboard in a day training program offered at Microsoft and partner offices across the globe. Reference

POWER APPS - Power Apps allows you to create both web and mobile applications quickly. To get started in Power Apps, sign up for the Power Apps Community Plan , and get started building out your first application. Microsoft offers online training, and overviews are available at the Microsoft Learn site . Look for hands-on training is available at Microsoft's App In a Day Roadshow . Reference

POWER AUTOMATE - Power Automate is what moves everything within the Power Platform. Need to email? Power Automate. Need to write a record to SharePoint? Power Automate. Getting started with Power Automate is the same process as getting started with the Community Plan for Power Apps with hands-on training at the App In a Day Roadshow . Reference

POWER VIRTUAL AGENTS - The Power Virtual Agents is the newest addition to the Power Platform. Power Virtual Agents allows you to easily create chat-bots that you can use to provide your customers with real-time AI-powered support. Setting up a Virtual Agent is easy to do and can be online in a few hours. Reference

Foundation of Self-Service

The Power Platform has several shared services that tie each tool together.

DATA CONNECTORS - Data connectors are used to securely connect your application to various cloud resources, databases, external sites, and other applications. Need to connect to a database, email, SharePoint? Data connector. Jira, Trello, Facebook, Twitter? Data connector. Keep in mind; these connectors allow you to move data both ways. Reference

PORTALS - The portals inside Power Apps allow you to create a safe tunnel to share data stored in the Common Data Service between internal and external resources. If you need to share performance analytics with a client, a Power Apps Portal is potentially the right solution, check with your security team to confirm. Reference

AI BUILDER - The AI builder allows you to create basic data science projects without a Data Scientist and leverage this AI across the Power Platform. AI Builder will enable you to perform binary classification, text analytics, and image recognition. Reference

COMMON DATA SERVICE - The Common Data Service (CDS) allows you to store data to be used by Power BI, Power Apps, Power Automate, AI Builder, and Portals. Reference

Easily Build Great User Experiences In Power BI

Sat, 25 Jan 2020 18:47:44 GMT

Power BI Seed Makes Navigation Easy

It's nearly effortless to overwhelm people with data, visuals, grids, and AI. Creating a collective experience for users to navigate your Power BI report quickly helps with the adoption of the report. A great UX/UI drives the impact of reports on the business.

Seed??? What the heck is a Seed?

Many of you may refer to this as a template file for Power BI development. You would be VERY wrong with that.

I prefer the term Seed because these are standards that you can grow your reports from. Seeds are highly functional entities that can be used by us 'Data Farmers' to grow analytical platforms quickly and easily.

Thus "Seeds".

Power BI Navigation Seed

The Kratos BI Power BI Seed is used to build out Power BI reports business partners can easily navigate. With these resources, you can quickly and rapidly build out sophisticated and powerful dashboards without having to spend a great deal of time on formatting, design, and training.

Navigation

The side navigation creates a single place for users to navigate between subjects within your Power BI experience. Breaking out by different subjects simplifies both the experience for users who consume the data AND makes Power BI development much easier to use.

Subjects

Overview - The Overview tab should contain a mixture of the subjects focusing one key metrics for each
Subject 1-4 - The Subjects tabs walk through a deeper dive into any of the specific subjects
Information - Use the Information tab to house meta-data about the Power BI report
How To - The How To tab should contain material that helps users understand how they can work with the report
Links - Include links to standard applications, SharePoint sites, or report documentation.
Definitions - Define the data in the report in terms that users are familiar with and can easily understand

These subjects navigate to different tabs allowing you to use different backgrounds, structures, and navigation. Good navigation also helps break out the selected items and groupings that you are working with on a given tab.

Deep Dive Navigation

Each subject has a standard navigation pattern allowing BI Engineers to tell a more in-depth story without having to have hundreds of tabs. The standard navigation makes navigating any given subject much more natural to report consumers.

Subject Breakouts

Summary - Key metrics and visuals that indicate performance for a subject
Breakout 1-4 - Breakouts on a subject that enable users to dive deeper into each subject
Key Influencers - The Key Influencers visual focused upon the subject to enable root cause analysis
Decomposition - The Decomposition Tree visual providing another means of understanding top or bottom performers
Q&A - A commonplace for users to ask their questions of the data. *

* Make sure you have filled out the proper metadata to enable Q&A and create sample questions that users can use.

Bookmarks - Not just for Harry Potter any more

The key to navigation in the Power BI Seed is the usage of Bookmarks and the ability to Show / Hide content. All of the Bookmarks and Items have common names that make finding items and making selections easy to modify for each user.

Tab Selection Bookmarks match the name of the Tab.

Example: Overview Tab, Subject 1 Tab, Information Tab, etc

Bookmarks

Each Tab has a set of bookmarks associated with them.

xxxx Tab - The landing page for the Tab
xxxx Info - The bookmark for overlays of the Tab for instructions and guidance for users
xxxx Breakout 1-4 - The bookmark for each of the sub-tabs that will contain visuals
xxxx Key Influencers - The bookmark for the Key Influencers navigation
xxxx Decomp - The bookmark for the Decomposition Tree navigation
xxxx Q&A - The bookmark for the Q&A navigation

Buttons

There are standard buttons that have the name of each Tab used to navigate between each page.

There are also sets of two buttons that help you navigate within a single tab. Each button will have a regular and 'light' version. These two buttons overlay each other entirely (Align left, Align top) and switch between show/hide when different bookmarks are selected.

NOTE - When you change the names of the Breakout buttons, make sure that you change the name on the button text for both versions of the button.

ACK - Don't DELETE that comment just yet!

Each Tab and each internal navigation has a text box comment that is used to build in your hide/show functionality quickly.

Leave that comment until you follow these steps.

Add visuals to the individual navigation
Group the visuals with the text box comment
Make sure the group follows the Hide / Show for the text box
Delete the text box

These comment boxes make adding visuals to each navigation much easier to get right.

Help Future You Save Time

Each report needs to include an Information tab that provides users with standard information about the report.

Report Details

The first section of each report should contain all of the critical pieces of information necessary for users to communicate with you about the issues that they are having with the report. Train your users to always provide this information along with screenshots of the issue to significantly shorten the time it takes you to troubleshoot and resolve any issues.

Data Refreshed Information

The next section answers the question every user is going to be asking about analytical data. When was this data last updated, and how up to date is this data? Just because a report updates today does not mean that the information is available through today. Often the report data is through the last day or month-end. Having a single place for users to go to identify the date the data refreshed helps users understand the data.

House Keeping Content

The last four tabs address some housekeeping tasks that many engineers can overlook, but are often really important for users if they are going to understand your report.

How To

Provide users with a guide for how they can best use your Power BI report. Include screenshots, write-ups, and even links to other applications or videos on how other users are using this report to impact business success.

Links

This Tab includes links to Team or SharePoint sites, applications, or web links that help users who are consuming your dashboard.

Definition

Include clear write-ups using the business language for the measures and attributes in your report. Business definitions help users understand what they are looking at or how this works without having to contact you directly. Save yourself some time.

Please write it down when you are building the report.

Power BI Admin

Be kind to future you, or the developers who come along well after you go crazy dealing with Business Intelligence and take some time to add comments around what it takes to run or build this Power BI report. Identify any known issues or things that do not work the way it should, or outstanding items.

This Tab is really for the BI Engineer. Use it.

I have spoken.

Grow Your Own Seed

The Kratos BI Power BI Seed comes with the PBIX file that includes all of the bookmarks, navigation, and embedded images highlighted in this article. It also comes with the background images used in the PBIX file as well as the PowerPoint used to generate the background images.

Your first step in using this Seed, after you play with it a bit, should be to modify the PowerPoint to switch over to your company's standard colors and logo, generate the background images, and then save them into the PBIX file for your internal Seed.

Kratos BI Power BI Seed + Kratos BI Excel Seed

Both the Kratos BI Power BI Seed and the Kratos BI Excel Seed are available on the Get Started with Power BI page on kratosbi.com . These are all free of charge, and I have created out of my love for data analytics and Power BI.

If you like this content, I highly recommend checking out Alluring Analytics authored by Chris Hamill . He has several other examples of this type of navigation that you can leverage.

I hope you enjoy these and find them useful for your Power BI content creation.

CMW

Power BI Custom Theme Creation

Sat, 18 Jan 2020 20:59:58 GMT

Get that look and feel JUST right...

Power BI has supported custom themes for the last several years. By building a custom JSON file, you could control everything from the default colors to the majority of custom features on every visual within Power BI.

There is sample code for how to control nearly everything in Power BI out on GitHub . If you are accustomed to working with JSON and you were ok experimenting with these JSON Power BI Themes, then I would highly recommend you build out a fully customized experience for your work.

A highly customized theme can pay off in spades by allowing you to focus on the story you are trying to tell, the dataflows, and the vast workload that many business intelligence people deal with.

The December update to Power BI desktop brought a large number of new themes and a HUGE change to how custom Power BI themes are created and who can build out a theme. No longer are custom Power BI themes limited to the few of us comfortable working with JSON files. Now there is an incredible GUI that helps anyone build out and create their theme!

There is still one hitch... just because you now have several new Power BI themes and can easily create your own, that does not mean that the theme you put together is going to look any good.

Don't worry.

I got you FAM.

The New Power BI Theme Experience

The new Power BI theme experience offers you a new default theme, new light, and dark themes, as well as the ability to connect to the online theme selection, customize your current theme and export the theme in the current report. Check out Microsoft's documentation on these new features.

Visually Testing Themes

Regardless of where the theme came from, be it one of Microsoft's default themes, a theme, you built from within the theme generator, or if you wrote custom JSON using GitHub sample code, you need to test out how your theme looks with all of the standard visuals within Power BI.

To make testing themes easier, I have put together the Kratos BI Visuals pbix file for you. This is the same file that I use to test out all of the custom themes that I have built. In one pannel, you can see all of the official visuals, the filter panel, and quickly change or update your theme based upon how visuals look.

This PBIX file is only really used to test out how each visual looks with an imported theme. If you are going the GitHub path, you REALLY need to be using this PBIX file to test out your themes. It can be effortless to make a small change to the JSON file and have a card or visual become nearly unusable due to color contrast issues.

Ensuring everything looks good with a theme is very important when working with a large number of reports at an enterprise or division level.

PRO TIP - If you have custom visuals that you use regularly, make sure you test these too. Unfortunately, many custom visuals play poorly with the themes in Power BI. Some work. Some don't. Best to know how they look.

I used the Kratos BI Visuals pbix file to test out the new themes and found some notable themes that I liked in both the Light and Dark categories.

Light Themes - Default, High Contrast, & Storm

The new Default theme in Power BI is a marked step up in the look and feel of the visuals created in Power BI. I don't know about you, but if I see another classic teal bar chart, I may lose my mind. Ugh. No one outside of Arizona likes teal. The new default theme looks sharp, has a good blend of colors, and feels very 2020. Well done Power BI team.

I found myself liking both the High Contrast & Storm themes. Both of these themes are going to be suitable for people who have issues with color blindness and the Storm theme plays intensely with the concept that most reports should have a unique color with the occasional highlight vs running the risk of looking like a kaleidoscope.

Dark Themes - Innovate, Bloom, Tidal

Dark themes are VERY tricky to get right. It's straight forward for the theme to make the charts and graphs unreadable and the general look and feel of the report to be challenging to work with. It's easy for dark themes to be too much.

Imagine my surprise when I found three Dark themes that had a perfect look and feel across all of the out of the box Power BI visuals!

I am a big fan of Innovate, Bloom, and Tidal. The colors pop, but not too much. The words are all legible, and these look highly use-able right out of the box. Take a few minutes, download the Kratos BI Visuals pbix and check these out. They look great.

Customizing Your Theme

The new Customize Your theme provides you the opportunity to customize large swaths of your Power BI experience.

The GUI breaks down the customization into:

Name and colors - control the name and the colors
Text - control the font, size, etc. of text within
Visuals - necessary control customization of all visuals
Page - control wallpaper and background
Filter pane - control the look and feel of the filter pane

When selecting your colors, look to your marketing department for a color or branding guide that they may publish. Much of the work of selecting just the right colors may already be done by someone who spends their career looking at different colors. Let those pros decide what is best when it comes to colors.

With that said, if something does not look good, then DON'T USE IT.

Colors may look good in print, web or applications. But if they don't look good in Power BI reports, then find something else.

PROTIP - Spend time with the Advanced tab looking at the color of the different levels of elements. The best way to do this is with the Matrix chart.

The text section is a miracle in and of itself.

If you are like me, you have been plagued by keeping all of your Power BI visuals in the same font. By default Power BI visuals rotate their font between Segoe UI, DIN, and Segoe UI Semibold... Ugh.

Now it's easy to lock all of your fonts into one Font family and not have to worry about a dozen settings on each visual.

Hip Hip Hooray!!!

PROTIP - If you are unsure what font to choose, pick Arial. It's not the default Power BI fonts, so your work will come off as customized, and it's a font that everyone is accustom to seeing.

While there are four tabs within the Visuals grouping, you cannot do a ton of things with the UI for Power BI themes. If you want more in-depth controls of your visuals, this is when you should look out at that GitHub repository.

There are a few items that you can be useful.

If you use a border that's the same color as the background and a 3-5 radius, all of your visuals have a nice bevel on them. Combine a white background with beveled corners on your images and slightly grey background, and you have a beautiful looking default Power BI report.

The Page grouping allows you to update the Wallpaper and the Page background. I am partial to a light grey page background on a bold - dark background.

Experiment and try out your look and feel.

If you went with bold - dark wallpaper, match the filter pain to that of your wallpaper. This will allow the filter pane to blend in naturally with the background and only call attention to it when you are leveraging filters.

A New Theme for Kratos BI

I was able to create a great new Power BI theme for Kratos BI by using the new Power BI Theme generator and the Kratos BI Visuals pbix file. Building out the new theme was straightforward and quite fun.

When I was all done with the theme, and each visual looked how I wanted them to, I exported the theme and saved this to my Power BI Theme space for use in other Power BI reports.

While I have spent many days working with the Power BI JSON theme, I found this process to be much more comfortable. The Power BI Theme generator didn't have all of the capabilities that exist when you hand-code the settings for each visual, but this entire process was MUCH more natural, and I consider that to be a fair trade.

Kratos BI Visuals

The Kratos BI Visuals and new Kratos BI Power BI Theme is available for you as part of Getting Started with Power BI.

To download by clicking on the image below and you will be taken to the page with all of the materials that is now available.

I would love to see the themes that you are able to create for your Power BI work. If you would like to share them with me, please feel free to send the JSON file to bi@kratosbi.com.

Thank you!

Analytics Life Cycle Overview

Sat, 18 Jan 2020 14:13:22 GMT

The Ever Evolving Ecosystem of Analytics

Understanding Analytics or Data requires an understanding of how each person interacts with technology, generation of data, how data is stored, how insights are made, and how these can be integrated into the Analytics Life Cycle.

The 'Analytics Life Cycle Overview' is a primer to provide context and understanding before going into a deeper dive into each of these subjects.

PERSONALITIES

Many different types of people work and interact with data regularly. From the CEO of the company to the front line help desk worker, the people who work with data and analytics runs the entire gambit. Understanding these different profiles and whom the audience is for a given piece of analytical work is critical to success.

Understanding and empathizing with your users is the starting point of the Design Thinking Process. If you are not familiar with Design Thinking, I highly recommend clicking on the image below. The Interaction Design Organization has a great article on the basics of Design Thinking that you should review.

APPLICATIONS

Users interact with applications in an ongoing and evolving manner. Analytics help users understand how, when, and where they can better work with applications. The interaction with applications, in turn, drives additional data and analytics.

The old expression "Garbage In / Garbage Out" continues to be true today. The data generated by applications need to be consumable by users to ensure that we minimize the garbage that goes into any system. The more we hide the data input by users, the worse the data becomes.

Old School and Hard Core programmers would talk about .Net, C#, or Javascript-based applications. If you are not a programmer, and you are looking for a Low-Code / No-Code solution, Microsoft's Power Apps platform would be your app development platform of choice.

STORAGE

Data flows from applications into a variety of different sources. From CSV's to NO-SQL databases to large enterprise data warehouses, data comes in an infinite number of shapes and sizes. These massive data lakes can hold treasure troves of information and insights IF anyone can access the data, understand what the data means and trust that the data is right.

Enterprises use both cloud and on-prem solutions to address storage. SQL Server, Oracle, DB2, Netezza, and Teradata are some of the most popular on-prem physical solutions that companies have been using for the last 20-30+ years, to address data storage. As companies look toward moving to the cloud, the storage options are shifting to Azure Synapse , Amazon Redshift , Snowflake , MongoDB , and many other data storage platforms.

Teams or individuals that need to store data for their Self-Service / Low-Code / No-Code solutions working with any of the O365 Office tools or the Power Platform should be using Microsoft's Common Data Model (CDM). The CDM makes storing data easy for business users and teams who need data to be available for applications and analytics in Power BI .

ANALYTICS

Analytics can help users understand and access the massive amount of information that is available today. Analytics can help measure data quality and provide a deeper understanding of how the data is stored and cataloged.

Over the last few years, Analytics has moved from being done by a centralized team that controls all data and reporting to a democratized resource that anyone can start to perform their analytics. Reporting platforms like Business Objects or SSRS where highly technical and required a great deal of programming knowledge and understanding to use. With the advent of Self-Service platforms like Power BI , Tableau , Qlik , and Looker , analytics has been shared between IT and the Business.

Changes to the O365 Office licensing program, and the significantly lowered costs of Power BI , have seen a recent explosion of Power BI being used by companies across the globe. This explosion of analytics across companies has it's pros and cons. The analyst in me loves the idea that more people are into performing analytics. The pragmatist in me is very concerned about the mistakes that are bound to come with the flood of people who do not understand analytics start to get into digging into data.

Fundamentally, understanding data through analytics enables people to work more with applications and with a higher degree of accuracy. When applications are tightly integrated with data and analytics, it encourages people to ensure that the data that they enter into their applications is cleaner and better.

AUTOMATION

Automation is what connects everything. While some steps may be more manual than automated, there are degrees of automation that exist everywhere. The tighter applications, data, and analytics are integrated, the better the entire ecosystem is and becomes. This integration allows users to see when and where weak data entered the system and motivates them to go back in and correct it.

System integration can be highly complex and take significant work from a centralized IT team. Tools and applications like Azure Data Factory , Informatica , IBM's DataStage , and SnapLogic perform many of these functions for large enterprise IT teams.

Users in the Self-Service / Low-Code / No-Code environments look to Power Automate , Power BI Dataflows, and PowerApps Dataflows * to integrate applications, data, and analytics.

*At the writing of this blog, I can honestly say I have no idea why both Power BI and Power Apps have different Dataflows or what the difference is for either of them. If / when I find the difference, I will share it with you. I guess that these teams are rolling out updates multiple times a week, and the Power BI and Power Apps teams need to merge the codebase between the two and that it was better to deploy two solutions NOW vs. waiting for them to be merged. Just a guess.

SUMMARY

The Analytics Cycle is continuously evolving to address changes in business, the market, industry, technology, resources, and a vast array of other external factors. New applications spring up every day, creating new sources for data and requiring additional analytics. As these changes continue to evolve and adjust, understanding these focal points can help bring order to chaos.

Comprehensive BI Solutions- Power BI + Excel

Thu, 16 Jan 2020 19:40:21 GMT

You got your Power BI in my Excel!?!?

I know, I know, I know... no one wants to see the spreadmarts blossom and grow. IT doesn't want it. The business doesn't want it. Heck, Analysts don't want it. Trapping data in hundreds of millions of spreadsheets across your network is a horrible way to manage data for any organization.

Yet, there is a reason that every organization continues to fight the fight against that ever growing spreadmart ecosystem.

Spreadmarts grow because they work to address short term needs of analysts or the business that IT cannot simply handle. The needs for spreadmarts are being addressed by Self-Service Power BI, but even that does not necessarily address all of the needs of business.

We now have the ability to integrate Enterprise Datasets hosted in the Power BI service with the work analysts do in Excel. The combination of Power BI and Excel offer us the opportunity to enable analysts to continue to run and gun in Excel without having to worry about data integrity issues common within spreadmarts.

I am not going to get into too much detail on the GREAT new security features that Microsoft and Azure is rolling out, but soon you wont even have to worry about files being emailed off of your network with protected information. Insane right???

You Never Know When an Excel File Becomes a Production File

For a long time, Excel has been a hidden gem in the Microsoft Business Intelligence (MSBI) portfolio. While there are many great books on the amazing things that you can do in Excel, many people didn't realize that Excel was the birth place of Power Query (M), Power Pivot (xVelocity - Tabular engine) and Power View. These three pieces of software have evolved into the Power BI platform.

This Excel Seed has evolved over the course of more than a decade of working with Power Query, Power Pivot, Power View and Power BI.

By using this Excel Seed, you retain the ability to use Enterprise Datasets, while allowing analysts to use all of their existing Excel features. While Power BI is awesome, and the future for analytics, Excel has been around for over 30 years and a slew of features and capabilities that Power BI simply does not offer.

On countless occasions, I have found that quick bit of analysis that I did in Excel became a guiding benchmark for teams, divisions or even entire companies or industries. Using these Excel Seeds for any Excel work ensures that the work you are doing has a polished look and feel, becomes a common UI, and has a large number of features that you don't have to recreate in each excel file.

"Three may keep a secret, if two of them are dead" Benjamin Franklin

Tabs within the Kratos BI Seed fall into three categories

Visible to Users
Optional for Users (Yellow)
For BI Developers (Red)

When you create a new Seed, take the time to hide any of the tabs that you will not be using or sharing with your users.

The Excel Equivalent to The Hitchhiker's Guide to the Galaxy

It's very easy to grow a large number of tabs within Excel for different purposes. The analyst that created the spreadsheet will know how it works, but navigating someone else's work can be very difficult. The Index tab works as a guide through out the workbook to the various tabs with a description of what each should be used for.

Feel free to Add more rows or columns to your Seed. It's Excel after all! Super easy to customize and make your own.

Who Doesn't Love a Sandbox?

The Sandbox is the single most powerful tab and mechanism within the Excel Seed. The Sandbox contains a pivot table linked to your data source that allows users to connect to the data, drag and drop fields, and perform all kinds of additional measurements.

The Sandbox is used to create new tabs, build out focused and detailed charts and graphs that create specific analytical views of your data.

Look for more detailed write-ups and videos on many things that you can do with this Sandbox.

Webster aint got nottin on you!

Use the Definitions table to document the data that exists within your Dataset. This is HIGHLY effective and necessary if you are using Power Query and Power Pivot to shape the data from within Excel.

If you are using data from an Enterprise data source, then simply hide the tab and ignore it!

Enable People by Telling Them How To Use Your Work

Use the "How To" tab to document how users can use your Excel Seed. This is an excellent place for screenshots, narratives, and specific use cases that are addressed within the Seed.

This Can't Be Right!

While the Sandbox is the most powerful tab for the vast number of things that it can do, the Balancing tab is out of the box one of the more useful concepts in the Seed. The Balancing tab is designed for you to be able to validate the data in the enterprise cube vs a validation data set. The Test Values calculations allows you to easily see if your dimensions are not matching or if any of your values are off (and by how much)!

This is the BI Testing Team's most useful tab. If you are in testing, or know someone in testing, please please please, spend a great deal of time with this.

Move at the Speed of Business

As a Seed grows and evolves, you will want users of the Seed to know about changes that you have made (or are planning to make). The Updates tab was designed as a single place to document the various updates and enhancement that you make to the Seed.

On Brand and On Message

Many companies have a Branding guide of approved colors and logos to be used in any official materials published by the organization. The Branding Tab is used to capture the branding colors, themes and logos to be used within the Seed.

When you are customizing the Kratos BI Seed for your own use, you should start working on getting this branding in-line with your organization.

This Bud's for You

The BI Admin tab is used to contain all of that stuff that you may want to keep hidden from users. Use this tab as a central place for lists, reference cells, instructions for updates, etc.

When You Need A Clean Slate

Use the Template tab to create a new clean tab for any other documentation, manual imports, data or any other needs you may have for a clean tab without a pivot table on it.

Don't forget those Random Queries

Hopefully you are adding any random queries into Power Query.

If you are manually running a query and copying the data into the Excel file (for what ever reason you have), paste the query into this tab along with the necessary information for someone else to be able to run the query.

Really though... best to put the query in Power Query.

Summary

The Kratos BI Seed combines Power BI and Excel to be used by analysts of all shapes and sizes for a million different purposes.

I hope you find this Seed helpful in leveraging the power of Power BI and Excel.

Iterative Business Intelligence Development

Thu, 09 Jan 2020 00:46:49 GMT

Business Intelligence and Agile Development go hand in hand

Earlier this week, we published the BEST way to get started in Power BI starter kit that includes:

Kratos BI Data - A sample dataset used for:

Demo of tabular model best practices
Testing out DAX statements
New features in Power BI

Kratos BI Sample Report - A sample Power BI report used for:

Report Best Practices
Layout examples
Examples of bookmarks
Examples of navigation

Kratos BI Power BI Theme - A sample custom Power BI report them used for:

Best practice for custom Power BI theme
Match corporate colors
Consistent look and feel
Starter file for those new to JSON file formats

Kratos BI Backgrounds - A PowerPoint file filled with sample layouts used for:

Report layout best practices
Report performance
User understanding

The Power BI starter kit is an excellent resource for testing out new features and build out as part of writing this blog. Look for updates to this starter kit to correspond to the updates on this blog.

First Report Feedback and Updates

The feedback for the Power BI starter kit was overwhelmingly positive. Thank you.

There was some feedback on how the Kratos BI report could be improved to look better.

Alex Ooi ACMA wrote to me and offered two suggestions that were apparent improvements to the report. Thank you Alex!

Pop Up Card

Alex's first piece of feedback was on the State card that would pop-up over the map visual on the Sales page of the report.

The blue background with the black text was tough to read. Alex shared a link to a great blog he wrote on the value of color contrasts and an excellent resource for selecting colors that work well together .

Alex played with the colors a bit and eventually recommended changing the background to white. It was the easiest to read and kept the focus on the pop-up data.

Changing the pop-up card to a white background was a significant improvement (see the screenshot below).

Bubbles tickle my nose

Alex's next recommendation was to do something about the bubble charts on the detailed Reseller and Customer Sales pages. On the bubble charts, the size of the purchase and a large number of items visualized made it hard to read, select a single value, or understand if there were any patterns or outliers.

Removing the size measure increased the usability of the bubble chart. Once the change was made, individual resellers became apparent, and it was clear to see that there were growth patterns within the Value Added Resellers and the Warehouse reseller, clients.

These changes (and a few less noteworthy) have been made to the Kratos BI Sample Report and are now available for you to download.

If you have any other comments or thoughts, please let me know! I will update these materials and share with others the best practices that come from the community.

Dataflows and Datasets in Power BI

Sat, 04 Jan 2020 00:06:32 GMT

It's Time to get your FLOW on

Dataflows and Datasets are some great new features that you want to leverage moving forward. They are mighty and enable several great features that add resiliency, ease of access, lineage, auto-documentation, and several other benefits that enhances ROI to each business unit.

What are Power BI Dataflows? Power BI Dataflows allow you to define individual tables that can be used in different data models out in Power BI. Some examples would be a Product, Employee, Date, or Transactions table that you would want to use the same information in different data models. Dataflows allow you to load the data from the source system out into the Power BI service a single time, transform the data once, and then consume the data many times.

Best Practices

Create a Power BI Dataflow

Dr Dataflow himself, Matthew Roche explain the difference between Power BI Dataflows and Power Platform Dataflows in this great video that he has over at BI Polar .

What are Datasets? Datasets are a combination of tables, joins, and measures that can be used to build out Power BI reports. Any time you build out a Power BI report, you are building a dataset. Radacad has a great article on what is a Dataset and how can you use them to improve your reporting and performance.

https://radacad.com/power-bi-shared-datasets-what-is-it-how-does-it-work-and-why-should-you-care

Why should you use a common Dataset? Excelerator BI published an excellent article over a year ago about the value of having a "Golden Dataset" with an easy to understand examples. Check out the first two sections in this blog ("The Problem- Too Many Data Models" and "What is the Golden Dataset"). The rest of the blog provides a detailed technical guide that is nearly 100% out of date and should be ignored.

https://exceleratorbi.com.au/new-power-bi-reports-golden-dataset/

What are Promoted & Certified Datasets? Once you start to build out datasets that can be used for multiple reports, you can category the dataset as either "Promoted" or "Certified". Best Practice is to mark a dataset that are used by a team or division as "Promoted" and any dataset used by the enterprise as "Certified".

Our friends over at Guy in a Cube have a great video about Shared & Certified datasets.

TOP 17 Power BI Desktop Updates for 2019

Wed, 01 Jan 2020 16:34:24 GMT

There were just TOO many to choose from (251 to be exact)

I thought that this AM I would Quick dash off a blog post on the top features of Power BI for 2019. I mean, how many could there possibly be? I can remember many thrilling updates coming through, but I had no idea that there were THIS many updates last year. No wonder this is a full time job!!

Good thing I have Power BI to help me!!!

After gathering the details on the updates for Power BI Desktop for 2019, by my count, there were 251 updates in 2019. I rated each update on a scale of 1-100. The lowest value I gave any item was a 50 due to the limited use of the item. Items that I felt were base line expectations for a BI platform, I rated in the 70's. Features that I was delighted to use I put in the 80's, and features that brought some degree of game changer to the BI world I rated as 90-100.

The Power BI Desktop team released consistent numbers of Features each month with December being the highest number of features, and July and August being the lowest number of features released. NOTE: I could not find any details on a January 2019 release of Power BI Desktop.

When I broke out the features by Subject area, it was no wonder to see that Reporting, Data Connectivity, Visualizations and Analytics had the absolutely most number of features released in 2019. When looking at the Average Rating for the features, Other, Data Preparation, Modeling and Analytics were the top spots. This would mostly be due to the number of game changing features released in 2019 in each of these Subjects (and only partially due to my biased love for analytics).

Looking at the features by month , quantity and average rating, I found it interesting that July, with the lowest number of features, had the highest average score for the features released.

In looking at the details of what was released in July, it's clear WHY July dominated the ratings chart. With Aggregations going GA, Power Apps visual getting certified, big updates to connectivity, and long waited for conditional formatting improvements all getting smashed into one month.

Enough of the analysis!!! Lets get on to why you clicked on this link.

TOP 17 Power BI Desktop Features of 2019 (by )

ANALYTICS
Aggregations- Generally available July
Drillthrough across reports April
Key Influencers- General availability August
Key Influencers visual (preview) February
The new Q&A visual October

DATA CONNECTIVITY
Azure Data Lake Storage Gen2 connector is now generally available December
Common Data Service connector is now generally available June
New Power Platform category within Get Data August
Power BI dataflows connector now generally available April
Power Platform Dataflows connector December

DATA PREPARATION
Query diagnostics October

OTHER
Modern ribbon (preview) November
Query performance improvements for DirectQuery models September

REPORTING
Decomposition tree visual (preview) November
Export current theme December

VISUALIZATION
PowerApps visual is now certified July
PowerApps visual now generally available September
PowerApps visual is now included by default October

Here are the Power BI Desktop posts used for this analysis. Enjoy!

January- no desktop update in January

https://powerbi.microsoft.com/en-us/blog/power-bi-service-and-mobile-january-feature-summary/

February

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-february-2019-feature-summary/

March

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-march-2019-feature-summary/

April

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-april-2019-feature-summary/

May

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-may-2019-feature-summary/

June

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-june-2019-feature-summary/

July

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-july-2019-feature-summary/

August

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-august-2019-feature-summary/

September

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-september-2019-feature-summary/

October

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-october-2019-feature-summary/

November

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-november-2019-feature-summary/

December

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-december-2019-feature-summary/

TOP 3 Tips for Faster Power BI Reports

Sat, 28 Dec 2019 16:49:18 GMT

Want your Power BI Dashboards to run faster? Follow these Tabular Best Practices.

The best practices for Fact/ Dim relations within Tabular models are based on the Kimball DW design. This design has been integrated into the VertiPaq compute engine in a manner that maximizes performance over large datasets and many dimensions and is usable by business users as well as advanced technical resources.

While the flexibility of the VertiPaq engine enables usage of almost any data model, there are specific techniques within Kimball that can ensure the highest performance and ease of implementation within this compute engine.

TIP #1 INTEGER BASED COLUMN STORE

The VertiPaq engine is built around and highly optimized for integers. This has led to the following best practices within the largest and highest performing models:

Fact Tables only contain Integer Values
All joins are Integer Based (recent updates may have tuned to enable GUID based joins to perform as effectively- I have not seen this in action)
Date joins as Integers in the format of yyyymmdd have been highly optimized and tuned for very complex and sophisticated time-series analytics (more on this later)
Minimize the size of string values (even in dimensions) as much as possible- 255 is good, 127 is better, 31-63 is better still
Note- there are new features and new techniques that would enable people to ignore these string limitations. These are very new and proper guidance is not widely known and usage is not obvious to people. We will be able to easily deal with this in the future.

TIP #2 DIMENSIONAL RELATIONS

The way dimensions are broken out in-memory and the calculations are stored in Cache based upon the joins and best performance can be derived from optimizing this cache. The cache uses concepts of HOT and COLD for where and how the data is stored, with HOT cache providing the fastest responses and COLD taking a little longer. To maximize what can remain in HOT cache, we follow the following best practices:

Integer based Joins
Minimize Cardinality of Dimensions
Highest performance is obtained by keeping Cardinality of a DIM sub 127k (above this performance will start to degrade)
Move Type 2 attributes to Fact Tables and separate into a different dimension

TIP #3 DATE DIMENSION

The Date Dimension within the VertiPaq engine has been specifically and highly tuned for time-based analytics to the point where there is no other engine that offers the same level of dynamic time-based analysis to business units and data scientists alike. If you are unfamiliar with what the Tabular model can do with Time based analytics, I highly recommend checking out the many articles Marco Russo has published on time intelligence (https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/)

Date Dimension based off of Datekey integer join of yyyymmdd
Dynamic calculations and breakouts by Date Dim and features and functions within the dimension
A standard DateDim makes enabling these features much easier
This DateDim can also be built to minimize data refreshes, partition cubes and reduce our overall cloud spend while improving performance.

TOP 10 New Year's Resolutions to help you grow with Power BI in 2020

Fri, 27 Dec 2019 20:35:38 GMT

Sharpening the ax is always a good idea

" Invest in yourself. The one easy way to become worth 50 percent more than you are now at least is to hone your communication skills--both written and verbal. If you can't communicate, it's like winking at a girl in the dark--nothing happens. You can have all the brainpower in the world, but you have to be able to transmit it. And the transmission is communication. " Warren Buffet

The New Year is filled with resolutions. Most resolutions fall under the traditional categories.

This year I AM going to lose 20 lbs.

In 2020 I will carve out time to finish Mad Men.

I am going to call my mom more.

If you are looking to learn more about Power BI, here are the TOP 10 items that you can set as a resolution for 2020.

#10 Watch the monthly Power BI release video

Each month the Power BI team puts out documentation and a video that covers the big features for the month out on YouTube . This year, make an effort to watch these videos to see what new features exist in the Power BI world.

#9 Attend a Dashboard in a Day Workshop

The Dashboard in a Day program helps students get up and running in a single 8-hour session. These are free courses usually taught in your local Microsoft office. Find out about times and locations here .

#8 Teach someone else about Power BI at least once a month

The best way to learn any subject is to teach it to someone else. Take some time each month to help someone in the Power BI community build-out or support Power BI content.

#7. Follow along with Patrick from Guy in a Cube on 2 minute Tuesdays

Every Tuesday (mostly) Patrick Leblanc does a short 2 minute (ish) video where he shows you how to perform some quick tricks in Power BI. In 2020, take some time to follow along with Patrick to see how you can add these features to your reports.

#6. Attend a local Power BI User Group

There are Power BI User Groups (PUG) all over the world. These groups bring together local Power BI professionals for training, speakers and to exchange ideas. These groups are a great place to get connected and advance your Power BI knowledge.

#5. Integrate Power BI, Power Apps and Power Automate in 1 report.

You know you want to... you know your users want this too... you need to learn how to integrate Power BI, Power Apps, and Power Automate easily. It's super easy. Once you learn how you will be hooked for life! Docs , GuyInACube , PragmaticWorks

#4. Go to the Microsoft Business Applications Summit

The Microsoft Business Application Summit (MBAS) is the biggest event of the year for Power BI developers. In 2020 MBAS will be in Anaheim CA April 20-21. If you are in California, this is a no brainer. Everyone else, start talking to your manager about the budget!

#3. Create more engaging Power BI reports.

Do your Power BI reports look just like those old out-dated Business Objects reports? Do they lack pizzazz? Take a look at the new Sales & Returns Sample Report that was just published by Microsoft. The Sales & Returns Sample Report is an excellent example of what you can do in Power BI.

#2. Learn more about Aggregate Data Tables in Power BI

Performance with large datasets is always a challenge. Power BI makes chewing through all but the most enormous datasets easy and fast. What about those times when you are dealing with one of those enormous datasets? That's when it's time to look at Power BI Aggregate tables . If you need to learn how to use these, there is no better expert than Phil Seamark. Phil's blog covers Aggregations better than anyone else I have seen.

#1. Learn DAX

Learning DAX should be the first thing that you learn about Power BI. Unfortunately, Power BI is too dang easy to use. Many people work with Power BI for years without realizing the gold mine that they are sitting on. There's no one better to teach you DAX than the Italians at sqlbi.com . You can also pick up the book " The Definitive Guide to DAX - 2nd Edition " by Marco Russo and Alberto Ferrari.

The Data Gods of Light & Color - Mike & Seth

Sun, 22 Dec 2019 21:31:15 GMT

Making Power BI Look Good- One Report at a Time

LinkedIn Profile Michael Carlo: Link

LinkedIn Profile Seth Bauer: Link

Web Site: Link

Twitter: @Mike_R_Carlo and @Seth_C_Bauer

Data God Qualifications:

Not only are both Michael and Seth Microsoft Power BI MVP's, but they have transformed Power BI work into an art form. The content they build are works of beauty and style. Turning a mere report into a Business Intelligence Application. The best part? They do this in a way that others can duplicate, imitate, learn from, and extend. The Power BI community broadly has benefited from the work of both Michael and Seth.

Gods of Light & Color

Power BI Tips has two excellent Theme generators that can be used to bring consistency and customization to your reporting. Why go into Git and generate the theme code yourself, when you have these two excellent resources.

Theme Generator - helps you create an underlying Power BI theme with up to 8 of your company's brand colors.

Report Theme Generator - helps you bring consistency and customization to all of the standard visuals within Power BI.

Gods of Custom Visuals

Michael and Seth also built out a tool that can generate a Custom Visual for you without having to learn how to write Javascript. Hooray!

The Community of the Gods

Seth and Michael do a great deal to help out the Power BI Community at large. Not only do they provide all of the great resources listed above, but they also manage the Milwaukee Power BI User Group .

Seth and Mike also host the blog content for many leaders and visionaries in the Power BI space .

(If you don't know Phil Seamark or Steven Campbell... well, I guess I may have to write a post about them too.)

Data God Fun Fact #1:

Unlike most Microsoft MVP's, who are nearly all consultants, Mike received his MVP designation when working as an employee at Johnson Controls. Mike's love for all things Power BI was a beacon of like for those MVP nominations.

Data God Fun Fact #2:

Seth has a unique background too. Seth's background includes being a certified sommelier and has his Bacholer of Arts in Art! Now we know why Seth's Power Bi reports look so good! Seth approaches report design with the eye of an artist at work.

The Data Gods of Knowledge - Guy in a Cube

Tue, 10 Dec 2019 00:00:00 GMT

Everyone now... "YOOOOOOOOOO!!!!"

LinkedIn Profile Adam Saxton: Link

LinkedIn Profile Patrick Leblanc: Link

Web Site: https://guyinacube.com/

LinkedIn Site: Link

YouTube: Link

Twitter: @GuyInACube

Data God Qualifications:

With over 4 million views on YouTube, there are probably no more popular Power BI celebrities than Adam Saxton and Patrick Leblanc.

If you are doing anything with Power BI, odds are you have watched a few dozen of the videos by Adam and Patrick. With new videos out on Mondays, Tuesdays, Wednesdays, and Thursdays, either Adam or Patrick are popping up on your YouTube feed regularly.

Roundup the Gods

On Mondays, Adam provides a good rundown of updates to the Power BI platform, useful blog posts, and covers any news impacting the field. With the many changes and updates continually hitting the platform, this is an excellent way to keep up with what is going on with Power BI.

Two Minute Tuesdays

"You guys know how I like to do. Let's head over to my laptop."

In these UBER popular Tuesday editions, Patrick will quickly show you how to use various Power BI techniques. The best part, his focus on the "clicks" makes these videos infinitely re-watchable and can help you work through each topic.

Top 5 Best Videos

While Adam has the most videos posted to the channel, Patrick is dominating in the most popular video category at 4 of the top 5.

#1. Dynamic Power BI reports using Parameters

#2. Embed a dashboard from Power BI into your application

#3. Build a Slicer Panel in Power BI and take it to the next level (not just because of the t-shirt)

#4. Data modeling best practices

#5. Working with Time

Data God Fun Fact:

Adam once ditched a night tour in Washington DC to go see a Star Wars movie on opening night.

Can you guess the Star Wars Movie?

History:

The first Guy in a Cube video was uploaded on December 4th, 2014. Each video has embraced nerd culture by showing off Adam or Patrick's eclectic t-shirt collection, Lego, or other toys.

Delivering Great BI on a Deadline

Sat, 07 Dec 2019 20:18:25 GMT

You gotta work!!!

Every great developer becomes saddled with an infinite demand for their work. On any given day, we are often triple, quadruple, or quintuple booked in meetings. The ability to do many things at the same time is a crucial factor in being successful. Multi-tasking in the tech field requires a deep understanding of tech and the ability to learn as you go.

The #powerbi template method pioneered by Chris Hamill at Microsoft blew me away when I first saw it a few months ago. I was infinitely more impressed yesterday when I went to try it out.

Here's what happened:

Typical Business Intelligence situation

A senior leader comes in looking for a list of challenges to some POC work done for executives, and the work needs to be done by the end of the day. For a seasoned BI Pro, it wasn't anything out of the norm and something that could be banged out without any issues.

An hour later, everything seemed done.

Model changes: Check.
New measures: Check.
Validation: Check.
Automated loads: Check.
Visual adjustments: Check.
New visuals: Check???

The new visuals introduced some challenges. The report was already bordering on having a cluttered look to it. These new charts pushed the report full into the cluttered category.

Crap.

Cluttered is no good for our executives. Something had to change fast if it was going to be delivered by the end of the day.

I made a decision to change the layout of the report radically, figuring that if the UX is good enough, then users will not require any training or additional explanations.

Over the next hour, I followed the techniques in Chris Hamill's blog posts on Background Concepts and In-Page Navigation . The process was much easier than I anticipated, and the results were far more beautiful than I had hoped.

Now the report isn't cluttered, scrolling is gone, and four huge pages are combined into a single page.

Better yet, the report was delivered on time and the new UX is a big hit with the users.

Thank you Chris Hamill!!!

I highly recommend trying these techniques out.

Background Concepts

Background Gallery

In-Page Navigation

The Data Gods of Magic - The Italians

Mon, 02 Dec 2019 00:00:00 GMT

The old gods of DAX and Power BI

LinkedIn Profile Alberto Ferrari: Link

LinkedIn Profile Marco Russo: Link

LinkedIn Company Site: Link

Web Site: Link

Blog: Link

Data God Qualifications:

Alberto and Marco are the Original Power BI GODS. They were there when the magic was born. When the first words of DAX were spoken, they were spoken by Alberto and Marco. Of all of the Data Gods, these two are the Oldest and Most potent.

Should Cthulhu ever break his bonds, I am confident that the Italians would either banish him to the nether realms with a couple of quick DAX statements or sit down with him for a beer and discuss managing the multi-verse and disparate timelines.

Publications

These guys figuratively wrote ALL OF THE BOOKS on DAX, Power BI, Tabular Models... I mean.. They wrote them ALL .

If you are going to work in this field, then you need to buy ' The Definitive Guide to DAX '.

Top 3 DAX Research Options

When you need to know how to do anything with DAX, you have three options:

Option 1 : Google your question and select the response that takes to you sqlbi.com

Option 2: Start at the sqlbi.com blog

Option 3: Look it up. Didn't you buy ' The Definitive Guide to Dax ' yet?

Tools of the Gods

These guys built ALL of the tools that you need to work with MSBI*. If anyone knows of a tool created by anyone other than the Italians, I need to know about it because these guys are the only place that has anything.

DAX Studio - Used to write, execute, and analyze DAX queries.

VertiPaq Analyzer - Understand what storage in your tabular models.

DAX Formatter - Make your DAX look great.

Plus many more

* Apparently the Gods of Magic didn't build EVERYTHING... They had help from Darren Gosbel on DAX Studio, and some of the other tools.

Training from the GODS

Marco and Alberto also do tons of training all over the globe . They speak at conventions and are available for private instruction.

If you need to know how to work with anything Power BI, Power Pivot or Analysis Services, these guys are the ones to reach out to.

Data God Fun Fact:

Neither Alberto or Marco have ever worked for Microsoft as employees.

I know, right!?!?

History:

Alberto and Marco are in all of the chronicles for everything Power BI, Power Pivot, Power Query, Power View... I mean, they have been around for a VERY long time.

UX/UI Blog Posts

The Gods of Magic are the original DAX blogers. These posts are the holy words of all things Tabular.

The Data God of Form - Chris Hamill

Sat, 23 Nov 2019 17:55:11 GMT

It's all about the User Experience

Linked In Profile: Link

Blog: Link

Data God Qualifications:

Chris may be new to the position of Data God, but his place amongst the gods is unquestionable. The UX/UI that Chris has created in Power BI is second to none and inspires others to do more.

What makes for a great Power BI UX/UI?

Great Power BI UX/UI takes a great deal of work. You must know your audience, know how to work with your tool, gather feedback, fail fast, and iterate often. The big key is focusing on how you can avoid overwhelming consumers of your report readers.

Top 8 Report Design Tips

The God of Form recommends that you focus on 8 design elements to be useful in your reporting:

Margins - Don't let visuals go to the borders.
Distribution - Making sure all of your visual elements are evenly distributed can make reports much easier to read and understand.
Alignment - Visuals that are not aligned with each other can be extremely distracting. Always take the time to line everything up before you publish it.
Branding - Make sure that you use appropriate branding throughout your report development.
Coloring - Consistent colors help focus people on the story. Try to limit the number of different colors. We're not at a circus here.
Consistency - Having a standard layout, fonts, sizes, and positioning can help make reports more consumable.
Accessibility - Taking the time to make things accessible can really help broaden the reach of your analytics. Many people are color blind. Don't solely rely upon colors to tell your story.
Backgrounds - This is Chris's secret sauce. Check out his multiple blog posts on the subject.

Data God Fun Fact:

Every time Chris visits Wisconsin, someone takes him to Cafe Benelux. Not 100% sure how this happens... maybe a god of Cafe's has something to do with this? Regardless- if you are in Milwaukee, check out Cafe Benelux . It's very good.

History:

Chris has only recently joined the ranks of Data Gods. Previously, Chris was a Finance Analyst for Microsoft, and before that, Starbucks.

Notable UX/UI Blog Posts

Background Concepts for Power BI - Link

User-Friendly Drill Down Experiences - Link

Overlays for True Self Service Reporting - Link

The Data God of Dataflows- Matthew Roche

Sat, 23 Nov 2019 17:03:34 GMT

Some call him "Doctor Dataflow"

Linked In Profile: https://www.linkedin.com/in/matthewroche/

Blog: https://ssbipolar.com/

YouTube Channel: https://www.youtube.com/channel/UCpsilPn-2qFlrYYuvyFkpPQ

Data God Qualifications:

"Doctor Dataflow" himself, Matthew Roche, is the Data God of Dataflows. Matthew is the program lead at Microsoft leading the development of anything and everything related to Dataflows.

What are Dataflows?

Power BI Dataflows allow users to self-serve data acquisition and management. Dataflows distributes a great deal of storage capabilities out to users of Power BI.

Need to mash up a SharePoint list with an enterprise Dataset? Dataflow.

Need to use the same custom entity across many Datasets? Dataflow.

Data God Fun Fact:

This Data God is also the master of the sword. Look the &^$* out when Mattew gets his hands on his trusty montante.

History:

Matthew is a Power BI Client Advisory Team (CAT) member at Microsoft. He has been a Program Manager at Microsoft for over a decade, working with such products as Power BI, Dataflows, Azure Data Catalog, Information Services, SQL Server, and Microsoft Learning.

Dataflow Documentation

Power BI Blog Introduction - https://powerbi.microsoft.com/en-us/blog/introducing-power-bi-data-prep-wtih-dataflows/

Official Documentation - https://docs.microsoft.com/en-us/power-bi/service-dataflows-overview

Dataflow Videos

Announcement of Dataflows - https://www.youtube.com/watch?v=0bJpCVj3JfQ

Guy in the Cube 'Power BI Dataflows: Where does it fit in?'- https://www.youtube.com/watch?v=lXq9GDfpv0Y

Creating Dataflows - https://www.youtube.com/watch?v=yUGkH_dNepA

Sharing Dataflows - https://www.youtube.com/watch?v=r65B-yLEahc

Analytics on Big Data

Sun, 11 Aug 2019 16:45:42 GMT

Why you need to use Power BI Aggregations

Over the last 20 years, data sizes have exploded and technology has continually adjusted to come up with new ways to deal with these large data sets. Databases (SQL Server, Oracle, Sybase, DB2) with hundreds of GB of data were augmented by Data Warehouses (Netezza, Teradata, SAP HANA, Redshift, Snowflake, SQL DW) with hundreds of Terabytes of data. These Data Warehouses then added Data Data lakes (ADLg2, S3) with Petabytes of data to the ecosystem.

Power BI reports and dashboards are designed to be able to deal with any size data. You just have to know when and where to use certain techniques to make these work. To do this, you need to understand how Power BI works.

Power BI Engine Basics

Power BI Import Mode

Power BI was designed to run off of in-memory cached data. Cached data can execute tens of thousands of queries across hundreds of millions of records with a sub-second response. This allows users of reports to be able to really interact with the data in real-time. Because the data is in the cache, the time it takes for the engine to move through the data is insanely fast.

Import Mode PROS:

Super Fast Interactions

Import Mode CONS:

have to cache data = size constrained to the size of Cache

Power BI Direct Query Mode

Direct Query addresses the issue with the size of data by not having to cache the data. This enables Power BI to report on data that exceeds the size of data that can be cached.

Direct Query Mode PROS:

Size can exceed the size of the cache

Direct Query Mode CONS:

Performance is poor because the source system cannot respond to the tens of thousands of queries that Power BI will fire at it.

Even high performing platforms (Redshift, Snow, SQL DW, SAP HANA, Netezza and Teradata) can really struggle under the number of queries Power BI will fire at it. Poorly written queries and poorly designed data sets can crash Netezza, make Redshift go non-responsive, and fail to return data from SAP HANA.

The Challenge

Well, shoot- we are often asked to deal with data sets that are much larger than what we can cram into Power BI. At the same time, NO ONE wants a slow-performing report.

So how the heck are you going to deal with these Massive amounts of data AND make sure that it performs???

Power BI Aggregations the C-C-C-COMBO BREAKER!!!

The best solution for large data sets is to leverage Power BI's new Aggregations and Dual mode where you can have some imported data, and some direct queries for detailed data. You import aggregated data at a grain that allows interaction within the dashboard but does a direct query of detailed data.

Example:

Imported Aggregate Data :

SalesPersonID, Date, ClientID, ProductType, SUM(Amt), SUM(QTY)

Direct Query Detail Data:

SalesPersonID, Date, ClientID, OrderNumber, ProductType, ProductID, Product Description, Amt, QTY

This example would allow you to build interactive reports off of all of the Sales Person attributes, Client attributes, Product type attributes, and date.

For any details, you then drill into the data for subsets of the data on another tab of the report.

This really changes the way we look at building out our reports and dashboards. No longer do we have to do ALL IMPORT or ALL DIRECT QUERY.

More Information

Josh Caplan (Power BI Principal Group Program Manager) announced these features going to GA on July 18, 2019.

https://powerbi.microsoft.com/en-us/blog/aggregations-for-petabyte-scale-bi-is-generally-available/

Technical Details:

https://docs.microsoft.com/en-us/power-bi/desktop-aggregations

Securing Data: Column Encryption vs. Dynamic Masking

Sun, 16 Jun 2019 12:31:28 GMT

Storing data in the cloud requires additional security.

CAUTION : When moving to the cloud, in order to ensure you have addressed all of the necessary security concerns, please make sure you partner with a cloud security professional to address all of the potential issues . They will help you understand and talk through the myriad of different security controls you need to address while in the cloud. This post only addresses some of the aspects that need to be addressed to secure your data. This is by no means everything done to secure our environment or our data.

Two years ago we began our journey migrating from on-prem server environment running our Microsoft Business Intelligence (MSBI) solution out to the cloud. We had a classic MSBI environment, DB server, SSIS server, SSAS server and an SSRS server (more on this in another post).

There are a number of considerations we had to take into account to ensure that this entire ecosystem was locked down and secured.

Protecting our data against both external and internal threats was the most important aspect of this process.

Our True-North guiding principal around data security was that Data needed to ALWAYS be encrypted.

Encrypted connections? Check.

Encrypted tunnel? Check.

Encrypted server? Check.

Encrypted device access? Check.

Encrypted in-memory and processing? Hmmmm... this is a potential challenge.

Challenge: Encryption turns the data into gibberish that can only be used with a very specific key. Encryption at the in-memory and process level protects data from a hacker watching the data flow through the processor or in the memory. This greatly increases processing time and may be necessary in certain situations, but should be limited where ever possible.

Column Level Encryption

In order to protect our most sensitive data, yet get good performance, we opted to go with Column Level Encryption for our most sensitive fields. This ensured that our data was protected to the appropriate level to enable us to move online, while maximizing our performance.

Our ETL and ELT processes where changed to ADO connections, and secured columns were encrypted upon pull and stored with as an encrypted value.

Account number '1234567A' would be translated into qwerytuadfs1234zxcvbnmghjktyui678r4567ghjklkjfdghtre encrypted value.

This is good, the data was secure. We were confident that no one could make sense of this data and we could migrate to the cloud.

Challenges Start to Emerge

Over the first few months, we started to encounter some challenges with column level encryption.

Encrypted Challenges- Calculations

As we started to work with the encrypted fields, we began to encounter a number of issues with working with encrypted fields. Encrypted date fields could not be worked with to perform date logic. Doing something as simple as calculating a Birthday was impossible with an encrypted date field, so queries that returned people who turned 21 in the last week could not be done.

Encrypted Challenges- Integration

Integrating data from a variety of different sources offered another challenge. Especially when the account number was encrypted.

Account number '1234567A' would be translated into qwerytuadfs1234zxcvbnmghjktyui678r4567ghjklkjfdghtre encrypted value.

BUT

Account number '1234567a' would be translated into a different asfdzxvcvbnmw456r5678fghjdwerty5 encrypted value.

Crap- one of our sources for data was not properly storing the account number with all capital letters. Normaly, no big deal. Something that would have been caught in the integration development. With the encrypted values, this was something that was missed.

While not unworkable, this created a number of challenges when it came to developing and building out new data integrations. Error rates would increase, and time to defect resolution started to take longer and longer.

ADO Connection and Load Times

As we continued on, we noticed that our load times were growing at an increasing rate. While our data was growing previously, the time to load was only growing by a few minutes each month. With these new ADO connections, we started to notice that load times were growing by minutes each week. This was rapidly going to grow beyond our SLA. We tried many different load optimizations, but it all turned back to the ADO connection necessary for column level encryption. Something would need to be done.

What to do?!!?

Mitigating Security Controls

When looking at security, it was important for us to understand the risks and address any risks with an appropriate means of control.

We reached out to our security partners to determine what other controls could be added, or used to justify the removal of column level encryption.

While we were working in the cloud and uncovering these challenges, our partners in security were continuing to increase the security of our entire environment through a variety of new methods.

Our security partners were able to confirm that the advancements that they had made in perimeter and environment protection were enough to address the risk of external entities accessing the data, but that didn't cover the issue of internal resources being able to access fields that they should not have access to.

Dynamic Masking

The advancement in perimeter and environment protection allowed us to revisit other means of protecting the data. Dynamic Masking was an option that we really liked as part of our initial security review, due to the flexibility of the solution, but because it does not actually encrypt the data, it had to be eliminated from our initial deployment. Once the need for column level encryption was removed, we were able to add Dynamic Masking back into our security strategy.

To address internal threats, we replaced the column level encryption with dynamic masking of data. This allowed us to build out role based access to allow some users to see the values in the columns, and others to NOT be able to see the values in the columns.

This ensured that users in the secured role had access to the data could run all of the necessary queries to do their research and data exploration. Then users who were not in the secured role were still able to run the queries, but saw masked values instead of the secured information.

Summary
Now our data is in the cloud in a highly secured environment and stored in a manner that loads fast, is easy to use, and protected from internal potential risks.

I am proud to say that our lead security engineer proclaimed our environment 'the most secure data environment in the company'.

We continue to partner with our security teams working to proactively address any new changes to security and continue to evolve our security profile. While no digital environment is ever completely secure, I have faith that we will continue to set the bar for data security within our organization.

TDL
Partner with Security
Use column encryption sparingly
Dynamic Masking works well

Nobody Understands Data Analytics

Sat, 01 Jun 2019 13:31:33 GMT

And that's OK!!!

Data based decisions is one of the biggest keys to success in business today. The Wall Street Journal ( here ) identified highly functioning Business Intelligence (BI) as a key competitive factor that will enable companies to dominate their competitors way back in 2012. Fast forward 7 years later, and companies have spent billions in data, analytics, BI and are now starting to spend massive money on Big Data ( here ).

So Data and BI is a big deal. Businesses get it.

Or do they?

After talking about data and analytics to businesses for a long time, it's still very obvious that this is a totally new field to most people. If you don't work in data, the whole thing can be difficult to understand.

As a data professional, and someone who analyzes everything (a big sorry to my wife...) it became obvious as to WHY business partners do not understand data.

HISTORY
Classic business professions have been around for thousands to millions of years. These professions have been woven into our collective society and education systems. Accounting has been around since Mesopotamia ( here ), Sales predates Accounting, with the very oldest being Toolmaker dating back 2.6 million years ago.

Data and analytics is a profession in it's real infancy. SQL was first described in 1970 ( here ), became an ANSI standard in 1986, and has been going through massive change ever since. Relative to other professions, data related positions is insanely new to everyone.

EDUCATION SYSTEM
In my MBA program (just over 10 years ago) data and analytics received a single line in a single business textbook 'Data analytics is an exciting new field that should be monitored for growth by business professionals'. Even the two classes in Statistical Analysis talked far more about the math than how data and analytics could be used in every day situations. While I loved these classes, most just got through the stats courses and look on stats as something for the 'nerds'.

Quickly browsing the local Universities graduation requirements for a degree in Business, looks like things might be improving slightly with optional courses in Business Intelligence, Data Base Management Systems and System Analysis and Design. Yet even today these basic analytics courses are still optional for business school graduates, unlike accounting, finance statistics.

EVERY DAY LIFE
Everyone knows hundreds of professions that they work with and interact with every day.

Doctors, lawyers, accountants, car salesmen, police, fire fighters... heck, baristas (a relatively new broadly held job- thank you Starbucks) are well understood professions that people see in their every day life. Even programmers and help desk agents have entered the realm of people that people have become accustom to interacting with.

Yet no data analysts or data scientists in the mix...

POPULAR MEDIA
Our favorite characters in TV and movies have positions that we can relate to and understand. There are hundreds of doctor, lawyer or cop dramas. 'The Office' is the #1 show on Netflix. Think of a job, you can easily imagine a favorite character with that job.

Who do we have in Data Analytics?

Can you think of one?

No- none of the guys on Big Bang Theory are data analysts or data scientists.

I've only found one.

Chandler Bing on 'Friends'.

Yep. That's the one and only analyst in modern culture.

NO WONDER
No history with data. No education. No personal connections. No popular culture. No wonder people do not understand data. It would be unreasonable to expect people to understand data analytics, data science, Business Intelligence or data engineering.

REAL WORLD RAMIFICATIONS
This lack of understanding has some very negative real world ramifications. From the Cambridge Analtytica data scandal ( here ) to every day interactions with people in business, not understanding how data works can cause real harm.

The very first data analytics project I ever worked on was to find the tellers within a bank that exhibited a behavior that was thought to be 'gaming' the bonus system. Unlike the Wells Fargo account fraud scandal ( here ), the bank I worked for watched very closely to ensure that no one in the bank was doing anything unethically.

Unfortunately, I was not tasked with finding people gaming the system who exhibited a type of behavior.

I was tasked with producing a list of employees who exhibited a given behavior. I was not given, and I was too new to ask, 'why' I was getting data. All the business user wanted was the employee name, and a number of events.

As a very new analyst I wanted show how efficiently I could obtain this information. I ran a few queries, did some validation, copy and paste into excel and some formatting quickly generated the list of employees who exhibited the given behavior. The file was then sent over to my business partner.

Two days later, the business partner called me to thank me for the quick work. Only at that point did he explain to me the 'why' they wanted the data. He informed me that the top people on the list had been gaming the system to get a bonus and that he had already started the termination process with HR. My quick work had 'saved the bank' from these unethical actors.

Or so he thought.

Once I understood the real end goal for the request for data, I understood how the request was fundamentally flawed. Several of the employees at the top of the list were part of a student education program where the bank gave students $25 to open a checking account to teach them how banking worked.

These employees were going to be fired because they participated in a student education program, and this director didn't understand the data.

Fortunately, I was able to explain this to the director, the termination process was paused, and we were able to sort through the list of people and find the true bad actors, and save many good people.

Bad guys were caught. Good guys were saved. Whew.

CALL TO ACTION
Until Chuck Lorre makes a popular hit TV show about data scientists, or HBO comes out with a data analytics late night comedy (man... I would watch BOTH of those) people working with data need to do something.

So what are we to do? How do we help bring data into the cultural norm? For that matter, how can we help our business partners better understand the complexities around data?

As someone who is very analytical, I understand that it is often more comforting and easier to work as a solo contributor or to only work with other analysts. This is mentally far easier and more engaging.

We need to break out of this mold. We need to get out and talk with our business users. Encourage them to dig into data. Show them the new tools that make data analysis and access data much easier.

KEEP IT SIMPLE
DEAR GOD- don't make it too complex for them. Most of them were thrown into Stats classes where they struggled to use R or perform other calculations. They saw this and ran away screaming.

Start with something familiar and easy for them to work with.

Excel and Power BI excellent starting starting points for these conversations. Excel has been out since 1985, and has been used by nearly everyone in business. Enhancing Excel skills and extending to Power BI is very easy and has a well laid out path.

Power BI and Excel Guided Learning ( here )

Power BI and Excel; More than just an Integration ( here )

What is Power BI ( here )- for people new to Power BI

Get our partners working with Data. Get them using it in their day to day lives. Start them with Excel. Then move them into Power BI.

Once people start to get comfortable with data, then look at new tools like Databricks ( here ) for starting their journey into big data.

But that's a whole different blog post.

About the Author
Chris Wagner has been working in the Data and Analytics space for nearly 20 years. Chris has dedicated his professional career to making data and information accessible to the masses. A significant component in making data available is continually learning new things and teaching others from these experiences. To help people keep up with this ever-changing landscape, Chris frequently posts on LinkedIn and to this blog.

Please follow Chris on LinkedIn