Solved: How can I query ActivityPointerBase or space usage details for activities

How can I query ActivityPointerBase or space usage details for activities

I'm a SQL Server Dba working with my CRM Dynamics administrator.
My company has lost the original architect who migrated us from onprem to dataverse. The two of us are playing catchup trying to find details on what to archive.

I'm trying to get information about our data usage (gb size) by activity & year for some proposals.

The response from Dataverse Support:

1. Get a list of all the "tables" from Admin Capacity. done.

2. Query the CRM Database to get aggregate counts on those tables. By "CRM Database", they're telling us to use the TDS Endpoint, not a database.

3. Query our "Synapse Link Db" copy of the data.

"Synapse Link Tables" and "TDS Tables" are different than the "Capacity Tables". Entirely different count of tables. only "Capacity Tables" mention "<tablename>Base".

As a DBA it looks like they have a set of tables that they're not showing us. Then they're exposing views or reporting tables with finessed data to report on.

So I spend a day analyzing "Synapse Link ActivityPointer" (TDS ActivityPointer times out on my queries).

I noticed the majority of the the table space is email (description field).

I noticed in the documentation that sometime in the future they're moving the email content (description) to file storage.

Huh, when are they doing that?

I ask Dataverse Support and they tell me that anything older than 1yr is archived. There are no email descriptions in older data for ActivityPointerBase.

I show them records in "TDS ActivityPointer" and "Synapse Link ActivityPointer" Old records do have email.

They told me that's not the case. I asked them to ask a sr person to explain to them why ActivityPointerBase does not store the email and "TDS ActivityPointer" shows the email.

They let me know ActivityPointerBase is the "TDS ActivityPointer".

I really hope they follow through and ask someone to explain how this is not correct, but I'm worried they'll ask their buddy in the same level of support and they'll laugh about how stupid i'm being.

I'm at a loss about where to go with Support.

I refuse to believe we're being billed on space used that can't be shown to us. That's like trying to clean up a full hard drive without foldersizes/filesizes.

There just has to be some reports/queries somewhere.

Next step this week is to pick up "Fetch XML" and see what that gives us. Does that data match Dataverse Capacity?

I'd really appreciate if anyone can help me get at the size breakdown for ActivityPointerBase... or a better understanding/vocabulary in how this Dataverse Capacity situation differs from what I'm used to as a DBA.

Categories:

Dynamics 365 general

Hello ST-24032311-0,

You've stumbled into the often opaque and frustrating world of Dataverse storage and reporting, especially when transitioning from a traditional SQL Server DBA background. Your experience is not uncommon, and it highlights the significant differences between on-premises database management and cloud-based Dataverse.
Let's break down the issues and provide some guidance:

Understanding the Dataverse Storage Model (Key Differences from SQL Server):

Logical vs. Physical Storage:
- SQL Server: You're used to direct access to physical tables and files, allowing for precise size calculations.
- Dataverse: Dataverse abstracts the physical storage layer. You interact with logical entities (tables), not physical tables. The actual storage is managed by Microsoft, and you don't have direct access to the underlying database files.
TDS Endpoint vs. SQL Endpoint:
- TDS Endpoint: This is the primary way to interact with Dataverse data. It's optimized for transactional operations and is not intended for heavy analytical workloads.
- SQL Endpoint (Synapse Link): This provides a read-only SQL Server-like interface to your Dataverse data in Azure Synapse Analytics. It's designed for analytical queries and reporting.
Capacity Tables vs. Data Tables:
- Capacity Tables: These tables, often with the <tablename>Base suffix, are internal Dataverse tables used for storage management and reporting. They might not directly correspond to the logical tables you see in the TDS endpoint.
- Data Tables: These are the logical tables you interact with through the TDS endpoint and Synapse Link. They represent the data you see in your CRM applications.
Email Storage:
- The move to file storage for email attachments and content is an ongoing process. Microsoft is gradually migrating this data to Azure Blob Storage to optimize storage costs and performance.
- The inconsistencies you're seeing between the TDS endpoint and Synapse Link regarding email content likely reflect this migration.

Addressing Your Specific Issues:

Data Usage Breakdown:
- Capacity Tables (Best Source): The capacity tables are the most accurate source for understanding your data usage. However, they might not provide the level of granularity you're used to.
- FetchXML (Limited): FetchXML is a query language for Dataverse, but it won't give you direct storage size information. It's useful for retrieving data counts and other metadata.
- Power BI: Use the Dataverse connector in Power BI to create reports that visualize your data usage. You can connect to both the TDS endpoint and Synapse Link.
Inconsistencies with ActivityPointer:
- Email Content Migration: The differences you're seeing likely relate to the email content migration. The TDS endpoint might still show email content stored in the ActivityPointer table, while Synapse Link might reflect the migrated data.
- Dataverse Support Misunderstanding: It's possible that Dataverse Support is not fully aware of the complexities of the email content migration. Escalate your support request and insist on speaking with a senior engineer.
Storage Billing:
- Microsoft bills you based on the total storage consumed by your Dataverse environment, not on the size of individual tables.
- The capacity tables provide the most accurate representation of your overall storage usage.

Recommendations:

Escalate Support:
- Insist on speaking with a senior Dataverse engineer who has a deep understanding of the storage model and email content migration.
- Provide clear examples of the inconsistencies you're seeing.
Use Power BI for Reporting:
- Connect to the capacity tables and data tables using Power BI.
- Create reports that visualize your data usage by entity and year.
Understand Email Content Migration:
- Research Microsoft's documentation and announcements regarding the email content migration.
- Ask Dataverse Support for a clear explanation of the migration timeline and impact on your data.
Focus on Logical Size:
- In Dataverse, the logical size of your data is more important than the physical size.
- Focus on understanding the number of records and the size of your data in terms of the logical entities you use.
Audit Logs:
- Use the audit logs in Dataverse to track changes to your data. This can help you understand data growth and identify areas for optimization.

Key Takeaways:

Dataverse storage management is significantly different from traditional SQL Server database management.
Focus on understanding the logical data model and using the appropriate tools for reporting (Power BI).
Escalate support requests when necessary to get accurate information.

This is a complex area, but with persistence and the right tools, you can gain a better understanding of your Dataverse data usage.

If my answer was helpful, please click Like, and if it solved your problem, please mark it as verified to help other community members find more.
If you have further questions, please feel free to contact me.

My response was crafted with AI assistance and tailored to provide detailed and actionable guidance for your Microsoft Dynamics 365 query.

Regards,
Daivat Vartak

Quick Links

Leaderboard