Interview question

Power BI ----------------------- Explain the building blocks of Microsoft Power BI? Datasets: The data sources imported into Power BI. Reports: Collections of visuals based on datasets. Dashboards: Single-page summaries created from multiple reports. Workspaces: Collaborative environments for managing content and sharing insights among users. What do you mean by the content pack? A content pack in Power BI is a pre-packaged collection of related dashboards, reports, datasets, and Excel files that can be shared with users. Content packs can be created by organizations or sourced from third-party providers to facilitate easy access to common analytics resources. difference between a calculated column and a measure. A calculated column is computed during data load and stored within the model as part of the table; it evaluates row-by-row. A measure calculates results dynamically based on filters applied during report interactions, providing aggregated results across rows Power BI keys : workspace, power bi service, slicer , filter , gateways difference between SUM and SUMX in DAX. SUM() calculates total values directly from a specified column across all rows without any additional context. In contrast, SUMX() evaluates an expression over a table row-by-row before summing up results; it's useful when calculations involve multiple columns or require context-specific evaluations Semantic Model? A Semantic Model defines how data elements relate within databases or datasets while providing business logic through measures, hierarchies, metadata definitions etc., facilitating easier understanding and analysis by end-users without needing deep technical knowledge about underlying structures. A semantic model is an abstraction layer that sits between raw data and business users or applications Example : dataset is semantic model : sits between reports and database How do you handle many-to-many relationships in Power BI? Many-to-many relationships can be handled by creating bridge tables that connect two tables indirectly. This involves creating an intermediary table that contains unique values from both related tables, allowing for effective filtering and aggregation difference between VALUES and DISTINCT in DAX? Both VALUES and DISTINCT return unique values from a column; however, VALUES also returns blank values if they exist within the column while DISTINCT does not. VALUES is often used when needing context-aware results based on filter context the purpose of the CROSSFILTER function in DAX? The CROSSFILTER function changes how filters propagate between two related tables during calculations. It allows users to specify whether filtering should be unidirectional or bidirectional when evaluating measures involving relationships What is Fabric? Fabric refers to Microsoft's unified analytics platform designed for seamless integration across various analytics services including Azure Data Factory, Azure Synapse Analytics, and others. It aims to simplify workflows by providing an end-to-end solution for managing analytics processes. OneLake Data Hub? OneLake Data Hub is the centralized repository, in fact, designed for vast amounts of structured and unstructured data, spread across a variety of services, within a single, cohesive ecosystem that offers easier access and integration between multiple analytics tools and provides robust governance features. Difference between the SUM and SUMX functions in DAX? — Answer: The SUM function in DAX is an aggregation function that calculates the sum of a numeric expression for a specified column or table Total Sales = SUM(‘Sales’[SalesAmount]) The SUMX function, on the other hand, is an iterator function that applies a calculation expression to each row of a table and then sums up the results. Total Sales = SUMX(‘Sales’, ‘Sales’[Quantity] * ‘Sales’[Price]) Summarize Table = SUMMARIZE(Orders, Orders[CustomerID], Orders[ProductID], “TotalSales”, SUM(Orders[Sales])) Add column Table = ADDCOLUMNS(Orders, “Profit”, Orders[Sales] — Orders[Cost]) Category Sales = CALCULATE(SUM(‘Orders’[Sales]), FILTER(‘Orders’, ‘Orders’[Category] = “Bike”)) Data bricks ----------------------------------------------------- Databricks is a unified, cloud-based data and AI platform. Founded by the creators of Apache Spark, it provides a collaborative workspace designed to simplify and scale big data analytics, data engineering, machine learning, and generative AI workflows • Data Lakehouse Architecture: Combines the flexible, scalable storage of a data lake with the structure and management features of a data warehouse. • Apache Spark: Functions as a managed, high-performance computing engine, removing the complexities of setting up and scaling servers manually. • Data Intelligence: Incorporates generative AI and natural language processing to allow users to write code, search for data, and troubleshoot using plain English Pipeline SSIS ------------------------------------- The SSISDB catalog is the central point for executing and managing Integration Services (SSIS) projects you've deployed to the Integration Services server 8. How does SSIS handle logging, and what are some built-in logging options? SSIS provides built-in logging to capture runtime events. Logs can be written to text files, SQL Server tables, Windows Event Log, or XML files. 11. How would you design an SSIS package to handle incremental data loads? I would use techniques like change data capture (CDC), timestamps, or lookup comparisons. Only new or changed rows would be extracted and loaded. 12. What is the difference between synchronous and asynchronous transformations in SSIS? Synchronous transformations process rows as they come without changing row counts. Asynchronous transformations create new output buffers, changing the flow and often using more memory SSAS-------------------------------------- What is a surrogate key? A surrogate key is a key created in the data virus to convert master information into a dimension, reducing storage and ensuring that all master information is referred to in the sales data. Walter / marina Type of facts : Fully Additive Facts Semi-Additive Facts Non-Additive Facts Derived Facts: Derived facts are the facts which are calculated from one or more base facts Factless Facts: A factless fact table is one which only has references (Foreign Keys) to the dimensions and it does not contain any measures Textual Facts: Textual facts refer to the textual data present in the fact table Relation between facts and dimension : Regular, Referenced, Fact: In a Fact relationship, the dimension table and the fact table are one and the same Many-to-Many: In a Many-to-Many relationship, a dimension is indirectly connected to a Measure Group through an intermediate fact table which joins with the dimension table Calculated Members are members of a measure group and are defined based on a combination of one or more base measures A Named Set is a set of dimension members (usually a subset of dimension members) and is defined using MDX (a Multidimensional Expression) KPI stands for Key Performance Indicator. A KPI is a measure of an organization’s performance in a pre-defined area of interest. Proactive caching is an advanced feature in SSAS and it enables a cube to reflect the most recent data present in the underlying database by automatically refreshing the cube based on the predefined settings. This feature allows the users to view the data in near real-time. ----------- Architect de solutions BI Tols : Microsoft Visio Visio 365 Luncichat ETL (transformation avant chargement) ELT : chargement dans le Data Warehouse/Lakehouse avant transformation La gouvernance des données désigne l'ensemble des règles, politiques et processus qui permettent à une organisation de gérer, sécuriser et valoriser ses données tout au long de leur cycle de vie. [1, 2] Les 4 piliers fondamentaux Qualité : Garantir que les données sont fiables, exactes et à jour. Sécurité et confidentialité : Protéger les données contre les accès non autorisés et les pertes. Conformité : Respecter les lois en vigueur, comme le RGPD ou les normes ISO. Disponibilité : S'assurer que les collaborateurs autorisés peuvent accéder aux bonnes données au bon momen Microsoft Fabric is an end-to-end, Software as a Service (SaaS) unified data analytics platform. It seamlessly combines data engineering, data integration, warehousing, data science, and business intelligence into a single, collaborative workspace. [1, 2, 3] Core Components • OneLake: Often described as the "OneDrive for data," it serves as a single, unified, logical data lake for your entire organization, eliminating the need to move data between different tools. • Data Factory: Provides robust capabilities for complex data ingestion, preparation, and orchestration. • Data Engineering & Science: Utilizes Apache Spark-powered notebooks and engines to build, train, and deploy machine learning models. • Data Warehousing: Delivers high-performance, schema-based data warehousing within a unified ecosystem. • Real-Time Intelligence: Simplifies working with streaming data from IoT devices and applications. • Power BI: Deeply integrated into the platform to allow users to visualize insights and build interactive reports seamlessly on top of OneLake • La conception d'un entrepôt de données (ou Data Warehouse) est le processus de planification et de structuration d'un système centralisé permettant de regrouper, d'harmoniser et d'analyser de grands volumes de données issues de sources multiples pour l'aide à la decision. Date modeling : . Conceptual Data Modeling: A high-level, business-oriented view identifying key entities and relationships, often termed domain models. . Logical Data Modeling: Maps business rules to technical structures (tables, columns, keys) without specific database technology details. . Physical Data Modeling: Defines specific database implementations, including column types, indexes, and partitions, tailored to a chosen DBMS (e.g., PostgreSQL, Snowflake).. . Dimensional Modeling (Kimball): Optimized for data warehousing and analytics using star schemas (fact and dimension tables) to enable fast querying and reporting. Benefits of BI Solution Architecture Enhanced Decision-Making: Provides accurate, real-time insights for informed decisions. Operational Efficiency: Automates data processes, reducing manual errors. Scalability: Adapts to growing data volumes and evolving business needs. Data Governance: Ensures data accuracy, security, and compliance.