He told me I needed to check out a new capability they had just enabled in SQL Server 2016 that would make it much easier then before to implement row-level security for SQL Server Analysis Services. You may not be as excited as I am about this unless you’ve also lived through the tedious configuration process we’ve had to endure for many years until recently.Ī little over a week ago I heard from my friend and past co-author, Robert Bruckner – a Senior Architect and Development Lead on the Reporting Services product team. This feature doesn’t require complex administration configurations, service principal delegation, Kerberos, Claims/Windows Token conversion or impersonation.
You can download the PDF file from his blog.Ī new ground-breaking feature in SQL Server 2016 allows report user context to be passed through shared data sources, enabling role-based, row-level security. I love the simplicity of this comprehensive graphic.
I’d also like to give a plug for a terrific Power BI architecture diagram poster created by Dustin Ryan.
The four-part series is on my YouTube channel using the following links:Īdam Saxton also gives a nice walkthrough of using SQL Server DirectQuery with Power BI that may provide an additional perspective and information about securing and sharing the solution. SSAS Tabular with large data volume & multiple partitions on-prem with direct connect.SSAS multidimensional/cube on-prem with direct connect.Large SQL Server database with clustered column store index connected with DirectQuery.SQL Server database connected with DirectQuery.I cover using the Power BI Enterprise Gateway to connect published Power BI reports to on-premises data sources: The running time for all four parts is about 44 minutes.
I'll continue adding things to my model until we have a model that's more fleshed out, like the video rental model that we saw at the beginning.I just finished posting a 4-part series demonstrating how to use Power BI with on-premises data. I can also add other informative items to my workspace, such as stamps, shapes, text, and notes. Here, I can modify properties such as relationship type, cardinality, or referential integrity. I can modify any of the relationships by double clicking on them to open up the Properties dialog. And then I'll click on film.Īs you can see, a non-identifying relationship is represented by a dotted line. I'll select Non-identifying Relationship. Genre is a look up table for film, so it requires a non-identifying relationship. I'll do the same thing for the customer table, but since customer ID is already an attribute in my order table, Toad will just modify it to become a form primary key. You can see, it brought over the film ID column as a form primary key, and the relationship is represented with a solid line. I'll click on the Identifying Relationship icon. So this order records table will need to have identifying relationships to films and to customers. In my application, every order references a customer and a film. Now that I have some tables to work with, I'm ready to draw the relationships between them. I going to add a few more entities before we move on. I've just created my first entity representing my customer table. Now, I'll even add an index for this entity. As I'm making changes to this entity, I can click on SQL Preview to see how Toad data Modeler is forward engineering this particular entity into Oracle. I can specify my keys by clicking on the Keys tab, click Add, and modify my first key. I can also make changes to the data type using this drop down menu. You can see I can quickly add as many columns as I'd like. Then I'm going to click on the Attributes tab to add my column information. I'm going to select Entity, and then click on my workspace. Now I'm going to add an entity to workspace to represent a customer table. I'm going to select Physical Oracle 11 GR2 database. Or, if you need to create a logical model with inheritance, select Logical Data Model. You can later convert a universal model to any other physical model. If you wanted to create a generic, universal model, you can do so by clicking on Universal Data Model. These are the databases Toad Data Modeler would forward engineer your model towards. And you can see the large number of platforms we support for physical modeling. Here, I have Toad Data Modeler open with a sample video rental model.īefore we get to this point, let's start with the basics. In this video, I'm going to show you how to create your first model with Toad Data Modeler. My name is Matthew Phan, Systems Consultant for Dell Software.