Analysis :: Count Distinct In SSAS Cube?
Sep 8, 2015
Background: I have a Huge fact dimension table(table has both measures and dimensions) that i am using to build a SSAS Cube.
The table didn't have a unique identifier, so the database team added ROW NUM as a column to the table which i am using as a PrimaryKey in my Cube build. I was able to create a cube successfully with it without any issues.
Problem: Now customers are asking for a 'Claim Count calculation' which shows the Distinct Claim Count.
Its defined as below :
Count(Distinct Claim_Number || Claim Year || Claim Month)
. All the 3 columns are available in table, but when i am trying to create this Count Distinct Object in the DSV the Cube processing time increased by 5 times, as now i have to use a GroupBy function in my SQL.(There are around 30 columns to group by).
Is there a better way to achieve this Count(Distinct Claim_Number || Claim Year || Claim Month) without using groupBy in DSV SQL logic? I cant seem to find any Count(Distinct) function in the Cube Calculation functions?
Environment: SSAS 2012 Multidimensional Model
View 4 Replies
ADVERTISEMENT
Jun 12, 2006
i am currently trying to build a distinct count on my cube (mssql2005 analysis services).But after i added the discount count on the field i want to and start the processing, the following errors appear.Errors in the OLAP storage engine: The sort order specified for distinct count records is incorrect.
View 17 Replies
View Related
May 21, 2015
how can use this mdx script in the calculation part of a cube, will i simply dump it in the script form by starting with the 'create member current cube.
[measures].[test]'
select
[measures].[abc] on 0,
[xyz].[xyz].(&0):[xyz].[xyz].(&60) on 1
from
(
select
(tail([month].[month].[month].members,6))on 0
from
[cube])
View 3 Replies
View Related
Dec 13, 2010
Is it possible to have more than one cube under one SSAS database? For example I have a database "Test" and in this the cube exist is "TestCube", is iit possible to deploy another cube "TestCube2" under the Test databse?
If yes then what is the process to do that, the reason I am asing is there are some common dimensions used n both the cubes and I am not sure what is the best way so that I can use the shared dimension?
View 6 Replies
View Related
Aug 7, 2015
I want to implement population data in sales cube.
Fact table has customer code which is foreign key of Customer master dimension which in turn is linked to census data dimension. Census data dimension have city wise population data having foreign keys of zone and state.
We want to add population data in fact table.
View 3 Replies
View Related
Oct 25, 2010
I have a .abf file, which I am attempting to restore. I go to Management Studio and attempt to restore the cube.
However, whenever I attempt to restore the following error message occurs:
"File 'C:/.......' specified in restore command is damaged or is not an AS backup file.
The following error occured:
Access is denied (Microsoft SQL Server 2008 R2 Analysis Services)
View 4 Replies
View Related
Jun 21, 2013
How can i deploy an existing cube with a new name on the same server?
View 4 Replies
View Related
May 21, 2015
I am thinking of a possible design where the cube will never go offline.
Usually when I do some code changes on my cube the cube goes offline and I need to Full Process it again to get it back .
However , in cases where the cube is extremely critical for the business users , it would be great if I can deliver a solution where the cube never goes down.
View 4 Replies
View Related
Jun 12, 2015
How can I connect ssas cube to server. I have no SSAS SERVER INSTANCE...
View 3 Replies
View Related
Apr 16, 2015
I am working on SQL 2012.We have a SSAS Cube build. On top of it client use Excel to connect to SSAS Cube and See the reports.My Cube process every hour and take almost a 1-2 min to Process.When ever End user/Client See or refresh the Excel report (Which use Cube as Source),WHEN CUBE IS PROCESSING ,they get an error that Source is not available
We Have tried best but cannot bring down the Processing time of the cube to 3-5 Second , so that End user don't face report refresh issue at the moment of cube processing
Requirement :In Case End User see the report while Cube is processing from back end , Instead of Error they should see some customize Msg which we can provide some thing Like "Data is Refreshing , please wait ".
View 5 Replies
View Related
May 7, 2015
I have only 1 denormalized table that is being used in a SSAS Tabular model(which is about 3GB). I am doing a POC to convert it into a SSAS Multidimensional and explore it.
Table1
-----------------------
StoreName
StoreDesc
ItemName
ItemDesc
Qty1
Cost1
Price1
Amount1
1st Question) I am seeing that there is no Primary Key(unique key) in the current denormalized table. (Tabular Model didnt require any primary key). But i think for Multidimensional the key is mandatory? Should i generate a composite key myself in a Named Query based on this table(in the DSV)?
2nd Question) What is the best way to design my Multidimensional Cube/Dimensions based on this single table?
Say if i comeup with a Composite Primary Key called (PK_ID) . Should i be splitting up my facts / dimensions in my DSV using Named Queries similar to below(Using the same PK for my dimension tables also?)
a) FactTable = Select PK_ID, Qty1,Cost1,Price1,Amount1 from Table1
b) StoreDim = Select PK_ID, StoreName,StoreDese from Table1
c) ItemDim = Select PK_ID, ItemName,ItemDesc from Table1
Would this work?
View 3 Replies
View Related
Jul 22, 2015
For Example: I have one dimension named as "Name", Under this I have "FirstName" and "LastName" Attributes are there.But when i drag "Name" dimension, By default "First Name" dragged. But i Want "Last Name" should drag.
View 6 Replies
View Related
Oct 2, 2015
Is there any way to copy my Data of 2015 to the Planning/Forecasting Value of 2016?
My question is based on that we use INFOR ION BI right now and there we can just add an Button in our reports wich physically copies the value from one year to the next year based on some other rules in the cube.
Now I need to make this example work with SSAS and Excel PivotTables but I cant figure out how.
I have absolutely no clue where and how to accomplish it. Do I use Calculations, do I use Actions, do i make it in the Dataview, Cube or directly in Excel?
View 4 Replies
View Related
Jul 22, 2015
I have a dimension like Districts, Under that 2 Attributes are there i.e,District ID and Districts. When i drag Dimension "Districts", in OLAP grid it come District ID first. But i want Districts to drag first. How can we sort Attributes(District ID and Districts) for a dimension.
View 6 Replies
View Related
Jul 8, 2015
I have connected to SQL SERVICES ANALYSIS SERVICE database through excel and when I observed that value of the date attribute is displayed as ######## in the excel for 1/1/1753.
I am able to see the value 1/1/1753 in the Cube browser but not able to see the vale in the excel.
how to replace this value with blank in the excel.
View 3 Replies
View Related
Nov 25, 2015
I am facing a very weird issue in our SSAS database. We have several roles with 'Read definition' access. None of these roles are able to see data in the cubes. I have checked these roles over and over and there is no problem with the definition. Each of the roles have been given read access to Data Source, Cube and Dimensions.
The users are able to access the definition and structure of the cube i.e. they can see the measures & dimensions available but when they drag measures and dimension attributes in the browser (SSMS) OR execute an MDX - they get null values. The roles with 'Full Control (administrator)' access are able to see all the data without any issues.
I have tried the following:
1. Deleted all roles and re-created.
2. Created roles directly on SSAS DB.
3. Deployed all objects and processed the DB.
Each time only the admins are able to see the data.
View 7 Replies
View Related
May 13, 2015
I want to change the color of the ssas cube calculated member in formview from red background to white or back to default color settings. This also includes changing the text color back to default. What properties do I have to change?
View 4 Replies
View Related
Jun 10, 2015
I've created a cube and it processed fine. The calculate command is there. The measure that I'm attempting to run in the SSAS/Visual Studios browser is simply a count rows measure. When I drag the measure to the window, it says no rows available. If I click on the filter that allows nulls, the only change it makes is that it goes from no rows available to "NULL".
View 4 Replies
View Related
Sep 17, 2015
I need to find the percentile using cube so i am using the below formula :
((n-1) * p /100) -1
n= count of number of array records
p= percentile
I am using below MDX query:
WITH MEMBER [Measures].[PV] AS 25
Member [Measures].[CntCT] as Count(NonEmpty([Tb City].[City Name].&[DC], [Measures].[CPT1]))
Member [Measures].[PVInt25] as Int(((([Measures].[CntCT] - 1)* [Measures].[PV])/100) - 1)
Member [Measures].[PVC] as ([Measures].[CPT1],Order(NonEmpty([Tb City].[City Name].&[DC],[Measures].[CPT1]),
[Measures].[CPT1],ASC).Item([Measures].[PVInt25]))
Select [Measures].[PVC] on columns, {[Tb City].[City Name]} on rows from test;
The line 2:
Member [Measures].[CntCT]
In that i need to find the n count of rows where city is DC (City is my Dimension) in Measures CPT1
But currently it is giving the result 1 instead in actual in my test cube there is the city DC exists with 23 CPT1 rows count.
I tried the below query:
SELECT NON EMPTY {[Tb City 1].[City Name].[City Name].&[DC] }
ON COLUMNS, NON EMPTY { ( [Measures].[Tb Main Count] ) }
ON ROWS FROM [test]
Above query gives me the correct count i.e. 23 but i need to get the result of above query in line 2 of MDX query:
Member [Measures].[CntCT]
View 4 Replies
View Related
May 28, 2007
Hi,
I'm using sql server analysis services 2000, i have a problem with grand total when i select more than one member of any demension. the measure of this cube is built on a distinct count agregation function. i have only one measure in th cube
can some body help me !!
View 1 Replies
View Related
May 27, 2015
My requirement is to count the customer order number for premium order type orders which has some order quantity.I am using below MDX
count
(
Filter
(([Customer Order].[Dim Customer Orderkey].[Dim Customer Orderkey].members,
[Outbound Order Attributes].[Order Type].&[P]),[Measures].[Ordered, pcs]>0 ) ,
)
The result is accurate but the query execution time is 3-4 minutes for 10 fact records, when i use multiple dimension. it is showing me 0 valus for this measure for all the members for the dimesion attribute which doen't have any customer order. example it shows all the member of date dimension. is there any way to reduce the rows. i think this is the reason to take more execution time.when i use EXCCLUDEEMPTY the result is NULL
count
(
Filter
(([Customer Order].[Dim Customer Orderkey].[Dim Customer Orderkey].members,
[Outbound Order Attributes].[Order Type].&[C]),[Measures].[Shipped, pcs]>0 ) ,
EXCLUDEEMPTY)
View 3 Replies
View Related
Aug 11, 2015
I have the need to calculate a distinct count over a period of 3 years. I use the MDX
SUM([Year].[Year].CurrentMember.Lag(2):[Year].[Year].CurrentMember,[Measures].[CNT])
Which works fine for all my other calculations except this, where I need a distinct count. CNT is a calculated measure. The browser would look like this:
Category Year1
Year2 ..... MDX what I have now
MDX what I need
A A1
1 1 2 1
A2 1
0 1 1
A3 0
1 1 1
How can I achieve this?
View 5 Replies
View Related
Aug 10, 2015
I am doing a distinct count on a related table's column, but get an out of memory error if I run it for the entire table (works great for just a few rows when filtered down).The error I get is: "We couldn't get data from the external source.The operation has been cancelled because there is not enough memory available for the application. If using 32-bit version of the product consider upgrading.
I know I can add a related column and that works fine...but that seems to me like I've defeated the purpose, I have a good and proper lookup table, and should be able to run my query against its relationship.Here is the query and details below *Note I supplied a scaled down sample, on my actual model I receive these errors, not in the sample as it has only a few rows
List Workers Distinct Project Customers:=CALCULATE(DISTINCTCOUNT(Projects[CustomerID]),'WorkersToProjects')
Other measure which returns no errors, but included for completeness:
List Workers Projects:=CALCULATE(DISTINCTCOUNT([ProjectID]),ISBLANK(WorkersToProjects[ProjectID])=FALSE())
My goal here is to allow the user to view the workers assigned to a project, but also get counts of the workers assigned to the CUSTOMER of a project. For example, suppose we lose a customer, we want to see how many workers would be impacted by that, so a count of projects per worker is not useful there, we need to see a count of workers per project's customer (owner of project whom project work is being done for)The question being: How can I accomplish this:
1. WITHOUT adding a calculated column to WorkersToProjects (of Projects.CustomerID)
2. WITH better performance?
There must be a better way to write this DAX to still get the correct answer?*Pic of pivot table, again, the numbers are accurate but the formula used to List Workers Distinct Project Customers measure does NOT scale :( 3 count for red , the number of Projects John has and 2 count for blue, the unique customers/owners of those projects "Veridian Dynamics" and "Massive Dynamic". URL....
View 3 Replies
View Related
May 19, 2015
I have problems creating a cube with AMO.
I can add the cube to the database object and fill it with dimensions and a measuregroup (see code below).
If I call cube.Update() it says something like "Error in meta data manager. Cube has no measuregroups." (getting the message in german language)
The error in Microsoft.AnalysisServices.OperationException.Results.Messages is -1055653629
I can't find any documentation about this (or any other) error code in Microsoft documentation.
Here's my Code:
Cube newCube = database.Cubes.Add("MyCube","MyCube");
newCube.Language = 1031;
newCube.Collation = "Latin1_General_CI_AS";
CubeDimension dim = newCube.Dimensions.Add("dim1","dim1","dim1");
CubeAttribute attrib = dim.Attributes.Find("dim1Attr1");
[code]....
View 2 Replies
View Related
May 13, 2014
I have a cube that we are processing nightly via an Analysis Service Processing Task in SSIS. In order to increase the performance of the processing time, we elected to use a lot of rigid dimension attributes, and do a full process of everything in the SSIS task. The issue that I am having is that after that task completes, I need to go into Visual Studio to deploy the cube becuase we are unable to browse or use the cube. This issue seemed to start once we changed the SSIS Analysis Service Processing Task to do a full process on the dimensions, rather than an incremental.
I would expect that once development is done, and it is processed and deployed, that is it. My thinking is that the SSIS task should just update the already deployed cube,
View 2 Replies
View Related
Aug 4, 2006
Hi,
I have a problem with processing my cube. My fact table (with telephone data) contains about 400,000 records... which is increasing rapidly (400,000 records is about 8 months of data)...
I have a few dimensions:
Dimension User: about 200 records
Dimension Line: about 200 records
Dimension Direction: 4 records
Dimension Date: 365 records for each year
Dimension TimeInterval: with 24 records
So far so good... when I process this dimension I have no problem....
However, when I add a dimension (CalledNumber, with exactly 101 records) the processing hangs as soon as it starts...
The SQL performed when processing the cube looks like this:
SELECT field1, field2,... fieldn
FROM table1, table2,.... tablem
WHERE
(table1.id=table2.table1id)
AND
(table2.id=table3.table2id)
...
When I execute above SQL in the Query Analyser from SQL Server Enterprise Manager, it ALSO hangs...
I am not really suprised by that, because this SQL first create a huge table of 400,000 x 200 x 200 x 4 x 365 x 24 x 101 records and after that works through the WHERE statements to filter out the appropriate records.
for me it would be more logical to use the following code to process the cube, but that cannot be changed in Analysis Manager:
SELECT field1, field2,... fieldn
FROM table1
LEFT JOIN table2 ON (table1.id=table2.table1id)
....
LEFT JOIN tablem ON (tablem.id = tablem-1.tablemid)
When I execute above SQL in the Query Analyser from SQL Servel Enterprise Manager, it does NOT hang, but performs the query in about 35 seconds....
But Analysis Manager does not allow me to change the SQL used for processing the cube...
What can I do to add more dimensions to my cube... (It will be more anyway after adding the CalledNumber dimension)??
any suggestions?
PS. forgot to mention: I use Sql Server 2000
View 1 Replies
View Related
Aug 4, 2006
Hi,
I have a problem with processing my cube. My fact table (with telephone data) contains about 400,000 records... which is increasing rapidly (400,000 records is about 8 months of data)...
I have a few dimensions:
Dimension User: about 200 records
Dimension Line: about 200 records
Dimension Direction: 4 records
Dimension Date: 365 records for each year
Dimension TimeInterval: with 24 records
So far so good... when I process this dimension I have no problem....
However, when I add a dimension (CalledNumber, with exactly 101 records) the processing hangs as soon as it starts...
The SQL performed when processing the cube looks like this:
SELECT field1, field2,... fieldn
FROM table1, table2,.... tablem
WHERE
(table1.id=table2.table1id)
AND
(table2.id=table3.table2id)
...
When I execute above SQL in the Query Analyser from SQL Server Enterprise Manager, it ALSO hangs...
I am not really suprised by that, because this SQL first create a huge table of 400,000 x 200 x 200 x 4 x 365 x 24 x 101 records and after that works through the WHERE statements to filter out the appropriate records.
for me it would be more logical to use the following code to process the cube, but that cannot be changed in Analysis Manager:
SELECT field1, field2,... fieldn
FROM table1
LEFT JOIN table2 ON (table1.id=table2.table1id)
....
LEFT JOIN tablem ON (tablem.id = tablem-1.tablemid)
When I execute above SQL in the Query Analyser from SQL Servel Enterprise Manager, it does NOT hang, but performs the query in about 35 seconds....
But Analysis Manager does not allow me to change the SQL used for processing the cube...
What can I do to add more dimensions to my cube... (It will be more anyway after adding the CalledNumber dimension)??
any suggestions?
PS. forgot to mention: I am using Sql Server 2000
View 2 Replies
View Related
Sep 6, 2007
I have an excel workbook, that has a pivot table in it where the data source is an OLAP cube. My problem occurs on the client machine, logged in as the client. I remote into the PC, and create a pivot table using the OLAP cube connection. I create the pivot table and everything works fine - I am able to browse the data with no issues. Once I exit the Excel Workbook, and come back in - I am no longer able to connect to the datasource. I have tried both saving the password in the connection and not saving it. It has made no difference.
View 1 Replies
View Related
May 9, 2007
The environment here is SSIS ETL feeding a Fact Table. The Fact Table is pulled into SSAS as a cube and reporting services are handled there. I am on the ETL side and don't pretend to know all the processing that happens with cubes, etc.
What we are trying to accomplish is to add partitions to a Cube via the ETL processing. The partitions should be incremented by Day, i.e. 20070501, 20070502, etc.
This is currently processed manually by the Reporting developer and we are looking for an automated process to reduce errors and hand work.
I have explored the following objects: Partition Processing DataFlow Destination and did not find much documentation or examples on it's use. If you have any information on this stage, please reply with such.
The other option is the Analysis Services Processing Control Flow. I understand that we can process Analysis Server objects as part of our package. Is there a way to incorporate a Partitioning Script in this object? If so, how. Again, I did not find detailed examples on the use of this object.
If you have experience with either of these, please feel free to reply. I appreciate any and all comments.
View 5 Replies
View Related
Nov 4, 2009
i am getting the following error when i am processing the cube in SSAS 2008...Errors in the back-end database access module. The size specified for a binding was too small, resulting in one or more column values being truncated. Errors in the OLAP storage engine: An error occurred while the 'Policy Type' attribute of the 'Policy Type' dimension from the MyDemo' database.i verified the datatype column length for policytype column in the dimension as well as all fact views.
View 9 Replies
View Related
Nov 12, 2007
Hi guys,
This is my first post. Could anyone help me out? I am trying to refresh a cube that i created by using a new script command but i get the following error "the script contains the statement, which is not allowed." On the microsoft page i've read that for refreshing you need to create a new script. Is there another way for refreshing the cube?
Cheers
View 5 Replies
View Related
Aug 24, 2015
I need to create 3 fact table in a cube with dimensions ?? is it possible . What are the steps to do.
View 3 Replies
View Related
Jan 14, 2008
Hi,
I have built two perspectives in a cube. Let's say: Sales and Projects. When I open my cube in Excel 2007 and I choose to open the perspective 'Sales' I see all sales related dimensions and attributes. When I open the perspective Projects I see all projects related dimensions and attributes.
So far so good...
But now I generated a report model from the cube. When I start reportbuilder I can choose between three perspectives, because the total cube is added as an perspective as well. Looks pretty good, and now I open the sales perspective, but...
I see al the entities in the model, even the ones from the 'projects' perspective! It doesn't have all the functionality though, but it is visible with all the entritbutes.
I hope somebody knows the solution to this problem. Thanks in advance.
Julian Kooiker
View 10 Replies
View Related
