Creation Of Dynamic Cubes Using XMLA?
Mar 12, 2008
I hope some of you can point me in the right direction. I am new to all this and
working on a project using Cubes.
The cubes need to be generated in a dynamic fashion as the contents
(that means field) in the fact tables change change every quarter and are
based off of a external application.
The base strucutre stays the same though. So we thought we would export the
XMLA representation of the cube and then munge (word for alter) the data in the
XMLA file and send it someplace to create the new cube on a SQL 2005 server.
This seemed easy enough but I have found no real information on how to
run some piece of code that would create the cube based on this XMLA code.
On another note is there a parsing object for the XMLA so i dont have to right one from
scratch?
Thanks,
Bob
View 1 Replies
ADVERTISEMENT
Nov 10, 2015
I am trying to incrementally update a Cube to get near real time data for the end users. Currently we have a Sql server agent Job that does a FullProcess on the Cube. The Cube consists of a single Measure group which is simply one named query containing inner joins of all the dimensions and fact tables in the underlying relational database. The end users have a lot to upload during the day and they would like us to refresh the cube (near real time) to ensure the adjustments are loaded so that they could reconcile their daily PnLs. We have a MeasureId added which is an auto increment column in the Measures table.
I am trying to schedule the below XMLA query in Sql server agent Job and schedule it to run every 15mins or even less (if possible). However it seems to be not working and keep throwing all sorts of errors.
DECLARE @LastMeasureId AS INT, @myXMLA nvarchar(max)
SELECT @LastMeasureId = "[Measures].[Maximum Measures Id]" FROM
OpenRowset(
'MSOLAP',
'DATA SOURCE=L68F728326574; Initial Catalog=GMDR;',
'SELECT NON EMPTY {[Measures].[Maximum Measures Id]} ON COLUMNS FROM [GMDR]');
[Code] ....
View 2 Replies
View Related
Sep 2, 2015
I am trying to select from 2 cubes. in one condition - select .. From CubeX, and in sec Condition Select .. From CubeY.
I tried to put the name of the Cube as parameter:
SELECT ...
FROM @CurrentCube
in ssrs source and i got the message:
------------------------------
Failed to parse the query to detect if it is MDX or DMX. Error: 'Query (148, 6) Parser: The syntax for '@CurrentCube' is incorrect.' (MDXQueryGenerator)
------------------------------
Also tried to get the name of the Cube as Member (I have a property with the name of the cube),and than using the member to the select :
WITH MEMBER [CurrentCube] AS
...[dim name].CURRENTMEMBER.PROPERTIES("Cube")
SELECT ...
FROM [CurrentCube]
It didnt work too.. Is it Possible at all make the Cube-name dynamic?!
View 5 Replies
View Related
Sep 5, 2007
Hi,
I would like to know if is possible to create dynamic reports based on cubes. What i mean is,after creating a cube with a couple of dimensions and measure if is there any way to give the normal users on the report manager or report builder the freedom to choose their own dimensions/measure so they can output the report with the choosen criteria.
Thanks.
View 3 Replies
View Related
Apr 6, 2007
Hi All,
I have a web site where the user can select from a list of databases (they're actually all the same structure, but have data unique to each client). Each database is a SQL 2005 database held in the App_Data folder. This all works a treat. Each database is simply named after the client it is holding data for, and the connection string for each is stored in a database that is accessed
However, what I need is a form where a database name can be entered, and I can then create a new database with the same structure as the others - including tables, indexes and stored-procedures. I know I can use the copy database wizard in the Management Studio, but that isn't an option to the users of this system (it's to be used on an intranet, and the page I'm trying to create will only be used by project managers - but these people won't have a clue when it comes to configuring databases themselves). I have scripted a database, but have no idea on how I can actually use this script from within Visual Studio and more importantly, don't know how to configue it to have the database name as a parameter.
I've also seen some examples that suggest using SQL-DMO, but haven't been able to work out how to use it and according to Microsoft it shouldn't be used for new developments (http://msdn2.microsoft.com/en-us/library/ms132924.aspx).
Anyway, I really hope someone is able to offer some assistance on this one as I've spent a whole day trawling forums and various web-sites trying to find a solution to this.
Thanks & regards,
Paul
View 2 Replies
View Related
Apr 19, 2007
Hi,I'm trying to create some tables dynamically based on the content of another table in the same database. I found a post that does what I want to do, but I can't get my code (that is similar to the post) to work.Given below is my code: 1 DECLARE @deptCode varchar(50), @numberOfDept int, @tableName varchar(MAX), @columnName varchar(MAX)
2 DECLARE @lengthDeptCode int, @lengthTableName int, @lengthColumnName int
3
4 SELECT @numberOfDept = COUNT(DISTINCT DeptCode)
5 FROM tbl_Department;
6
7 WHILE (@numberOfDept >=0)
8 BEGIN
9 SELECT @deptCode = DeptCode, @lengthDeptCode = LEN(DeptCode)
10 FROM tbl_Department;
11
12 SET @tableName = 'tbl_ProjectNumber'+@deptCode
13 SET @lengthTableName = LEN(@tableName)
14 SET @columnName = 'ProjectNumber'+@deptCode
15 SET @lengthColumnName = LEN(@columnName)
16
17 CREATE TABLE CAST(@tableName as char(@lengthTableName))
18 (
19 CAST(@columnName as char(@lengthColumnName)) int IDENTITY(1,1) NOT NULL
20 )
21
22 SET @numberOfDept = @numberOfDept - 1
23 END
This is actually my first time using SQL programatically so I'm guessing there are alot of problems with it. I just don't know what exactly. The error I get is:Msg 102, Level 15, State 1, Line 18Incorrect syntax near '@tableName'. Thanks.
View 1 Replies
View Related
Jun 19, 2007
Hi there,I am trying to generate tables names on the fly depending on another table. So i am creating a local variable containing the table names as required. I am storing the tables in a local variable called@TABLENAME VARCHAR(16)and when i say SELECT * FROM @TABLENAMEit is giving me an error and I think I cannot declare @TABLENAME as a table variable because I do not want to create a temp table of sorts.I hope I am clear.thanks,Murthy here
View 6 Replies
View Related
Mar 19, 2004
Suppose I have a table named table1 which has a field question_Id.There are many values for this field say 100,101,102.
Now I want to make a table with the field name 100,101,102 but the problem is that it is not fixed how many values are there for question_id.Here in this example I mentioned three(100,101,102) but it may be anything.How can I make a table with the field names this way?
Subhasish
View 3 Replies
View Related
Oct 1, 2006
I'm wondering if there is a control available for creating/modifying db tables through a web interface. I want for users to be able to add/remove, rename, and change the datatype of certain fields in a database table. I've been searching all day online if such a control exists in asp.net but haven't found anything.
View 1 Replies
View Related
May 14, 2008
I'm trying to read the folder which has flat files and transform it and put it another folder with the SAME original name.
I created a For Eachloop containter and assign it to variable infile. In the data flow I added some transform and flat file destination. currently its reading 3 files and output is only one file.
I would like to create dynamic flat file connection so that it'll create separate files (same name) in different folder.
Thanks,
View 5 Replies
View Related
Mar 19, 2008
Hi All,
I'm new to SSIS. I have a small problem. In our system we are trying to create flat files based on data from returned from different SQL stored procedures. The configuration settings for creating the flat files are stored in a SQL table.
For example, the config table will look like below :
Feed Id Feed Name Feed File Name Feed Stored Procedure Name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1 Biz C:Biz.txt dvx_GetBiz
2 PG C:PG.csv dvx_GetPG
3 NexT C:NexT.txt dvx_GetNexT
4 Amez C:Amez.csv dvx_GetAmez
The problem is dvx_GetBiz, dvx_GetPG,... all have different number and types of columns. So, I'm not able to create a dynamic Flat File destination depending on the columns returned from the corresponding stored procedure.
Is there a way to accomplish this ?
Pl. help.
Awaiting someones reply ASAP.
Thanks & Regards,
Vidya.
View 4 Replies
View Related
Feb 26, 2008
Hi All, i've been reading this article http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html
in regards to creating an excel spreadsheet dynamically in SQL Server 2005 SSIS. However, i'm constantly getting an where the tab is created but not being populated. Can somebody post up a clearer example?
The problem I'm trying to solve is to automate the export of a query onto a new dynamic spreadsheet each time I run this SSIS package.
Any help would be greatly appreciated.
Thank you.
View 3 Replies
View Related
Mar 27, 2006
Hello. I'm trying to create a new login and username inside a trigger using variables.
This code works:
create login testUserName with password = 'testPassword'
The problem comes when I try to use variables for the username and password like this.
create login @username with password = @password
Does anybody know how to get around this problem?
BTW, the error message it gives is this, but I really doubt that semicolons have anything to do with it. If I literally type my data in the create login call it works fine, but when I use variables it doesn't.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near '@username'.
Msg 319, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
View 8 Replies
View Related
Jul 31, 2015
I have a reqirement where in i need to create a Database dynamically in SSIS, Database name is given in table.
View 5 Replies
View Related
Jul 31, 2015
I have a requirement where in i need to create a Database dynamically in SSIS, Database name is given in table.
View 3 Replies
View Related
Jan 23, 2008
Hi,
I have a requirement to create #Temp table in database and insert values to it.
I use following code:
DbCommand dbCreateTable;
dbCreateTable = provider.CreateCommand();
dbCreateTable.Connection = conn;
dbCreateTable.CommandText ="Create table #MyTemp (Id varchar(10))";
dbCreateTable.ExecuteNonQuery();
string[] insertValues = {"Insert into #Mytemp values ('TestString1')",
"Insert into #Mytemp values ('TestString2')"};
DbCommand dbInsertData = provider.CreateCommand();
dbInsertData.Connection = conn;
foreach (String insertStr in insertValues)
{
dbInsertData.CommandText = insertStr;
dbInsertData.ExecuteNonQuery();
}
Code creates the Temp table but when it comes to insert statement, it throws error saying "Temp table not found".
Reason can be Create and Insert statement gets executed as 2 different sessions.
How to get the above requirement work fine?
Thank you.
HV
View 4 Replies
View Related
Jun 6, 2006
Süleyman writes "I want to transfer databases with cubes and virtual cubes from SQL 2000 Analysis Server to SQL 2005 SQL Express.
1.how i can transfer the cubes?
2.how i can transfer the virtual cubes?
3.Why i see the tables(saw)and the cubes in SQL Managment only under Tables together and nothing under cubes?
Please help me
many thanks
Süleyman from Germany/Frankfurt am Main"
View 1 Replies
View Related
May 23, 2008
I have created a Backup Schedule job for the Cube using following XMLA Query
Code Snippet
<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>SalesAnalysis</DatabaseID>
</Object>
<File>D:Cube BackupsSalesAnalysis.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>
But thing is everyday the backup file is getting created with the same name "SalesAnalysis.abf", and it is overwriting the previous days file.
So I want to create a Backup file with Timestamp in its name.
Thanks in advance.
View 3 Replies
View Related
May 10, 2007
how to run XMLA file in C Sharp
View 4 Replies
View Related
May 29, 2008
Hi,
We want to synchronize one database in two instances of Analysis Services.
We are using XMLA synchronize command.Our script is correct.
Account, which is executing the script has server role in source SSAS.
I cannot find anywhere what other permissions must have the user which is executing the script and what permissions must have SSAS account(on the destination) in source server and database.
Thanks Krasimir
View 7 Replies
View Related
Jul 31, 2007
Hello,
We are in a similar situation at my client... we wish to issue the CREATE and DELETE XMLA scripts for a specific cube via SSIS, however are unsure as to the control object to use...
More info regarding our processes:
OUR SSIS flow is designed to:
1.) backup an existing cube
2.) create a new cube leaving the original in place for users to use while the new one is building
3.) once the newly built cube has been validated, drop the orignal cube, and rename the newly built cube back to the original cube's name
We used DDL code files (DDL Task Objects within SSIS) which contain the XMLA query code for each CREATE, DELETE, ALTER statements used for each specific task:
The flow:
------------------------------------------------------------------------------
1.) BACKUP Original_Cube
2.) CREATE New_Cube
3.) PROCESS New Cube
4.) DELETE Original Cube
5.) ALTER New_Cube to Original Cube
------------------------------------------------------------------------------
In order to execute each DDL Task code set for a specific task, it is necessary to specify a connection to a given catalogue... however, in our case, and in the general case of using CREATE/DELETE/ALTER ddls, a given database & catalogue may or may not always be available...
it would be ideal for there to be a way to issue an XMLA script to CREATE a DUMMY CUBE (outline and model only) for all other DDLs to use as a basis (at the beginning of our SSIS Flow), and then issue another XMLA script to DELETE the DUMMY cube when it is no longer needed (at the end of our SSIS Flow)
Currently, the flow does work fine, however, the DUMMY CUBE must exist on the Server in order to provide a connection for each DDL Task... at this point, the creation of this DUMMY CUBE has to be a manual process, we are looking to automate this....
the "Script Task" object bumps you into a VB window asking you to provide a VB script... is there a way to have VB issue an XMLA command...? Or, is there an easier way around this...?
THANK YOU!
Michael
View 1 Replies
View Related
Jun 7, 2006
I have a very simple SSIS package with one connection and one Execute SQL Task.
The connection is a Native OLE DB for Analysis Services 9.0
The execute SQL task fires off this XMLA query created through the syncronize database wizard from MSAS2005.
All I want to do is dynmaicaly replace the Source on the connection String in this script to a local variable. I have tried the @variable and the ? syntax as I found some good info in BOL. However neither of these work.
I supposed I could use scripting to handle this but I didn't want to add that layer and feel that SSIS should be able to handle this.
Here is the raw script which works fine withing SSIS:
<Synchronize xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Source>
<ConnectionString>Provider=MSOLAP.3;Data Source=ServerName;ConnectTo=9.0;Integrated Security=SSPI;Initial Catalog=catalogname</ConnectionString>
<Object>
<DatabaseID>Recommended Pricing</DatabaseID>
</Object>
</Source>
<Locations />
<SynchronizeSecurity>CopyAll</SynchronizeSecurity>
<ApplyCompression>true</ApplyCompression>
</Synchronize>
Here is what I want to execute (Which gives this error
failed with the following error: "The following system error occurred: No such host is known. .".
):
<Synchronize xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Source>
<ConnectionString>Provider=MSOLAP.3;Data Source=?;ConnectTo=9.0;Integrated Security=SSPI;Initial Catalog=catalogname</ConnectionString>
<Object>
<DatabaseID>Recommended Pricing</DatabaseID>
</Object>
</Source>
<Locations />
<SynchronizeSecurity>CopyAll</SynchronizeSecurity>
<ApplyCompression>true</ApplyCompression>
</Synchronize>
View 2 Replies
View Related
Jun 3, 2015
In Analysis services,i have multiple DB's. i need to delete all using XMLA script.
View 3 Replies
View Related
May 28, 2015
I am working on SSAS project and now i am looking for a way to generate some documentation from XMLA code ?I have seen that it's possible to do it with XML, so i think it is possible too in XMLA !
View 2 Replies
View Related
May 8, 2007
Does anyone know where can i get the source code for XMLA Thin Miner ?
It would be useful to my project.
View 1 Replies
View Related
May 20, 2015
I am trying to run XMLA script to save .trc file to remote drive.
Anytime I try to save file it save it to the local drive where SSAS instance is installed.
Here what I am using it within XMLA script
<LogFileName>c:TraceTraceFileName.trc</LogFileName>
How can I save it to my local (c drive) or remote server
View 0 Replies
View Related
Mar 21, 2007
Hi:
I am a R data miner who is new to SQL and SSIS and would appreciate any help.
I wanted to automate the process of creating and processing decision tree models for every county in the Country. I wanted to use the foreach loop for iterate through all the counties. I wanted the foreach enumerator to be used by the XMLA code that creates the model so it would append it to the name of the model and i would get a different model for every county. I am not sure how to have the XMLA code accept foreach loop enumerator.
Any help would be greatly appreciated and if you could direct me to a previously done example that would greatly benefit me.
Thank you
avneet
View 1 Replies
View Related
Oct 8, 2014
We have an SSAS instance where when we run the query "select * from $system.discover_traces" the creation time in the resultset shows a different time from when we actually started the trace.
for example if we have create the trace at 3.30pm it shows 7.35 pm in the Sql server management studio resultset when we run the query "select * from $system.discover_traces".
View 0 Replies
View Related
Aug 10, 2005
I have virtual PC loaded on my machine .Everytime i try to view data in the Data Pane in Analysis Manager,it comes up with an error "Unable to browse the data(Unspecified error).I have tried restarting the DHCP services but has not no effect .
Could someone please advice me.
Have 2 virtual PC's running on is the Domain Controller named as London and the other one is Brisbane which is a memebr of the domain controller.
Niether the London and Brisabn are able to dispaly the data in the data pane in Analysis Manager.
Please Advice.
View 1 Replies
View Related
Jan 25, 2008
What permission do i need to deploy a cube?
http://www.sqlserverstudy.com
View 4 Replies
View Related
Jan 22, 2003
I use the SQL Analysis Manager to create a cube and it can process successful, but when i want to browse the cube, it shows the error message: " Unable to browse the cube "my cube name", Unspecified error"
can anybody tell me what's wrong with that? thanks
View 2 Replies
View Related
Mar 12, 2003
Hi all,
I have created a cube in sql server
analysis manager and now i want to
give that to end user...like .exe program
what are all the possible ways we can give that to the end user.....
how to do that...
thanks
View 2 Replies
View Related
Dec 5, 2004
Hi,
I have managed to setup some cubes on the server, have roles defined, and users are using
these and the results are mathcing...
Is there a way that I can get some sort of user based usage log of the cubes andget details
of when and what type of data did any user see/make use of?
I'll appreciate any help in this regard.
View 2 Replies
View Related
