The fourth page shows the details behind each measure. Smart filters at the top of the page let you filter the Table and To Table in the relationships. Slicers on the left filter both the force directed graph and the table. The third page shows relationships between the tables. The second page uses a bar chart as an interactive filter against a list of table source queries and columns. It gives me a decent idea of the size and complexity of the model. On the first page, I have several summary numbers that tell me about the contents of the model. I imported the Excel model into Power BI, made a few adjustments, and then added some visualizations. This report is a bit paired down from a visualization standpoint compared to the Excel file because I eliminated some of the data that wasn’t analytically relevant for me.Ĭlick here to view this report in Power BI. But we could really analyze this using visualizations other than tables in Power BI Desktop. This is great if you just need documentation or a quick way to look up specific information. I was able to open the file and update the queries with no issues and no changes in Excel 2013. Just download the file, change the values in the TabularInstanceName and TabularDBName queries, and refresh. You can download and use this model if you have Excel 2016 or 2013 with Power Query. The rest of the tabs use pivot tables and slicers. The Database Info tab uses cube functions to display the values shown. Perspectives and the columns, hierarchies, and measures they contain.Security roles, membership, and permissions.This redesign gets you around the error since the finalized tables don’t have the embedded source connection, just a reference to the results from another query. Basically, you have to redesign your queries so that you have “staging tables”, which you can then use to build finalized tables. Ken Puls has a great explanation of how to get around this issue. “Formula.Firewall: Query ‘QueryName’ (step ‘StepName’) references other queries or steps and so may not directly access a data source. ![]() As I did that, I ran into a Power Query limitation as evidenced by the error message below. I built my model with the normal embedded connection strings and then converted them to use the connection Connection Info tables. In order to make this work for any model, I employed a trick I learned from Chris Webb to avoid duplicating connection information across queries. This enables me to merge data into a model that I feel makes the most sense and doesn’t suffer from too many relationships and hidden tables (which is what would happen if you just imported the results of each DMV query into the model). My Power Pivot model uses Power Query to import the data from the DMVs. My example model was connected to a quick demo SSAS model I made based upon the Wide World Importers DW database. You can download my documentation model here. Since I needed to document a tabular model for a client, I created a Power Pivot model in Excel 2016. This makes it necessary to store the data somewhere before transforming and merging them. Since DMVs require DMX queries, you are somewhat limited in your ability to join the data from the DMVs together. ![]() For more information analyzing your Power BI model, see Chris Webb’s post. You’ll just need to change the connection information to your Power BI instnce while the. I’ve been able to figure out the descriptions that correspond to some of the values, but I’m still missing a few.Īs with the MDSCHEMA DMVs, the TMSCHEMA DMVs can be used to document your model. There are a few fields that show IDs rather than descriptions (e.g., ExplicitDataType in TMSCHEMA_COLUMNS, Type in TMSCHEMA_DATA_SOURCES). While there are 36 TMSCHEMA DMVs, the main DMVs you will want to check out for model documentation purposes are: Until then I published a Gist that provides a list of those views as well as some notes on what kind of useful info is in each one. I expect them to show up here at some point in the future. They are, unfortunately, not documented at this time (as of ). Not to worry, though. With the new compatibility level 1200 SSAS Tabular models came some new DMVs. For example, the MDSCHEMA_MEASUREGROUP_DIMENSIONS DMV seems to just return one row per table rather than a list of the relationships between tables. If you were used to documenting your SSAS model using the MDSchema rowsets, you might have noticed that some of them do not work with the new tabular models.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |