Data Lake Storage to Synapse Analytics using Managed Service Identity (MSI) – COPY INTO and PolyBase
Why to use MSI? Easy to authenticate any Azure Active Directory supported service Provides limited visibility of the credentials More secured than SQL authentication and less susceptible to hacking Configuration steps Create a storage account and enable Hierarchical namespace for Data Lake Storage Gen2. Within storage account, create a container for file system . Navigate … Continue reading Data Lake Storage to Synapse Analytics using Managed Service Identity (MSI) – COPY INTO and PolyBase
Configure Managed Identity in Synapse Analytics Workspace using PowerShell
Prerequisites: Synapase Analytics workspace is created along with Data Lake Storage gen2 Step 1: Create new linked Service for a workspace Log in to Synapase analytics workspace from Azure portal and Create new Linked services and select Azure Synapse Analytics (SQL DW) Set up Authentication method for linked service as Managed Identity. Once a linked … Continue reading Configure Managed Identity in Synapse Analytics Workspace using PowerShell
Synapse workspace: Load data using Polybase and Managed Identity
Configuration Steps Create a Azure Data Lake Storage Gen 2 storage account, make sure to enable Hierarchical namespace. Read Microsoft KB here. Create a Synpase workspace and select newly created data lake storage as Account name and blob container as File System name. Assign Storage Blob Data Contributor permissions to Synpase Workspace in the Storage … Continue reading Synapse workspace: Load data using Polybase and Managed Identity
How to configure Managed Identity in Synapse Analytics Workspace?
In data engineering a common challenge is to securely establish communication between different services. By providing an Azure resource identity in Azure AD and using it to obtain Azure Active Directory (Azure AD) tokens, Managed identities (formerly known as Managed Service Identity) remove the need for developers to manage credentials. In this blog, we will … Continue reading How to configure Managed Identity in Synapse Analytics Workspace?
Synapse TSQL: variable assignment in SELECT statement cannot be included
When working with synapse as a TSQL developer, it does feel like you’ve gone in a time warp back to SQL 7.0 in terms of whats supported and whats not. Luckily most of the TSQL patterns not supported have some form of workaround. This one I’m looking at is the naughty practice of doing variable … Continue reading Synapse TSQL: variable assignment in SELECT statement cannot be included
Sample Financial Reports for Adventureworks (Updated)
I updated my AdventureWorks Sample Report to include CashFlow, so we now have the three core areas of GL in one PBIX. Profit and Loss Balance Sheet Cash Flow You can download the PBIX from below link https://1drv.ms/u/s!Aquy5jaDyEyihsVDWoQ9TKyGRzwWSA?e=O4VkYr You can also download the source files from below linkhttps://prodata365-my.sharepoint.com/:u:/g/personal/bob_prodata_ie/EVnrkBMz0atFqswf16Pe6uUB3FHm_qcjDFVnJvop9LaTrA?e=n8du0z To see my sessions on Creating Financial … Continue reading Sample Financial Reports for Adventureworks (Updated)
Cashflow using M2M Rules Tables in Power BI
Here is my video on implementing cashflow using M2M Rules Tables in PowerBI
Using Many to Many for Running Sum
There are some well known design patterns and blogs for handling running sum commonly used on reports such as Profit and Loss Statements.https://www.daxpatterns.com/cumulative-total/ I’ll show here an alternative approach that I commonly sue when we have a Many to Many table such as:– A table mapping account codes to Financial Reporting Ranges – A Date … Continue reading Using Many to Many for Running Sum
Using Many to Many for Financial Reporting
1) For multiple Reports and Report Variations As discussed in a previous blog article, simple Profit and Loss may assume a single reportinghierarchy rolling up account numbers into a reporting line and heading. As this starts to break down we may have a Many to Many mapping between Account Codes and Reporting Lines. Like below, … Continue reading Using Many to Many for Financial Reporting
Why Chart of Accounts doesn’t work for Financial Reporting
For financial reporting, can we use the Chart of Accounts as a hierarchy for all our reporting needs ? The short answer is: In the ideal world – Yes. In the not ideal world – No. The Chart of Accounts provides us with a way to categorise Account Codes (aka GL Codes) into an account name and … Continue reading Why Chart of Accounts doesn’t work for Financial Reporting
Financial Modelling in PowerBI – Downloads
For my session in SQL bits on Financial Modelling in PowerBI there are couple of useful downloads if you want to follow or dissect at home. 1) AdventureWorksDW2017This data warehouse has an albeit basic data warehouse version of an accounting table and Account Dimension, so good to use as a demo and starting point.https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2017.bak 2) … Continue reading Financial Modelling in PowerBI – Downloads
Dealing with Blanks Ragged Hierarchies in PowerBI (ISINSCOPE)
While the Matrix Control does has some new features for automatically handling ragged hierarchies as described by Reza Rad and others (https://radacad.com/removing-blanks-from-organizational-ragged-hierarchy-in-power-bi-matrix-visual), it falls short of what we want to achieve on most of my projects and still doesn’t come up to where MOLAP was with its automatic support via HideMemberIf This is the approach … Continue reading Dealing with Blanks Ragged Hierarchies in PowerBI (ISINSCOPE)
Loading…
Something went wrong. Please refresh the page and/or try again.
always on Azure backups BI Cloud consolidation Cube DW ERP Events Featured Finance Financial Reporting Irish Economic Crisis Licencing Load Test Maestro Many to Many MasterClass MCM mdx Modelling MOLAP OLAP Partitioning Performance PerformancePoint Personal Power BI PowerPivot Power View scom SQL SQLBits SQL IO SQL Saturday SQL Server SSAS SSIS storage Syanpse Synapse Tabular TSQL Virtualization
Subscribe to Prodata Blog
Get new content delivered directly to your inbox.