Analysis :: Tabular - Filter / Search For A Specific Measure Value?
May 14, 2015
One of my models has order data, cost per order/invoice ID and then dimensions on Fiscal Year, category, etc...the usual.
A user wanted to search it for an exact order amount. (They knew for example that one of our accounts was not balancing by single order worth $746.13 and assumed it must be an order that was placed but never marked shipped that slipped through the cracks).
Now, in the model I have "order amount" as a field and then a measure that sums that.
I could expose that "order amount" field as a label and let them filter on it in Excel (and that works).
However, I haven't had any luck filtering on the actual measure "Total Order Amount". Such as OrderID-> View Filter -> "Total Order Amount" equals 746.13.
I assume this is due to a few things:
Measure calculates at different levels so filtering on a measure is difficult as you would have to place all the "slicers" and set them first before the measure would "exist" at a level where it could be $746.13. Orders by year would have $746.13 as part of it's year sum, but wouldn't exist as a stand alone line item orders by year 2015 might be 2 million.
Orders by category might exist at 500,000, 8,000, 15,146.36, etc... but not $746.13.
So I would need OrderID on there as a column so the measure could return at the value of $746.13 for one row for it to match the filter?
Basically:
1. Why it can't really filter on a measure?
2. Is there a better way to accomplish this other than exposing the actual column in the fact table "order amount" as it feels like that could cause all kinds of confusion if other users try to slice/filter on that not realizing exactly what it is meant to be?
I have been living in the T-SQL world for years, and am working on my first Tabular model.
I have a fact table that I would like to use for many reports. My idea is that I would use Perspectives in the tabular model to pull in the columns that I need for each purpose. In some cases, I also need to filter out some rows. Is there a way to filter perspectives? The only solution I am coming up with is to create views in SQL Server and import the fact table multiple times for each purpose. That doesn't seem very efficient.
I'm trying to create a percentile rank function based on the standard WIKI version:
I've seen Brian Knight's article here, but that only deals with percentile.
Where I'm struggling is getting the count of members in a set using a measure, in the current context on the same hierarchy, as the filter expression. I'm using the comparative set as in belonging to the same geographical location, and therefore associating by another attribute.
So, cl as below:
MEMBER [Measures].[RegionPercentileCount] AS Count( Filter( NonEmpty( descendants(Ancestor( [Supplier].[NameMap].CurrentMember, [Supplier].[NameMap].[Region]), [Supplier].[NameMap].[Supplier Id]), [Measures].[ActiveMeasure]) , [Measures].[ActiveMeasure] < ([Supplier].[NameMap].CurrentMember, [Measures].[ActiveMeasure])))
Using the same measure and context hierarchy is always going to be equal, and therefore the count is always zero. Its almost as if I need a nested context for the FILTER which allows me to use enumerate the set on the same hierarchy whilst maintaining the external reference.
I'm thinking that perhaps I'm going to have to create another hierarchy and use that as the filter set and reference through StrToMember or similar.
Is it possible to filter out a measure only at the intersection of Two dimension members? I have a date dimension, a Hospital dimension and a wait time measure.
For Example, is it possible to filter out Wait time for Bayside Hospital for the Month of June 2015?
I want Wait time to continue to be displayed for all other months and roll up into the totals without the filtered value.
The user wants to be able, using excel, to apply a filter to all measures in every measure group. I though that I can create a dimension with a single level with two members, let´s say "on" and "off" and depending on the selected member and using an IIF statement decide which formula applies to the calculated measures.
I have serious doubts about the performance and for this technique because I am thinking as a .Net developer and not as a cube developer. Maybe it is better to resolve it scoping the measures but I cannot figure it out.
At the following MDX code , I want to get the aggregate of measure only for members that are also in the specified last time (like in examp 01/06/2015) . I tried existing and exists, but without any lack.
WITH MEMBER A AS (b)+(C) MEMBER [Measures].[Aggregate] AS Aggregate(DAYTIME].[Month].&[2013-01-01T00:00:00]:[DAYTIME].[Month].&[2015-06-01T00:00:00], ([Measures].[D])
In my cube there are two measures which are used in different calculations.Now I'm need to show in report if there any months in data when both or even another one of the measures is not updated (value = 0 or NULL).
how should I create the calculated measure for that?
I have tried in mgmt studio to plan this but I'm in a loop of errors.
I have a dataset that needs to return "Fatalities Involving drivers age 15-17". For years 2003 , 2004, 2005.
So i set up 3 different filters: Year : 2003,2004,2005
Person type: Driver
Age : 15,16,17
Now this is fine for getting a crash count and fatalities (for teen drivers). But i want the fatalities for everyone ( All Person Types) and (All Ages) ...how do i go about this. All i get right now, is the fatalities for Drivers 15-17.
We have a fact table where one of the measures in that fact table is invoice balance. Some of our reports requirements list that they only want to see the sum of those invoices where the balance is greater than X dollars. How do I go about filtering calculations based on a measures value?
My boss and I attended the SQL conference in Vegas a few years ago. He remembers a session on tabular where the presenter said when the underlying table that tabular is built on changes, it automatically refreshes the tabular db. Now that we're working on a tabular project I'm looking around for this information and don't find anything like that happens. From what I can see, the refresh either has to be scheduled from a task, or manually refreshed by right-clicking the db and selecting "process db". He said the session presenter was a Microsoft Program Manager too. Is there any truth to what he remembers?
I have a Tabular model, it has 8 tables , 7 of the tables process in 3 mins, but the main table is 22.3gb in size//I am processing the table on a 64 bit pc with 8 gb ram to the local instance from a Production server//At this rate it will take days to process the Main table 22.3gb, the main table was running off a view but I have inserted the data into TableA and updated the model///Only other optimization process I can think of is copying TableA to my local machine but the performance to the Server on the network is not an issue.I am just process the main table for the last 4 hrs and it is only at 11.7 mill records out of 124 mill total @ this rate it will take 44 hours
According to the book "Microsoft SQL Server 2012 Analysis Services - The BISM Tabular Model" (pages 276 and 277) the following DAX query should work. However, I'll get a rank of 1 returned for every product name. My impression is that ALL doesn't block the context transition for product name. Because there is nothing in the errata, I might do something wrong!? The second query is just a workaround giving the correct result.
SQL 2014...In SSDT, as I'm developing my tabular model I often add new columns to the underlying table/view that feeds my model. When I go into Table/Table Properties, select the new column I've added then hit OK it spins and spins then times out. My model has just over 1 million records. The only way I can get it to work is to delete the table and then add it in again.
Any way to create a measure that filters the second column to mimic the behavior of only filtering for slicer for the Open Pool Date values as per the image below. Ultimately, I need to create a measure that only includes accounts that were opened 6 months prior to the month row context.
Trying to get the values in the second column to only include aging accounts 6 months prior instead of 12 months prior.
We have a requirement to convert Tabular model to Multidimensional as few functionalists like actions does not support in tabular. Looking for list of things needs to take care to convert tabular solution to Multidimensional.
What I want to do is be able to show a measure that SUMS the number of hours logged by anyone who is a SiteManager from the ConstructionSites table.
I wanted to do a SUMX of WorkerTimesheets against HoursLogged, but FILTER against WorkerTimesheets[WorkerID] = ConstructionSites[SiteManagerID] so only workers who are also SiteManager would be counted.However, I can't seem to get that to resolve it always throws an error along the lines that it can't determine context.
SQL Server Data Tools within VS 2010 Shell.2012 SASS in Tabular Mode.I've created a Measure and KPI in my fact table. I've created a perspective named "Summary" When selecting the fields and measures for display in the perspective, the measure is available, but not the KPI (target, status, etc.). When Analyzing in Excel with the Summary perspective, the KPI is not available in the field list. When analyzing in Excel with the (default) perspective, the KPI is available.As far as I can tell, there is no way to select a KPI within the perspective window.
I have been looking at implementing a tabular model based on an OLTP database that's not dimensional. I know that this is possible but during my proof of concept I have encountered numerous problems ...
The things that I have run into are: After setting up the relationships I have found that measures filter context don't propagate along the relationships as I would expect. if the measure is coming from a target table and not a source then an ALL member is returned ( as in multi dimensional when a dimension isn't related to a measure group). Given the lay out of an OLTP database this will be hard to avoid.
One thing I have done to try an mitigate the above problem is to combine the tables used for measures in a view and using that in the source to connect to the rest of the tables. however due to the tables being of different grains this has then created duplication in some of the keys and measures. so the keys cant be used in relationships and the measures aren't accurate.
Are these things other people have come across? or should I give up the ghost and just recommend using dimensional models for the source? is tabular just geared towards a DW the same as multidimensional?
getting a complete Microsoft DW/BI (Tabular), Power BI and Office enviroment up running. I am a (Microsoft) DW/BI manager and want to learn about the latest Microsoft DW/BI (SQL Server Tabular) and Power BI (Power Pivot, Power View, ...). To save time & access from anywhere, I thought I could use Windows Azure but quickly got lost in the Azure preview and market place! I even contacted the Windows Azure team and that didn't clarify things.
So, how to get a SQL Server (2014) environment with all DW/BI (SSIS, SSAS, SSRS, Data Mining) technologies together with Power BI and Excel working? If so, how to get started.If this is not possible on Azure and it has to be done on Window, what do I need to install for the Power BI stuff (to work with Tabular)? I already have SQL Server 2014 and Office Pro Plus installed natively on my Windows machine.
I created a model in Visual Studio 2012 and when I process the model, most tables (the ones that should) process 77,546 records and that is reflected in VS.
However, when I deploy the model to the server it is only deploying 76,500 records for those tables.
Where those 1,046 records went to? Is there a setting that limits the records to be deployed? My base tables in the Data Mart have 77,546 records, just as in my Visual Studio model.
I have a new 2014 SSAS installation. During VS2013 Tabular Project create I get an error Cannot connect, Reason The workspace database on server ***** is not running in tabular mode.I've changed msmdsrv.ini - DeploymentMode>2...The server wont start.
I am working with SSAS Tabular. I have a stand alone table with 60 columns and contains 120K records. Table size is 250MB. And trying to build a tabular report out of it and it is taking longer and throwing exception, screenshot attached.
It might be cross-join issues, as workaround created a dummy measure and using in report. But it working for 10-20 k records and beyond throwing same exception. I have 8 GB RAM and 100 GB free disk space.
i would like to pose a question, when a user do a login in SSRS server,is it possible to pass the username to SSAS tabular, to implement security? I've tested using USERNAME() creating a role that relates USERNAME() with a dim user username, but now i need to implement it with the username used in SSRS server.
I have a Tabular data model and I'm returning a measure that counts employees (each row is an employee) and then a calculated column in the model that gets SeniorityInMonths.
So if an employee was hired exactly 1 year ago, they would have 12 in this column.
I want to group these into bins, but the Group option is grayed out.
It seems simple but I'm not able to make it works. I've got a fact table with sites (DimImplantation) with allowed users (DimDroit) :
1 user can have access to many sites. I've tried these codes but only one worked and only in case where the user had only one site :
a) =DimImplantation[DimImplantation_ID]= LOOKUPVALUE (DimDroit[DimImplantation_ID]; DimDroit[Utilisateur];USERNAME() ) ==> worked for 1user with 1 site
b) =DimImplantation[DimImplantation_ID]= LOOKUPVALUE (DimDroit[DimImplantation_ID]; DimDroit[Utilisateur];USERNAME(); DimDroit[DimImplantation_ID];DimImplantation[DimImplantation_ID] ) ==> doesn't work and says 'not able to to identifie the value of DimImplantation[DimImplantation_ID] in the current context.
c) CALCULATE(VALUES(DimDroit[DimImplantation_ID], SUMMARIZE('DimDroit',DimDroit[Utilisateur]), DimDroit[Utilisateur]=USERNAME()) ==> version for SQL2012. I've had 'End of input reached'.
I get the following error while processing a SSAS tabular model (2014) on a new server.The SSAS service on this server is running under a login which has access to the SQL server data sources. I tried changing the provider to OLEDB from SQLCLNI11 in the connection string but that doesn't work too. The error message isn't useful to debug further.
The cube processing succeeds on a different server. I scripted out the cube DB and ran it on the new server and am trying to process full but it fails with the following error.
Error Message: The operation failed because the source database does not exist, the source table does not exist, or because you do not have access to the data source.
More Details: OLE DB or ODBC error: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.
For more information see SQL Server Books Online.; 08001; SSL Provider: No credentials are available in the security package
; 08001; Client unable to establish connection; 08001; Encryption not supported on the client.; 08001. A connection could not be made to the data source with the DataSourceID of 'd7a37dae-be87-44e0-a8b2-498069af82c9', Name of 'connection name'. An error occurred while processing the partition 'XXXX_460f3467-1a99-4dc9-aaf2-bcf3d54a5c4c' in table 'XXXX_460f3467-1a99-4dc9-aaf2-bcf3d54a5c4c'.
The current operation was cancelled because another operation in the transaction failed. The operation failed because the source database does not exist, the source table does not exist, or because you do not have access to the data source.