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])
I am trying to compute average of Sales amount for 10 days and I am having an issue when using LAG function. Since weekends fall in between working days my LAG (10) function is also including weekends and hence I am not able to get the correct average.
For ex: D1 - 10 , D2 - 20, D3 - 30, D4 - 50, D5 - 10, D6 - W, D7 - W, D8-10, D9 - 20, D10 -30, D11 - 10, D12 - 40, D13 - W, D14 - W
I am using a sample script
SUM([Time].[Hierarchy].CurrentMEMBER : Time.[Hierarchy].Currentmember.Lag(9), [Measures].[Sales Amount])/10
When I use this and When my current member is D12 Then I am getting the value as 200/10 = 20 (from D12 to D3).
However ideally it should be 230/10 =23 (from D12 to D1) and lag should exclude D6 and D7 as it is a weekend.
I also have a working day flag in my time dimensions which says 0 for non working days and 1 for working days.
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?
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.
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)
How can i deploy an existing cube with a new name on the same server?
View 4 Replies View RelatedI 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.
How can I connect ssas cube to server. I have no SSAS SERVER INSTANCE...
View 3 Replies View RelatedBackground: 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
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 ".
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?
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 RelatedIs 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?
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 RelatedI 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.
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.
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 RelatedI'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 RelatedI 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]....
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,
I am looking a way to use a tail funcion to achieve something like this
If you see the picture i want obtain in one row the total amount of the month over a selected range. If I select months between 1 and 12, i want the values only for the month 12, if a select months between 1 and 10 I want only de value for October and so on.
I tried this simple mdx query:
Select
Tail([Id Fecha Orden Pago].[Mes].allmembers,1) on
Rows
,
{[Measures].[Total Gastos Orden Pago]} on
Columns
From
[CUB_DGM_TestEnamiDW] WHERE
[DIM Programa Fomento].[Nombre Programa Fomento].&[RECONOCIMIENTO DE RESERVAS]
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
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
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 RelatedThe 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.
I have built an analysis cube and I am wondering how I can now somehow add in some kind of forecastinbg to it?
I have a time dimension in years, quarts, months, days.
Is there a way I can show forecasting calculations for future dates?
I am currently creating calculations for weekly and fortnightly ranges by day.
Is it better to use LastPeriods or Tail? For example, the last 14 days could be:
LastPeriods(14,
StrToMember
(
"[Date].[Year Week Day].[Date].&[" + FORMAT(Now(),"yyyy-MM-ddT00:00:00")
+"]"
)
))
Tail(
Filter(
{[Date].[Year Week Day].[Day]},
([Date].[Year Week Day].CURRENTMEMBER, [Measures].[Test Measure]) > 0
), 14
)
What is the advantages or disadvantages?
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 RelatedHi 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
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
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
Hey Forum Community
I seem to have a problem creating a model for my AS cube through Report Manager.
I have set up the data source as follows:
Name: Gates Aust Business Intelligence
Connection Type: MS SQL Server Analysis Services
Connection String: Data Source=MANT4003;initial catalog="GAPL Sales Analysis"
Connect Using: Windows Interegrated Security
This part works, but when i go to generate the model, i get the following error in the browser:
"
Cannot create a connection to data source ''. (rsErrorOpeningConnection) Get Online Help
For more information about this error navigate to the report server on the local server machine, or enable remote errors
In the reporting services log file i have the following errors:
w3wp!library!7!07/03/2006-10:51:40:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.DataSourceOpenException: Cannot create a connection to data source ''., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.DataSourceOpenException: Cannot create a connection to data source ''. ---> Microsoft.AnalysisServices.AdomdClient.AdomdConnectionException: The connection either timed out or was lost. ---> System.IO.IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. ---> System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host
at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags)
at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
--- End of inner exception stack trace ---
at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
at System.IO.BufferedStream.Read(Byte[] array, Int32 offset, Int32 count)
at Microsoft.AnalysisServices.AdomdClient.DimeRecord.ForceRead(Stream stream, Byte[] buffer, Int32 length)
at Microsoft.AnalysisServices.AdomdClient.DimeRecord.ReadHeader()
at Microsoft.AnalysisServices.AdomdClient.DimeRecord..ctor(Stream stream)
at Microsoft.AnalysisServices.AdomdClient.DimeReader.ReadRecord()
at Microsoft.AnalysisServices.AdomdClient.TcpStream.GetDataType()
--- End of inner exception stack trace ---
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.EndRequest()
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.CreateSession(ListDictionary properties, Boolean sendNamespaceCompatibility)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.CreateSession(Boolean sendNamespaceCompatibility)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()
at Microsoft.ReportingServices.Library.RSService.OpenDataSourceConnection(DataSourceInfo dataSourceInfo, CreateDataExtensionInstance createDataExtensionInstanceFunction, Boolean isUnattendedExecution, Boolean unwrapConnection, IntPtr clientToken, IDbConnection& unwrappedConnection)
--- End of inner exception stack trace ---
w3wp!library!1!07/03/2006-10:51:59:: i INFO: Call to GetPermissions:/
w3wp!library!1!07/03/2006-10:53:02:: i INFO: Call to GetPermissions:/
I have tried searching the forums and cannot find anything really helpful. I have had one cube setup and a model generated before and all was workin, but i had to delete it and i recreated the SSAS cube from scratch.
My user account has admin rights on the cube and also on the servers.
Server is Windows 2003
Client is Windows XP.
Any one got anythoughts on this or how i can determine what is wrong.?
Many Thanks
Scotty
Any working example of what a ragged hierarchy should look like for the unary and custom rollup calcs to work?
I have a parent-child hierarchy that works as expected but can't manage to get the same with a flattened ragged hierarchy. Parents disappear if any of the children aren't in the fact table (even though the children are set to '~' and the parent has its own custom rollup calc).
Do I need to replicate the unary and measure formula all the way to the end in the same way i do the member name/value (so i can set ignore if same as parent)? Setting unary to null seems to work when the key doesnt exist in the fact table but the default behaviour for null is '+' so i end up getting wrong results if the key does exist!
Repro:
In words
I have an EAV style fact table. The measure name is defined in a 'dim measure' table. Some measures exist in the fact, some are manufactured using Unary ops or custom rollup formulas.
Here's a diagram of the hierarchy
I want to recreate the functionality as a flattened hierarchy (the number of levels is fixed), but can't seem to do it... Here's what I've gotten for the same hierarchy created using the two different approaches
Below are queries to recreate the tables from adventureworksdw2014. The parent child hierarchy is set up the standard way... measureparentkey is parent. set name, unary and formula as expected. Set NonLeafDataHidden for the parent attribute. I've tried various combinations for the ragged hierarchy but none work 100% of the time. I want to have a user defined hierarchy for the performance benefits.
--Fact view
CREATE View dbo.FactSales
AS
select fis.ProductKey
, fis.OrderDateKey
, 1 AS MeasureKey -- salesAmount
, fis.SalesAmount AS MeasureValue
[Code] .....